Monday, April 8, 2013

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!

No comments:

Post a Comment