So You Want To Create a SSRS Custom Report

When I started writing reports a few years back I purchased a SQL Report Writer book.  Amazon and other online retailers have several books that you can purchase from beginning Report Writing through Programming.   Just look through their lists, read the reviews and find one that you are comfortable with.  The book I purchased was a great reference for getting started but as the custom report requests I received became more complex, I had to dig deeper for specific answers to my ‘how to’ questions.  Once again, the tech community came to my rescue.  Whether I needed to understand how to format my query, manipulate dates to get the return result I needed, use the ‘CRM_AF’ filter feature, etc., I could generally find a post or answer to a posted question that pointed me in the right direction.

Here are some of the free resources I use and suggest for getting started with writing SSRS reports for Microsoft Dynamics CRM.  They are in no particular order.

• Report Writer Guide

Nuts to Bolts; everything from best practices to understanding the database model, publishing reports and more.

• Create a report in 15 minutes or less

Great article, quick read that includes all the key points to get up and running quickly.

• Step-by-Step on how to create a custom quote, invoice report

I wrote this post.  It provides step-by-step instructions for creating a branded invoice or quote that you can upload to CRM and use for delivering these types of documents to your clients.

• Build a CRM 4.0 Report that will be accessed via an iFrame

Terrific step-by-step article by Catherine Eibner on how to build and implement a SSRS report for use in an iFrame.

• Download existing reports from CRM or the CRM Report Server, open them in Visual Studio

Not for the faint of heart as the default reports in CRM can be quite complex because they are written by expert report writers.

• Microsoft SQL Server Forums

Great place to get your specific questions answered by the experts.

• LinkedIn

Join SQL groups on LinkedIn.  There are several to choose from.

• Twitter

There are a significant number of SQL experts on Twitter who are happy to help you with a specific question.

The above are just a few of the free resources available to you.  As you begin your journey into the world of SSRS Report Writing & Business Intelligence, I’m sure you will discover more resources and find a few favorites along the way.

One important point to remember when writing custom reports is to write your query against the Filtered Views.  There are a lot of reasons for writing against the Filtered Views rather than the base tables: data is aggregated in a friendly way so you will have significantly fewer joins, lookups are translated to their friendly names, security is applied so users will see only the data they are permitted to access, etc.  Consider the Views your friends and use them.

And, last but not least, if you are new to writing SQL queries, here is a tip that will definitely give you a ‘leg-up’ in understanding how to write queries against the CRM database. 

If you find yourself in a quandary about how to create a join, wondering which table to query for the data, which filter you should apply to generate the desired result set, etc., then………. (drum roll please) …….. if you can build an Advanced find view for what you need or at least a View that is close to what you need, you can have the system build the query for you.  Wow, did you catch that?  That’s right, CRM will generate the query for you, allow you to export the query, copy and paste it into your report, modify as needed and is available to every CRM user by leveraging the Dynamic Worksheet export feature.  Below are the steps. 

For this example, I am going to build an Advanced Find that returns all Orders created this month which includes some fields from the related Account (Customer).

• Create your Advanced Find query in CRM
  • Open Advanced Find, select Orders from the Look For picklist, select Active Orders from the Used Saved View picklist
  • Select ‘Created on’ from the first available filter field (Select)
  • Select ‘This Month’ as the filter criteria
  • Select Edit Columns from the top menu bar
  • Select Add Columns
  • Select Account(Customer) from the list
  • Select the e-mail field to add to the result set and select OK
  • Run the query by selecting the Find button

• Select all the options required to open the Excel worksheet and enable the data content
• Select ‘Data’ from the top menu
• Select Connections from the Data Ribbon

• Select Properties from the Connections window

• Select the Definitions tab from the Connections Property window

• Look in the Command text box and you will find the query
• You can copy and paste the query into your report dataset or SQL Management Studio and use it as a base query for your report

I generally always remove the select ‘top 10000’ record limitation from the query as this is a limitation applied through Advanced Find for performance and other reasons.  One nice discovery of this particular query is the use of the dbo.fn_BeginOfThisMonth(GetUTCDate()) function.  This is a very helpful function that you can leverage for manipulating and filtering date related data.

The above is an excellent method for gaining a good understanding of how data is queried from the CRM database.  You will definitely learn a lot from reviewing these types of queries so I encourage you to build some complex views using Advanced Find then take a look at the queries running behind the scenes.

For those of you who are new to SSRS or would like to find resources to help you grow your skills, I hope you find this information useful. 

Windows Live Tags: Dynamic Worksheet,Business Intelligence,Visual Studio,Catherine Eibner,Report Writer Guide,Microsoft Dynamics CRM Forum,SSRS Custom Report,CRM_AF,LinkedIn,Twitter,GetUTCDate