Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / where are report defin...
Microsoft Dynamics CRM (Archived)

where are report definitions stored in the database

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi, I have written a report in SSRS that I have tried to add to CRM via the "Existing file" option.

It is quite a complicated report and gave an error  after adding it to CRM.

To try and find what was causing the error, I stripped everything out of the report except “select * from accountbase”, I then renamed the report to “Very basic report.rdl”

When I then added this report to CRM, not only did it work OK but it actually showed the output from original “Complicated” report and all worked fine.

However I needed to make a small change to it and tried to replicate my success with a new report entry (I didn’t want to risk losing the working report)

Unfortunately this time it does not work and gives the original error (I get the same error even when adding the "Very Basic report").

Comparing the two reports in the table "ReportBase" shows them as identical except for the report Id and the date/times. The sql for both shows the “Very Basic” report sql not the one that is actually working.

 

Can anyone tell me what table in the database the report definition comes from that is run when I click on the report in Reports-Reports-Available Reports when a “Reporting Services Report” is run  or how I debug what is happeneing when I click the report link?

Thanks in advance

Tony

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: where are report definitions stored in the database

    Sorry, I understand it now, so even though I am importing the report into crm it in fact stores the report in the ssrs reports database.

    Many thanks

    Tony

  • Verified answer
    joman Profile Picture
    joman 617 on at
    RE: where are report definitions stored in the database

    As i wrote before, current running reports store in SSRS DB:

    Content - is content of a report! It is in a Hex View in a base.

    select name, Content, CONVERT(varchar(max), cast(content as varbinary(max)))

    from ReportServer.dbo.Catalog

    where type =2

    P.S. i didn't think above, that convert from hex to string makes some troubles.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: where are report definitions stored in the database

    Thanks for the answers guys, however if I look at the OriginalBodyText in "Reports" it shows the definition for the "Basic report" not the actual one that is running when I click the link.

    To recap, I created the report in ssrs report builder, exported the report as <file>.rdl

    Then in CRM I went to Add new report, selected "From file" and loaded the saved report.

    Because I exported the report from report builder I don't think there will be any link between crm and reportserver so the report that is running must be defined within crm somewhere but it isn't in "Reports" table as that is showing the wrong report structure.

    Any ideas?

    Thanks

    Tony

    .

  • Suggested answer
    joman Profile Picture
    joman 617 on at
    RE: where are report definitions stored in the database

    SSRS:

    select name, Content

    from ReportServer.dbo.Catalog

    where type =2

    CRM:

    select Name,

    OriginalBodyText

    from OrgName_MSCRM.dbo.Report

    When you run report, it takes from SSRS base.

  • Suggested answer
    Andreas Cieslik Profile Picture
    Andreas Cieslik 9,267 on at
    RE: where are report definitions stored in the database

    There are two databases that are used by the SQL Reporting Services:

    - ReportServer

    - ReportServerTempDB

    All the reports and configurations are stored in the first database, and the second one is used to store temporary data and improve the service performance by caching the user sessions.

    More about this topic here:

    www.packtpub.com/.../introduction-reporting-microsoft-dynamics-crm

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

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,354 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans