Dynamic SQL Report Pre-Filtering

Previously we discussed the two types of queries where CRM supports pre-filtering, Aliased Views and Dynamic SQL. – The advantages of Dynamic SQL allow for flexible and, well, uh, “dynamic” reports.

Let’s look at how Dynamic SQL query is constructed in a report and how it is interpreted at run-time.

Building Dynamic SQL

In order to pass the pre-filtered dataset to a sub-report, or to make the report query flexible at run-time (“Dynamic”); you’ll need to design the report dataset’s query using dynamic SQL and utilize a report parameter named CRM_FilteredEntity.

NOTE: “CRM_FilteredEntity” is a generic way of naming the parameter. When used in a report the “Entity” part would be replaced by the name of the entity needing to be pre-filtered – e.g. CRM_FilteredOpportuntiy or CRM_FilteredNew_MyCustomEntity. Also note that the Dynamic SQL variable name begins with “CRM_” as opposed to the “CRMAF_” used in the aliased views method.

When the report is run from CRM, the report parameter CRM_FilteredEntity is set to the equivalent of the Advanced Find filter, but in the form of a standard SQL select statement. This SQL statement is then incorporated as a sub-query into a larger SQL query in order to build the dataset.

Example Query

The SQL for the dataset in an opportunity report using Dynamic SQL could be built with this query:

Here’s the way the it would be coded in the dataset -

/* Declare a variable (@SQL) to hold the full query text */
DECLARE @SQL nvarchar(max)

/* Build the query string to select the fields needed for this report */
/* and include the variable @CRM_FilteredOpportunity the subquery. */
/* Note- Be sure to alias the subquery, otherwise it won’t work. */
SET @SQL =
'
   SELECT
         ownerid, owneridname,
         opportunityid, name,
         customerid, customeridname,
         opportunityratingcode, opportunityratingcodename,
         estimatedvalue_base, estimatedclosedate
   FROM ('+@CRM_FilteredOpportunity+') AS Opportunity
'
/*Execute the contents of the variable @SQL */
EXEC (@SQL)

Dynamic SQL at Run-Time

When run from CRM with a default filter of “Status = Open”, the filter editor will pass the following query string into the report parameter ‘CRM_FilteredOpportunity’:

select opportunity0.*
from FilteredOpportunity as opportunity0
where (opportunity0.statecode=0)

@CRM_FilteredOpportunity is combined with the rest of the select statement into the variable @SQL.

Reporting services now sees the full query as:

SELECT ownerid, owneridname,
   opportunityid,name,
   customerid, customeridname,
   opportunityratingcode, opportunityratingcodename,
   estimatedvalue_base, estimatedclosedate
FROM (select opportunity0.* from FilteredOpportunity as opportunity0 where    (opportunity0.statecode=0)) AS Opportunity

(Notice how the value of CRM_FilteredOpportunity is now embedded into larger query.)

This larger query is then executed by use of the “EXEC” command - EXEC(@SQL) - and the results populate the report’s dataset.

In the next post we’ll look at how to build/test this in Query Analyzer and how to pass a GUID variable into Dynamic SQL.

-- Scott Sewell