CODEWITHSUNDEEP
reporting-servicesdynamics-crmdynamics-crm-2016
Leon Bouquiet
Leon Bouquiet

Reputation: 4382

Adding SSRS report through CRM2016: Prefiltering doesn't work?

I've created a custom Reporting Services report from within Visual Studio that is intended to use automatic prefiltering as described here.

So, I've created my query as follows:

select fullname, createdbyname, createdon
from FilteredContact as CRMAF_FilteredContact
order by createdon desc

However, once I add a new Report in CRM based on the report's .rdl file (as described under "Deploying the Report" here) it adds the report fine, but two things happen:

1) The shared data source for this report is initally invalid, both CRM and SSRS refuse to render it with the error: "The report server cannot process the report or shared dataset. The shared data source 'MyOrg_MSCRM' for the report server or SharePoint site is not valid. Browse to the server or site and select a shared data source. (rsInvalidDataSourceReference)"

I have to manually set the DataSource from within SSRS to /MyOrg_MSCRM/CustomReports/MSCRM_DataSource to get it to work.

2) The report doesn't appear under "Run on Selected records" but under "Run on All Records", and doesn't perform any prefiltering.
Also, when I download the .rdl from SSRS it hasn't modified it, neither adding an extra parameter or changing the SQL query. as I would have expected based on this information.

What could I be doing wrong?

I'm using MS CRM 2016 update 1 (v8.1.0.359) on premise, with SQL Server 2014 Reporting Services and the SRS Data Connector installed on the same machine.

Upvotes: 1

Views: 1629

Answers (2)

Leon Bouquiet
Leon Bouquiet

Reputation: 4382

Apparently, you can use a shared data source reference, but you have to name it MSCRM_DataSource, just as the Data Source that MSCRM uses for its reports.

If you upload a Report in MSCRM like this, MSCRM recognizes this and automatically updates the data source to the valid /MyOrg_MSCRM/CustomReports/MSCRM_DataSource datasource, and also adds the prefiltering modifications.

Upvotes: 2

jasonscript
jasonscript

Reputation: 6180

You've created a Shared Data Source that you are using for multiple reports. When uploading reports through CRM this is not supported. Each report must have its own connection details embedded in the report.

I think the reason for this is, as you are trying to do, the report gets modified to add hidden CRM parameters and to alter the queries so that pre-filtering can take place.

To fix this, you have to modify each report and enter the Connection Details directly into the report rather than using a Shared Connection. Its a little annoying but should give you the results you are expecting to see

Upvotes: 0

Related Questions