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.
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:
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.
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.
FROM (SELECT * FROM FilteredAccount WHERE accountid in (SELECT AccountId FROM #AccountIds)) a
-- 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
PingBack from http://www.universityupdate.com/Technology/Microsoft_SQL_Server/3988301.aspx