Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications


Microsoft Dynamics CRM Pre-Filtering for CRM Reporting

Post Author: Joe D365 | May 31st, 2012

CRM Pre-filtering is a unique feature provided by Microsoft Dynamics CRM to enable reports to be context sensitive and present the report with the Advanced Find functionality. This will greatly enhance a report.

To make use of it the CRMAF_ alias prefix is simply appending to the query. So instead of:

Select name from FilteredAccount

Change the query to:

Select name from FilteredAccount as CRMAF_Account

While creating the report this capability will not become apparent until posted as a report through the report manager. Using the steps below we will walk through the steps

Follow these steps to complete:

Step 1:

Create a new report, select new table wizard

Create the data source

For the DataSet paste in this query

SELECT

customeridname, name, owneridname, estimatedclosedate, estimatedvalue, opportunityratingcodename, closeprobability, statecodename

FROM

FilteredOpportunity

Note: we are not using the CRMAF_ filter yet

It it not necessary at this time to enhance or make costeic change to the report, we are merely exploring this function.

Step 2:

Save the report as: ‘OppsByOwner_noFilter’

Post the report into CRM as new report.

Run the new report and it will immediately render.

Step 3:

Return to the report and open the dataset. Modify the query and append the alias:

AS CRMAF_FilteredOpportunity

Your query should now look like this:

SELECT

customeridname, name, owneridname, estimatedclosedate, estimatedvalue, opportunityratingcodename, closeprobability, statecodename

FROM

FilteredOpportunity AS CRMAF_FilteredOpportunity

Save the report with a ‘Save As’ and name it: ‘OppsByOwner_withFilter’

Pre-Filtering for CRM Reporting

Post as a new report in CRM

Pre-Filtering for CRM Reporting

Run the report.

This time you will be prompted with the default Advanced Find screen!

Simply run the report or add your own criteria

Pre-Filtering for CRM Reporting

Step 4:

Next we will set a default filter.

Select the report and click ‘Edit Default Filter’

Pre-Filtering for CRM Reporting

Set as desired. In our example we will pre-set to ‘Open’

Pre-Filtering for CRM Reporting

Click to ‘Save Default Filter’

Run the report to test.

When running you will now see the ‘Edit Filter’ option. This returns the user to the pre-filter screen to modify their query

Pre-Filtering for CRM Reporting

Summary

The pre-filtering option is unique to CRM reports and allows to expand the user experience and reduce time spent report building by empowering the end-user to add their own parameters and filters as their requirements dictate.

Happy CRM’ing!

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

11 comments on “Microsoft Dynamics CRM Pre-Filtering for CRM Reporting”

  1. Great article!
    In our increasingly online MS cloud / IFD private cloud world, can this same technique be used with FetchXML based reports?
    Or won't it matter anyway since the query is run by the report server (not the remote user) and rendered for the user?

    1. Hi Adam -- Yep - this same technique can be used with crm online, but we need to use fetchxml instead of sql. Fetchxml can also be used on prem too.

  2. This is the best way to explain pre-filtering for reports. Could you please show one example with fetchXml in same manner?

    1. Hi Trapti - with Fetch we can do: <entity name="account" enableprefiltering="true" .....

      1. Hi Joe,

        same issue i faced.

        i create custom ssrs report (complex query ) for crm 2015, i uploaded in crm 2015, on executing time its give error on parameter ("to long string"). how i define current opporunityid on report parameter.

        there many blog they using sql query dataset in ssrs but not with define parameter.

        in my case i create report for opportunity entity, its means there require "opportunityId" on executing / run time.

        kindly define how i full fill my requirement.

        Regards,

        MadPro Dynamics

  3. Great tutorial. I have been writing CRM reports for a few months but Filtered Views give me a headache!! I followed your instructions but the Filter report did not display the Advanced Find window? Any suggestions on what is wrong?

    1. Hi Darlene - are you using crm online or on prem? If on prem, make sure you have not only the srs connector installed, but the update rollup that matches the crm server's update rollup level.

      1. We are using On Premise, I believe the updates are all current. The thing I don't get is that I have some reports that I created before that are working?

        1. I created another Filtered Report and I'm still not getting the Edit Filter
          option? Any suggestions?
          .SELECT CRMAF_Filteredcdi_pageview.[cdi_campaignidName]
          ,CRMAF_Filteredcdi_pageview.[cdi_contactidName]
          ,CRMAF_FilteredContact.[ParentCustomerIdName]
          ,CRMAF_Filteredcdi_pageview.[cdi_leadidName]
          ,CRMAF_Filteredcdi_pageview.[cdi_iporganizationidName]
          ,CRMAF_Filteredcdi_pageview.[cdi_WebContentIdName]
          ,CRMAF_Filteredcdi_pageview.[cdi_adobe]
          ,CRMAF_Filteredcdi_pageview.[cdi_browser]
          ,CRMAF_Filteredcdi_pageview.[cdi_campaigncode]
          ,CRMAF_Filteredcdi_pageview.[cdi_duration]
          ,CRMAF_Filteredcdi_pageview.[cdi_email]
          ,CRMAF_Filteredcdi_pageview.[cdi_title]
          ,CRMAF_Filteredcdi_pageview.[cdi_viewedon]
          FROM Filteredcdi_pageview AS CRMAF_Filteredcdi_pageview
          INNER JOIN Filteredcdi_webcontent AS CRMAF_Filteredcdi_webcontent
          ON CRMAF_Filteredcdi_pageview.cdi_WebContentIdName=CRMAF_Filteredcdi_webcontent.cdi_name INNER JOIN FilteredContact AS CRMAF_FilteredContact
          ON CRMAF_Filteredcdi_pageview.cdi_contactid=CRMAF_FilteredContact.ContactId
          WHERE CRMAF_Filteredcdi_pageview.cdi_viewedon >='11/06/2013'
          --AND CRMAF_Filteredcdi_pageview.cdi_viewedon <= @LastDate
          AND CRMAF_Filteredcdi_pageview.cdi_email not like '%buyersproducts.com%'
          AND CRMAF_Filteredcdi_webcontent.[statecode] = 0 AND CRMAF_Filteredcdi_webcontent.[statuscode] = 1
          --AND CRMAF_Filteredcdi_pageview.[cdi_WebContentIdName] = 'Get Your 5% Off Order Code'
          --ORDER BY CRMAF_Filteredcdi_pageview.cdi_WebContentIdName, CRMAF_Filteredcdi_pageview.cdi_viewedon

  4. Something I think worth mentioning, to save others the hours I have wasted trying to work out why my report wasn't pre-filtering, is that you need to be very careful about the From statement. Do NOT fully qualify your table/view name, or even include the schema. I just wasted 3hours trying to workout why my report wasn't pre-filtering only to discover the reason was I had:

    FROM [dbo].[FilteredAccount] as CRMAF_FilteredAccount

    and not

    FROM FilteredAccount as CRMAF_FilteredAccount

PowerObjects Recommends