A guide to help you determine the best solutions for you.
Optional Parameters in a Microsoft Dynamics CRM Fetch Report
So you’ve written a lot of SQL-based reports in your day, and you’re sitting down to write a FetchXML report for online Dynamics CRM. You realize that you need to include an optional parameter in the dataset, but FetchXML doesn’t allow for SQL standbys for situations like this such as like COALESCE, ISNULL, or CASE WHEN. For starters, you came to the right place because this blog will show you how to proceed. This blog is written with the audience in mind of those who do have experience writing SQL based reports, and are venturing out into the brave new world of FetchXML reporting.
Given Hitachi Solutions’ deep experience in all things financial services, we’ll use an example from asset management. Suppose you have a (matrix) report that shows a count of deals by portfolio manager broken down by status. Envision if you will that each portfolio manager is listed across the top of this matrix, and each deal status is listed in the first column horizontally. In each cell there is the count of deals. You want to be able to hyperlink to a subreport from this report that shows detail level information for the portfolio manager. It just so happens that this detail level sub-report is designed to show all details for all portfolio managers: it can be run on its own. We need to have an optional parameter such that when passed it returns details only for the portfolio manager clicked, otherwise this report should return all records.
If this resonates with you, keep reading. The answer to this conundrum lies in the Dataset of the report. While you can’t have calculations in FetchXML, you can have calculations in the Dataset Filter, which in Visual Studio 2010 can be accessed by right clicking from the highlighted Dataset in question –> Dataset Properties –> Filters:
The expression in this case reads in kind-of-techie English as “If the prmPM parameter is null, then filter the dataset to all records where the PM value equals the PM value. Otherwise, filter the dataset to where the PM equals the value of the prmPM parameter.” Well, in all cases the PM value equals the PM value, so it’s the same as having no filter. Now if this were a SQL based report I would do all of this within the dataset itself, but with FetchXML I do this within SSRS/Visual Studio in the creation of the RDL. So its that simple folks. Its a question of knowing where to apply the filter.
If you’d like to know more abut how Hitachi Solutions can help you with your Dynamics CRM needs or questions, please email us at CRMinfo@Hitachi-Solutions.com.