So You Want To Create a SSRS Custom Report

I frequently come across posts in the Microsoft Dynamics Community Forums from people who have various questions regarding building and modifying custom reports for CRM. The good news is that, yes, you can do it. I did it and I have minimal SQL training, no formal SSRS training, and I’m not a programmer or a SQL DB Admin. With a little reading and perseverance, I’m confident you’ll be up and running in no time.

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.

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

Export the Data

Select the Excel icon from the top menu bar

Select Dynamic Worksheet and Export

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.

We use the CRMAF prefiltering to prefilter on the record where the report is being run. If you want to have the report display a field that the user can select to filter data then you’ll need to add a parameter to your report. Here is a link to get started.

Donna Edwards writes about the things she learns on her journey with Microsoft Dynamics CRM

The purpose of this blog is to provide information about the Microsoft Dynamics CRM product and related applications in a way that helps the community implement, configure, understand, more fully leverage the application and improve user adoption.