RSMUS.comA client recently asked us for a pie chart report illustrating the breakdown of account types, and hyperlinked to a Microsoft Dynamics CRM 2011 view showing only accounts of the type matching the clicked pie slice. Here is how we did it using Visual Studio 2010 Report Designer.
First we created a standard pie chart categorized by account type and tallying the account values.
We then needed to create the Dynamics CRM views to which the chart would link – in this case there are only two types, ‘Brokerage’ and ‘Managed’, so two views were created and saved in Dynamics CRM. These are standard Dynamics CRM 2011 views created through Advanced Find and filtering on the account type, then saved in Dynamics CRM.
Once the views are created, we needed the URL to each view to embed in the report. This can be found by opening each view and clicking Copy a Link -> Of Current View, which will place the link on the Windows Clipboard.
Since the URLs for the two reports are the same except for the report ID, we just needed to pass the correct ID for the pie slice being clicked and we could hard-code the URL string. To set this up in an easily maintainable manner, we established two report parameters and set the default values to the IDs.
Then in our dataset, we built a case statement to populate a column with the appropriate ID for account type of each data row.
Finally, we set the Action property of the Chart Series to ‘Go to URL’,
and use the Expression Builder tool to build the following expression from the report URL copied above and where Fields!reportid.Value is a reference to the report ID we populated for each record:
= Parameters!HostName.Value & Parameters!OrgName.Value & “/main.aspx?etc=10000&extraqs=%3fpagemode%3diframe%26sitemappath%3dWorkplace%257cExtensions%257cnew_clientaccount&pagetype=entitylist&viewid=%7b” &
Fields!reportid.Value &
“%7d&viewtype=4230”
Also note the use of two report parameters for the server hostname and Dynamics CRM organization name – these are constants that would need to be updated when deploying to different environments and so are more easily maintained as report parameters. Since they are constant within the report, there is no need to map them into the dataset but instead can be referenced directly through the parameters collection.
The result was a dynamic pie chart with “hot” clickable zones in each slice section that will launch the appropriate Dynamics CRM 2011 detail view for the account type. This provides a handy way to get at timely business data directly from a dashboard.
If you are looking for Microsoft Dynamics CRM support, RSM has professionals across the United States that can help. Email us at crm@mcgladrey.com for more information or contact us by phone at 855.437.7202.
By: RSM – National Microsoft Dynamics CRM partner
