Building Reports in Layers

Writing a report for Microsoft CRM can seem a bit daunting at first if you’re not familiar with Microsoft SQL Server Reporting Services. The reports that are available in Microsoft Dynamics CRM out-of-the-box are fairly complex creatures. Almost all of them use dynamic SQL, which is fairly uncommon in and of itself, mostly to enable prefiltering on complex queries. About half of them use sub-reports or drillthrough reports to provide multiple layers of user-click interactivity. Many reports use parameters in addition to prefiltering, and hence calculate many display elements from Visual Basic expressions. There’s also an inline sorting mechanism for tabular reports.

One Step at a Time

It can all seem pretty overwhelming if you’re just getting started. But you don’t actually need all of these elements. Armed with a book on SQL Reporting Services and the Microsoft Dynamics CRM SDK documentation, you can get started pretty quickly with simple reports. After that, you can start adding bits of extra functionality in layers. Here’s a suggested progression on the road to rich interactive reports:

  1. You can export the view to a dynamic Excel worksheet. The worksheet now contains a SQL query against FilteredViews that can be dropped directly into SQL Reporting Services.
  2. A view may be interesting all by itself, but there’s really no need to write a report when you can just look at the data in the grids and export it to Excel for manipulation.
  3. If you’ve got a chart, perhaps the natural thing is to drilldown into details. This could be done with a drillthrough report. Write the drillthrough report first, and again start simple – get the query right, then the layout, and only then hook up the drillthrough from the parent report. For a tabular report, some of the table cells could drillthrough to Microsoft Dynamics CRM For example, clicking on the name of an Opportunity should bring up the Opportunity form in Microsoft Dynamics CRM, allowing the user to take action directly from the report after seeing a item that catches the eye.
  4. This is most easily done with dynamic SQL rather than static SQL. For example, you may want to group rows in different ways. Make this a parameter, then build up a SQL string dynamically, using the value of the grouping parameter in the query. You can see examples of parameterized grouping in many of the reports that ship with Microsoft Dynamics CRM, such as Products By Contact.

Remember that reports are just SQL queries embedded in a presentation language (Report Definition Language, or RDL). There is a natural division of duties between the SQL and the RDL. The SQL should do the heavy-lifting: data retrieval, rollup, complex aggregate calculations. The RDL should stick to the presentation. You’ll notice this division in the built-in reports from Microsoft Dynamics CRM 3.0. Although we use a lot of Visual Basic expressions, they are used largely for display, for example to display different strings in a header depending on the value of a parameter.

A report can be considered to be a one-off item, like a SQL query developed to answer a specific business question. Or it can be regarded more like code that performs a function, code which can grow over time. You can manage the transition by planning ahead. For example, you can use a hidden parameter with an unchanging default value if you think it might turn into a dropdown parameter list later on. Or, if you anticipate needing some data-driven elements later (like the number formatting in the out-of-the-box reports in Microsoft Dynamics CRM 3.0), you can put in a SELECT statement that returns constant values. This structure of the returned table then forms an interface that you can fill in later. Think of this like adding constants in programming – it makes the code more adaptable when needs change.

Mixing Dynamic and Static SQL for Prefiltering

Sometimes, you run into a problem early on, at stage two when you’re writing the query. Say you have a complex business question that takes 20 Kbytes of SQL to get the answer. Since it’s such a complex query, you definitely want it to be prefilterable so people can throw different data sets at it.

But when you tag the query for automatic prefiltering, it fails to upload into Microsoft Dynamics CRM. Just too long and complex. Time to switch to explicit prefiltering, which in effect says to Microsoft Dynamics CRM: “Hands off my query, just pass me the prefilter and I’ll handle it from here on.” But explicit prefiltering requires the use of dynamic SQL in order to insert the filter into your query, and you really don’t feel like doubling up all your single quotes. For one, it’s annoying to take your 20 Kbyte query and escape everything. Plus you lose all the syntax highlighting when you copy-paste the query into SQL Management Studio. You don’t want to resort to keeping a copy of the query outside of the report for development, then having to keep integrating it back in later.

In my last blog posting about Microsoft Dynamics CRM prefiltering, I explained the difference between automatic and explicit prefiltering. I also mentioned that you could use a temp table to maintain the use of static SQL while still taking advantage of explicit prefiltering. One of the comments asked for a clarification. Here’s what I mean:

SELECT a.attributes

FROM FilteredAccount

WHERE conditions;

Of course the actual query will be 400 times longer than this one. You want to filter on FilteredAccount, but without turning all 20 Kbytes of the query into dynamic SQL. To do this, you can write only a little bit of dynamic SQL for the sole purposes of executing the prefilter query, and dumping the record IDs into a temp table. Then, use that temp table in your query, which remains static SQL.

— Store the primary key of the related records into a temp table

CREATE TABLE #AccountIds (accountid uniqueidentifier PRIMARY KEY)

DECLARE @TempSQL nvarchar(max)

— Use dynamic SQL with explicit prefiltering

SET @TempSQL = ‘INSERT INTO #AccountIds (accountid) (SELECT a.accountid FROM (‘ + @CRM_FilteredAccount + ‘) a)’;


— Now use the primary key inside the original query.

SELECT a.attributes

FROM (SELECT * FROM FilteredAccount WHERE accountid in (SELECT AccountId FROM #AccountIds)) a

WHERE conditions;

— Drop table for cleanliness. Not strictly necessary since report query
— runs in its own session, but makes it easier to debug this query
— standalone in SQL Server Management Studio.

DROP TABLE #AccountIds


Tao Yue