Pre-filtering in CRM 2011 SSRS Reports

By now you've been through the SDK and other sites explaining how to do pre-filtering.  There are the 2 main types: Automatic and Explicit. Utilizing the automatic is simple enough, instead of using a plain filtered view in the from clause, alias it with CRMAF_ prefix. (Example:  Select accountid from FilteredAccount becomes Select accountid from FilteredAccount as CRMAF_FilteredAccount)

This works well if you can use the query string within the report designer. If, however, you need to used a stored procedure (or you just prefer it), you need to setup explicit filtering. The suggestion is to use dynamic SQL strings and the sample reports provided in CRM out of the box all use explicit filtering. When I look at them, it seems rather complicated. After all, if I already have a stored procedure I don't want to rewrite the whole thing, I just want to get a filtered dataset and be on my way.

To this I say: take the explicit filter and create a temp table. You can then more easily reference the temp table throughout the rest of the stored procedure.  To do this, you will need to create a hidden parameter within the report with the prefix CRM_ that references the filtered view. (Example: @CRM_FilteredAccount).  This parameter will also need to be within your stored procedure. 

I find it easiest to use the wizard in CRM to create an RDL file that will contain all of the parameters. Download the RDL from CRM and then you can modify it. Just delete the data set they create. You'll be using your own dataset with the stored procedure.

In the stored procedure you'll have the following to create your temp table:
------------------------------------------------

ALTER

PROCEDURE [dbo].[report_Name]

@CRM_FilteredAccount

nvarchar(max),

AS

BEGIN

SET

NOCOUNT ON;

--place Account IDs into a temp table using CRM Explicit PreFiltering

create

table #temp (item uniqueidentifier)

declare

@tempSQL nvarchar(max)

set

@tempSQL='Insert into #temp(item) (select a.accountid from (' + @CRM_FilteredAccount + ') a)';

exec

(@tempsql)

---------------------------------------------------------------------------

You can have any other parameters you need in the beginning. Just so long as you have the @CRM_ parameter. Using this method I can now just reference the temp table to make sure I work with the appropriate records. (Example: Select name from FilteredAccount where accountid in (select item from #temp))

Once you connect the stored procedure to the report, it's going to ask you for a sample value to that it can make sure everything runs. Pass the @CRM_ parameter as blank.  It will throw an error if you try to pass in a value.  When the report is uploaded to CRM, CRM will load the parameter for you dynamically.

Counting unique values conditionally using SSRS

Sometimes you want to count unique values based on filtered conditions. For example, count the number accounts and contacts in which you have a qualified opportunity.  You would be running the query on the opportunity and the joining back to contact and then account. To do this:

countdistinct(iif(<parameter that makes opportunity qualified>,<field with distinct value you want to count>, nothing))

Hope this helps someone out there.

First Post

Hello.

After spending hours combing through other blogs finding the one thing that will allow me to finish a task for my current project I realized I should have a professional blog for me to post about. This way, I don't have to go to other blogs to find the solution to the problem, I can just come to my own blog.

We'll see how this goes.