Set Multiple Value To Report Parameter When Running CRM Report Using Report Server URL

Set Multiple Value To Report Parameter When Running CRM Report Using Report Server URL

Often users wanted to run the MS CRM reports directly form the report server using the report URL.

The one way of approach to display the record in the URL, pass the parameter name and then pass the value, another record want to display again pass the parameter name and value.

To avoid the above approach for provided the parameter name (“more than once”).

As I identified my approach to resolve the parameter name passing more than a time and also URL length was compatible.

You will see the below query example for my approach

DECLARE @S nvarchar(max),@X xml  SELECT @S = @ServiceId SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,';','</s><s>') + '</s></root>') SELECT T.c.value('.','varchar(max)') as ServiceValue into #temp FROM @X.nodes('/root/s') T(c)   SELECT  *  FROM TableName(CRM Entity Name) WHERE FieldName(FilteredField) IN  (SELECT  *  FROM  #temp ) Drop Table #temp

In this approach declared two variables, convert this format into xml and assign the values to temporary table (“#temp”).
The Semicolon represent more than one value to be passed and add the conjunction after each parameter value. If the user has to change any expressions like (“; | , etc.,”) based on the needs.
Finally, filtered more than one parameter value using temporary table and then drop the table (Avoid the memory leakage).

 

Example Query:-

DECLARE @S nvarchar(max),@X xml SELECT @S = @ServiceId SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,';','</s><s>') + '</s></root>') SELECT T.c.value('.','varchar(max)') as ServiceValue into #temp FROM @X.nodes('/root/s') T(c) SELECT                 FTO.name AS OpportunityName,                 FTO.opportunityid AS OpportunityId FROM FilteredOpportunity as FTO   WHERE FTO.opportunityid IN (SELECT  *  FROM  #temp) Drop Table #temp

In this example, I have using opportunity entity, retrieve two fields,display the record based on parameter supplied(OpportunityId) and also using the parameter name has ServiceId.

Query Output:-

Example Query URL:-

http://ServerName/ReportServer/Pages/ReportViewer.aspx?%2fBaker_MSCRM%2fOpportunity+Report&rs:Command=Render&OpportunityId=1670A42C-F487-E211-9D33-005056A1000A|9158CE77-5969-E211-B847-005056A1000A

ServerName - CRM Server Name
OpportunityId - Parameter Name
1670A42C-F487-E211-9D33-005056A1000A - Guid (Id) of an Opportunity
|  - Special Character after each parameter value

Hope this was helpfull!
Thanks!