Use PreFiltering in Reports downloaded through Webservice

In my previous post I discussed a Mail-merge alternative: Reports exporting to Word files. The reports downloaded in that approach do not support any pre filtering as CRM reports would allow you to set. It would be great if that can be set as well. This post describes how to do that.

The reports created in CRM or for CRM do support the prefiltering. In custom created reports this is added by setting an alias for the filtered views which contain the CRMAF_ prefix. See Reporting PreFilter for details. Even though that post is written for CRM 3.0, it still is valid for CRM 4.0.

The reports which are created by using the Report Wizard do not have the CRMAF_ prefix though. Also reports which have been added to CRM and have been downloaded again do not have the prefix anymore. Instead, there is a report parameter added to the report. This parameter is @CRM_FilteredEntity. Entity would need to be replaced by the entity like @CRM_FilteredAccount or @CRM_FilteredNew_Entityname. This parameter will get the value of the filter in the format of a SELECT query. For Account the default filter would be "SELECT * FROM FilteredAccount". You can set this parameter in code and replace the default filter by your own filter. Here is a code example.

ParameterValue param = new ParameterValue();
param.Name = "CRM_FilteredAccount";
param.Label = "CRM_FilteredAccount";
param.Value = "SELECT * FROM filteredaccount WHERE accountid='" + accountId + "'";
ParameterValue[] reportParams = new ParameterValue[1];
reportParams[0] = param;

These parameters you can specify in the call to the Render method of the webservice. The example below is a modified version of my previous post.

// Download the report from the webservice in HTML4.0 format
reportData = rs.Render(rptNameFullPath, "HTML4.0", null,
reportParams, new DataSourceCredentials[] { dsc }, null, out optionalString, out optionalString, out optionalParams, out w, out streamIDs);


Steve Noe said...

Thanks Ronald! That was the missing piece I needed.

Ronald Lemmen said...

You're welcome Steve :)

Ronbo said...

This was exactly the information I needed to get my solution to work. The only difference was that I had call my parameter P1 instead of the Filtered_TableName. Not sure if it was because it was a custom entity or not. I used the GetReportParameters method to figure out what the parameter was called. Thanks for this great blog!