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. TeriSeptember 17, 2019 at 5:37 PM

    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. mardukesFebruary 19, 2020 at 10:28 AM

    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. mardukesFebruary 19, 2020 at 10:29 AM

    ...in their...

    ReplyDelete
  4. zivaMarch 23, 2022 at 2:57 PM

    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

I recently was settting up a new Microsoft SharePoint 2010 machine and had promoted the machine to a domain controller before creating my SharePoint admin accounts.  I needed to add several of my accounts to the local Administrators group.  Unfortunately after you promote a server to a domain controller you can no longer access the GUI for Local Users and Groups.  Instead I had to use the command line to add the users. Open a command promt using the "Run as administrator" function and then run the following command. net localgroup Administrators /add {domain}\{user} Note: do not include the {} brackets.
Read more

Calling Dataverse Web API in PowerShell using Client Credentials

Image
Connecting to Dataverse using PowerShell can be very helpful for data migrations and use within Azure DevOps. Connecting to an instance in a non-interactive way can be tricky though. This article will provide you the links you need for creation and App registration and adding the app user to your environment. You can then utilize the script provided to call Web API requests including ones you define using the new Custom API functionality now available. The script was written so that it is not dependent on any outside libraries such as the Microsoft.Xrm.Tooling connector. This is helpful in situation where involving an outside library will slow down your deployment time by having to be approved in a change control board. If utilizing an outside code library is not a concern you can create a connection to Dataverse utilizing the Microsoft.Xrm.Tooling connector Get-CrmConnection cmdlet. Create App Registration The first thing to do is create an App Registration within Azure AD for ...
Read more

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

Image
When created a lab environment to test ADFS 2.0 I utilized the Windows 2008R2 VM baselines distributed by Microsoft.  After a few days I was told that I had to activate.  The VMs included a 180 day license for use but I didn't feel like adding another network adapter into Hyper-V to connect them to the internet.  I started having issues though where the servers would shut down every hour or so.  I though that maybe there was a memory issue and Hyper-V was shutting them down in order to free up RAM.  Turns out that if Server 2008R2 is not activated it automatically shuts down after a period of time.  After connecting the server to the internet and activating them the problem went away. Finally the madness of the unknown shut downs has ended :)
Read more