where are report definitions stored in the database

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (5)

Answers (1)

Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

joman 617 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

.

Was this reply helpful? Yes No
Suggested answer

joman 617 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Andreas Cieslik 9,267 on at

Like (0)

Report

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

Was this reply helpful? Yes No