I remember, in one of my previous projects, client wanted me to have a similar dashboard experience. The problem was Microsoft Dynamics CRM version 4.0. Back then dashboards were not out of the box. So I had to write a custom made solution which was a combination of out of the box entities, plugins, silverlight and custom workflow activities. Not anymore. Dashboards are built into CRM 2011 framework.
My blog on CRM 2011 Dashboards will be coming soon!!!
I will demo how to create a multi series column chart in SQL Server Reporting Services (SSRS). I will also show how to add & display this chart on a custom CRM 2011 dashboard.
1) First I will create a multi series SSRS chart in Visual Studio report project. Then I will display this chart in a custom CRM 2011 dashboard.
The chart will compare the total cost of production for movies and the revenue for these movies for each genre. This chart will compare cost and revenue for the movie genres of Horror, Thriller and Family.
CRM 2011 when released did not have the capability of multi series charts. This capability or enhancement was added as part of Microsoft Dynamics CRM November 2011 service update. The chart wizard now supports the ability to specify multiple data series when designing charts.
|Movie Records (used as data in SSRS Multi Series Column Chart)|
2) I have opened a movie record. This record has 2 fields "Cost of Production" and "Revenue". Both these fields are in millions (let's say currency is AUD - Australian Dollars).
|Fields "Cost of Production" and "Revenue" will be used in Multi Series Column Chart|
Note: Even though we use Visual Studio version 2010 for CRM 2011 development, but this version cannot be used for reports development. Microsoft SQL Server 2008 or 2008 R2 installs the report project templates in Visual Studio version 2008 (instead of Visual Studio 2010). Visual Studio 2010 only supports developing local reports (.rdlc).
|Visual Studio to Create a Report Project|
|Create a Report Project|
|Create a New Report|
|Add a Data Source|
|Data Source Window|
8) In this window select a SQL Server which hosts your CRM 2011 database. I have selected the default Windows Authentication. Select the MS CRM 2011 database. Click OK button.
|Create a Data Connection|
|Data Connection String as Data Source|
|Add a Dataset|
|Define a TSQL Statement|
|Create and Test the TSQL Statement|
|Save the Dataset|
14) The new dataset appears under the Datasets folder.
|Add a Multi Series Column Chart|
16) There are many types of charts like Column, Line, Shape, Bar, Area, Range, Scatter and Polar. I have selected the first Column type chart which can handle a multi series display. My demo also applies to many other types of charts. You can use my demo in the same way with other charts.
|Select a Chart|
17) This will create a chart skeleton on my report. Now I can start binding data to the chart and I can customize chart as per business requirements.
Since we are comparing cost and revenue, I will select options "Cost_of_Production" and "Revenue". This will add these fields as values. Cost and revenue will be part of "Y-Axis" on the column chart.
|Add the Numerical Financial Data Values|
19) Now I will add the "Group By" data which will form part of "X-Axis" on the column chart. We are only interested in cost and revenue for movie genres, so we select movie genre.
The report is running and displaying data as required. It is called as multi series column chart as we have 2 columns side by side displaying 2 different value types.
|Test the Chart from the Reports Server in a Browser (Internet Explorer)|
26) Click the report to run in Internet Explorer. The report runs fine. Copy the URL address of this report which is required to display report in the CRM 2011 Dashboard IFrame.
In my case I would like to display the chart in a bigger cell.
Pay special attention to 3 check boxes. I haven't selected "Pass record object-type code and unique identifier as parameters" check box. I have not selected "Display label on the Dashboard" check box. I have also not selected "Restrict cross-frame scripting" check box.
|Define IFrame Properties|
|CRM Dashboard Section|
34) I will go to "Dashboard" (right hand side) select list. I will select my newly created dashboard "Movies".
|"Multi Series Column SSRS Chart" Displayed in a Custom CRM 2011 Dashboard|
As we saw above, multi series charts are very helpful and powerful. Microsoft Dynamics CRM 2011 brings to us extensive charting and dashboard capabilities, helping the users to analyse their data quickly and effectively. The chart control is rendered as a single image.
Multi series charts help us compare and analyse 2 different values for the same type.