Wed, 08/04/2021 - 06:16 By Nguyen Van Hao

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:

  1. Some experience in SSRS report
  2. Some experience in Fetch XML query

Here is the report that we're going to build:

Dynamics 365 Enable pre-filtering for SSRS report

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":

  1. 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.
  2. prefilterparametername="CRM_FilteredAccount"

When you create dataset with enableprefiltering, a new parameter is created automatically

SSRS report

And the dataset will contain a parameter:

SSRS report

 

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 reportSSRS report

If you click on the button "Filter", you will see the current Account are filtered automatically.

Dynamics 365 Enable pre-filtering for SSRS report

 

 

Dynamics 365 Enable pre-filtering for SSRS report

Add new comment

Image CAPTCHA
Enter the characters shown in the image.