Supercharge your Dynamic Excel Pivot Tables into SQL Server Reporting Services in 15 minutesPosted by Brendan Landers on May 19, 2011 | Comments (0)
While Microsoft CRM offers plenty of different reporting and analysis options, each reporting tool has its own unique pros and cons. One of the most popular reporting tools in Microsoft CRM is the ability to create Dynamic Excel files using Pivot Tables and Pivot Charts. You can crank out some very powerful reports without writing a single line of code. While Pivot Tables are a great reporting option, they do have some limitations. One of the biggest issues that I see is that many of our customers’ end users aren’t comfortable working with Pivot Tables. Instead of trying to train everyone on how to use Pivot Tables, another option is to convert the Pivot Table report output into a simpler report format so that the entire audience can use it.
We’re going to show how to convert a dynamic Excel Pivot Table into an advanced SQL Server Reporting Services (SSRS) report. Even better, you can do this within 15 minutes!
Since many of our customers focus on sales forecasting reports, let’s create an Opportunity Pipeline report for our example. First, I will open up Microsoft CRM and navigate to the Open Opportunities view. In my example, I have 18 open opportunities.
Let’s assume that these are the columns you want to include in your report. On the Export to Excel dialog, I’ll select Dynamic PivotTable.
In the PivotTable Field List in Excel, I will add the Potential Customer field to the Rows, Probability to the Columns, and set the Values equal to the Sum of Est. Revenue.
Just like that, we’re done with our Dynamic Excel report. Now we’re going to take this Dynamic Excel file and supercharge it into a SQL Server Reporting Services report. SSRS reports offer you advanced reporting features such as:
- You can schedule SSRS reports for automatic email delivery (i.e. send the opportunity report to sales management every Monday at noon).
- You can create sub-reports and drill-through reports.
- You can include data from multiple entities in the report query .
- You can setup report caching, snapshots, etc.
While the Report Wizard in Microsoft CRM allows you to create SSRS reports, unfortunately it doesn’t let you pivot the data into the table/matrix style the Excel file output shown above.
First, we need to grab the query from the data connection from the Excel file. Getting the query using this technique makes your life easier as you don’t need to know the CRM database to get the SQL query needed for the report. It also ensures that your SSRS report conforms to the Microsoft CRM security settings(because the query in Excel connects to the Filtered Views designed for reporting). To get the connection information, click on the data tab in Excel and select ‘Connections’.
Open up the properties of the connection named ‘Connection’. On the Definition tab, copy the text found in the Command Text section:
select top 10000 opportunity0.name as 'name', opportunity0.estimatedvalue as 'estimatedvalue', opportunity0.estimatedclosedate as 'estimatedclosedate', opportunity0.customeridname as 'customeridname', opportunity0.opportunityratingcodename as 'opportunityratingcodename', opportunity0.closeprobability as 'closeprobability', opportunity0.opportunityid as 'opportunityid', opportunitycustomeridcontactcontactid.emailaddress1 as 'opportunitycustomeridcontactcontactid.emailaddress1' from FilteredOpportunity as opportunity0 left outer join FilteredContact as opportunitycustomeridcontactcontactid on (opportunity0.customerid = opportunitycustomeridcontactcontactid.contactid) where (opportunity0.statecode = 0) order by opportunity0.name asc, opportunity0.opportunityid asc
Quick tangent: The ‘top 10000’ portion of the query ensures that the user does not export more than 10,000 rows (the default limit) into Microsoft Excel. That value can be changed in the database if necessary. Alternatively, if you want to get around that limit in an Excel report, you can modify the value in the connection properties area and click OK. We should probably keep that a secret though. Shhhhhhhhh.
OK, back to the report. Now that we have our query, we will open up Business Intelligence Development studio and use the report wizard to create the SSRS report.
Note: Business Intelligence Development studio is part of the Visual Studio install. If you do not have access to this tool, please see your Administrator
First, we will create a project. To do so, click the Create Project link.
Next, select Report Server Project Wizard, give the report a Name and click OK.
The Report Wizard appears. On the Select the Data Source page, click the Edit button and enter the Server and Database name for your Microsoft CRM installation.
Click OK and click the Next button to go to the Design the Query page. Paste the query captured from Excel in the Query String field and click Next.
On the Select the Report Type page, select Matrix and click Next. A matrix report represents a pivot table in SSRS.
In the Displayed Fields area, move closeprobability to the Columns area, customeridname to the Rows area, and estimatedvalue to the Details area.
Click Next. On the Choose the Matrix Style page select Corporate and click Next. Click Next once again to get to the Completing the Wizard page. Give your report a name, click the Preview Report checkbox and click Finish
There you have it…the basic SSRS report is created.
Apply some simple formatting in SSRS on the design change, and you can upload the report (RDL file) to CRM and you are done!