So You Want to Become Custom Report Writer Expert


Guest blogger and CRM MVP Donna Edwards talks about a power-user toolset that help you become a proficient report writer.

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.

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

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

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.

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.

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

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

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

      image

      • 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

      image

      • Select Properties from the Connections window

      image

      • Select the Definitions tab from the Connections Property window

      image

      • 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. 

      Cheers,

      Donna Edwards

    Comments (9)

    1. Sam says:

      I wish I had this information a couple of years ago! I started work here at a NGO that had just deployed CRM and my job was just data entry, but by doing exactly what you described here I have now developed the system with about 10 – 15 additional custom entities, numerous workflows, about 35 custom SSRS reports, significant onload and onchange javascript customization and ISV customization – all without any qualifications in IT – just a knack for problem solving and Googling 🙂

    2. Peter says:

      Awsome! This is really helpful and thanks for the links to the external resources you gathered!

    3. Edwin says:

      A very informative article – especially using Advanced find to drill down to the source query – i liked that…

      Cheers,

      Edwin

    4. Neil Benson says:

      Donna, thanks so much for sharing. I'm not an expert report writer at all so I'll definitely be using these resources and sharing them with my customers. Business intelligence gets easier with built-in visual charts in CRM 2011, but sometimes you've got to crack open Visual Studio and roll your sleeves up!

    5. Frank Lee says:

      Nice write-up Donna!

    6. Jaspinder Singh says:

      Great post Donna! Enjoyed reading it.

    7. CRMor says:

      Donna, I am new to CRM report. I am working on the project that needs open a new report in report viewer window from a CRM report via 'Go to Url' setting. I was unable to pass in any params to the new CRM report via url. Any insight would be much appreciated.

    8. Jim Glass Jr says:

      CRMor, can you tell us if you have consulted the CRM SDK?

    Skip to main content