I had to write a report for a customer this week and unfortunatly I wasn’t able to write the report using the report wizard.
This was very bad news because all other reporting methods take a lot more time.
So what are my options
- Dashboard and charts
- Reporting wizard (although for this report it wasn’t any use)
- Excel Powerpivot
- SRS reports – FetchXML
- SRS reports – SQL
To give you some background one of the reports was a report grouping on date (month, year), another group of accounts and another group of Call Type. In the report they wanted to show the number of resolved calls and the percentage of each call type.
Dashboards and charts were not really used for this because this was a report rather than a visual graph. I am a big fan of charts and dashboards but one aspect that makes them a harder sell is the dashboards and charts cannot be scheduled and can’t be exported from CRM without doing a print screen (or I haven’t found a way to do this yet, please comment if you know how)
I was able to run the report in Excel PowerPivot, although it took me a while to work out why I couldn’t group the date by month (as seen in many Google searches on the subject). If you cannot group/format a date in excel to month this is probably because there are some blanks in the date field, if you fill in this blanks you will then be able to format the date by month.
The excel PowerPivot report looked very good and has the advantage of being self sufficent. What I mean by this is once you have saved the Excel report onto your hard drive you can run the report from your machine without opening CRM and the reason you can do this is because you can refresh the data within Excel.
I think excel is a really useful tool because you can also view the individual lines behind the graph, so it does give you a bit of drill down in basic terms.
I do find some users don’t view reports in Excel very highly and almost don’t see this as a legitimate solution to their reporting needs.
I tried to write the report using FetchXML, this does make writing reports quite simple because all you need to do is create the correct advanced find and then download the fetchXML. The problem I had with this method was all the grouping had to be done then in the report. My SRS reporting skills are not great and I couldn’t really get round the problem of the report displaying every line, e.g. every call, I didn’t want this I wanted a summary count of the different call types.
Searching around I found you can group one item in a fetchXML.
The other solution is using SQL and doing selects from Filteredlists. The reason it’s good practise to use filtered lists is these lists automatically add the CRM security to the selects as if you are viewing the records in CRM. It can be a bit slower and some reports don’t need to worry about security if you are showing summary values and not individual records.
The benefit of creating an SQL report is you can group the data in the SQL statement and use the report to just show the data.
The downside to using SRS is it’s hard to use and formatting the report can be a puzzling and time consuming practise of trial and error.
I really wish Microsoft would supply an easy to use reporting system on the same lines as the dashboards and charts. Reporting is one of the key areas for CRM, useful reports can help get support for a CRM project from the key decision makers.
My hope is the Sharepoint/SQL Server 2012 new reporting tools are very good and if they are not then Microsoft should buy a company which has already developed a reporting tool.
So in the end I did the report in Excel PowerPivot and a version in SRS reports using an SQL statement I wrote.