Dynamics 365 Enable pre-filtering for SSRS report:
When implementing an SSRS report in CRM, you might want to run a report on a specific record. For example run a report for the opening Account, Contact.
Normally, the user will manually enter a value for a parameter (a GUID of record) for report when running report
In this article, I'll show you how to build an SSRS report (fetch XML base) that runs on a specific Account.
We'll build a report to show account information and a list of related contacts in a table.
Prerequisite:
- Some experience in SSRS report
- Some experience in Fetch XML query
Here is the report that we're going to build:
Step 1: Create a new empty report. You can create a report using Visual Studio or Report Builder
Step 2: Add dataset for our report.
Hint: you can use Advanced Find in CRM to build a fetch XML query then copy it to data set query
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="contact">
<attribute name="fullname" />
<attribute name="telephone1" />
<attribute name="contactid" />
<attribute name="parentcustomerid" />
<attribute name="mobilephone" />
<attribute name="lastname" />
<attribute name="firstname" />
<order attribute="fullname" descending="false" />
<link-entity name="account" from="accountid" to="parentcustomerid" link-type="inner" alias="ab" enableprefiltering="1" prefilterparametername="CRM_FilteredAccount" >
<attribute name="telephone1" />
<attribute name="name" />
<attribute name="fax" />
<attribute name="websiteurl" />
<filter type="and">
<condition attribute="name" operator="not-null" />
</filter>
</link-entity>
</entity>
</fetch>
The key points here are 2 properties in the "link-entity":
- enableprefiltering="1": To enable pre-filtering for the primary or linked entity in a Fetch-based report, you must set the value of the enableprefiltering parameter to “1”, and specify a parameter name in the prefilterparametername property.
- prefilterparametername="CRM_FilteredAccount"
When you create dataset with enableprefiltering, a new parameter is created automatically
![SSRS report](/sites/default/files/inline-images/SSRSReport2.png.pagespeed.ce.zdIYCoCwRM.png)
And the dataset will contain a parameter:
![SSRS report](/sites/default/files/inline-images/xSSRSReport3.png.pagespeed.ic._iR3Lbl3pV.png)
It's done, let's deploy this report to CRM and run a report in an Account.
You need to create an Account and some Contacts link to this Account, then open the Account and run our report
![Dynamics 365 Enable pre-filtering for SSRS report](/sites/default/files/inline-images/xSSRSReport4.png.pagespeed.ic.a39_bWRK4l.png)
![SSRS report](/sites/default/files/inline-images/xSSRSReport5.png.pagespeed.ic.H_pPd7HsYA.png)
If you click on the button "Filter", you will see the current Account are filtered automatically.
![Dynamics 365 Enable pre-filtering for SSRS report](/sites/default/files/inline-images/xSSRSReport6.png.pagespeed.ic.fjh0Q3QpbE.png)
Dynamics 365 Enable pre-filtering for SSRS report
Comments
Thank you
Hello sir, I did what you explained exactly but the Report filtering criteria appear empty. what i miss ? please help