CRM SSRS FetchXml PreFiltering Tips

I don't do custom SSRS to often but when I do I consonantly have issues pre-filtering FetchXML in SSRS reports.  The items below are what I have found to be the total set of requirements to make sure it works.

Example:  Let's say we have an entity called 'new_house'.  Below is the FetchXML statement I would use in the data set.

<fetch mapping="logical" version="1.0">
 <entity name="new_hosue" enableprefiltering="true" prefilterparametername="CRM_Filterednew_house">
  <all-attributes/> 
</fetch>



Here are the steps i usually take to create this fetch.

-Open the query designer and paste in the fetch without the enableprefiltering and prefilterparametername attribute.

<fetch mapping="logical" version="1.0">
 <entity name="new_hosue" >
  <all-attributes/> 
</fetch>


-Next add the enableprefiltering="true" attribute then close the dataset.  A new Parameter will automatically be created in your report.  Sometimes SSRS will create the report parameter name correctly but sometimes it will not.   The report parameter MUST have the following naming convention.  If it does not select the properties of the parameters and change it's name.

CRM_Filtered<entityname>

So in my case it needs to be called CRM_Filterednew_house

-Reopen the dataset and update the fetch to include the prefilterparametername attribute

<fetch mapping="logical" version="1.0">
 <entity name="new_hosue" enableprefiltering="true" prefilterparametername="CRM_Filterednew_house">
  <all-attributes/> 
</fetch>



Final Notes:
-DO NOT ALIAS the entity you are prefiltering.  If you add the alias it will not work.  You may Alias any Linked Entities.
-DO NOT RE-UPLOAD your file to the same Report record you created in CRM.  When you do the initial upload of the report the first time a lot of things are saved to this record and are not updated if you just edit the record and upload a new rdl file.



Comments

  1. I wish I would have found this 2 hours ago, but thank you so much for posting it! I had my entity aliased and that was exactly what was causing the prefilter to fail. I never would have thought of that!

    ReplyDelete
  2. Now why can't Microsoft present things as straight forward and precisely in there documentation. One could read that stuff for days before putting all this together.

    FYI for all you kids out there. The automatic "CRM_new_house" may work but, what is happening under the covers is that "filterednew_house" is a filtered view on the database server that applies the security filtering. So if you leave it without the "filtered", you will lose all the work you put into building security filtering.

    ReplyDelete
  3. Is it possible to display a specific parameter from @ CRM_Fitertext in the report?
    For example, my filter section has many parameters, but I want to display only the date range selected by the user

    ReplyDelete

Post a Comment

Popular posts from this blog

Add User As Local Administrator On Domain Controller

Calling Dataverse Web API in PowerShell using Client Credentials

Windows Server 2008R2 VMs Shut Down After 1 to 2 Hours