Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Dynamics GP SQL server...
Microsoft Dynamics GP (Archived)

Dynamics GP SQL server reporting

(0) ShareShare
ReportReport
Posted on by Rebecca Payne

Hi

I am trying to create documents using SQL Server Report Builder and I would like to assign the logo based on the company like you would in the Reports>Template Configuration settings. I have found the table where these are stored as Binary BLOB's but can't figure out how to effectively link these to the company ID that is correct every time using tables syCompanyImages and SY01500.

I have got this to work for EITHER Fabrikam OR companies I have created using the following scripts:

NON FABRIKAM

SELECT       SY01500.INTERID                         AS Company,                     SY01500.CMPNYNAM                 AS CompanyName,                     syCompanyImages.BinaryBlob AS Picture FROM         Dynamics.dbo.syCompanyImages INNER JOIN   Dynamics.dbo.SY01500 ON SY01500.CMPANYID = (syCompanyImages.RELID /65536) ORDER BY     CMPNYNAM

FABRIKAM

SELECT       SY01500.INTERID                         AS Company,                     SY01500.CMPNYNAM                 AS CompanyName,                     syCompanyImages.BinaryBlob AS Picture FROM         Dynamics.dbo.syCompanyImages INNER JOIN   Dynamics.dbo.SY01500 ON SY01500.CMPANYID = (syCompanyImages.RELID /65536) +1
ORDER BY     CMPNYNAM

Any ideas would be greatly appreciated.

*This post is locked for comments

  • Rebecca Payne Profile Picture
    Rebecca Payne on at
    RE: Dynamics GP SQL server reporting

    Hi Redbeard,

    This is really helpful, but I was trying to get away from having to run different documents for different company databases (hence the dynamics database using the template configuration). But I will give this a try.

    Thanks

    Becky

  • Verified answer
    Redbeard Profile Picture
    Redbeard 12,931 on at
    RE: Dynamics GP SQL server reporting

    Rebecca,

    Your idea is sound, but I think I have a more workable approach.  It is possible to store the location of image files at the company level in Dynamics GP.  Setup > Company > Company > Globe (Internet Information).  This data is contained in the SY01200 in each company database, so it's not as complicated as dealing with data maintained at the DYNAMICS database level.  

    If you save the location of the file in this window in GP for each company, you can access this data with the following query - the path is contained in the INET4 field:

    select Master_Type,Master_ID,INET4 from SY01200
    Where Master_Type = 'CMP'
    and Master_ID = 'TWO' --for Fabrikam

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans