Limit number of multi-select parameters in SSRS – Custom Code

In reference to my previous post “Limit number of multi-select parameters in SSRS” here is the custom code to show only the TOP X parameters in the report header:

image

Public Function ShowParameterValues(ByVal parameter as Parameter) as string 
Dim s as String  

If parameter.count > 10 then    
For i as integer = 0 to 9     
 s = s + Left(CStr(parameter.Label(i)),3) + ", "    
Next  
Else     
For i as integer = 0 to parameter.Count-1     
 s = s + Left(CStr(parameter.Label(i)),3) + ", "    
Next  
End If  
Return Left(s,Len(s)-2)
End Function

 

and the textbox needs to look something like:

=Code.ShowParameterValues(Parameters!CurrencyFrom)

 

Those with a hawkeye may have noticed the Left() function, this isn’t necessary but was because I wanted the parameter Labels in the drop down to show a full currency description (ie “GBP – Pound sterling”, “Czech Koruna”) to help avoid confusing items like the Singapore Dollar (SGD) and the Sudanese Pound (SDG), but the report header to only show codes (“GBP”, “CZK”). For performance reasons I wanted to use the integer surrogate key on the fact table to filter (making use of indexes etc) rather than use the 3 letter ISO codes.

What would probably be a bit smarter would be to add a hidden variable @CurrencyFromLimit with a default value of 10 (make sure it’s of type integer otherwise the the main dataset sql won’t work) and refer to this in both the customcode (lines 3 and 4) and the sub select in the where clause of the main data set in the form:

(SELECT TOP (@CurrencyFromLimit)     
 CurrencyKey 
FROM     
 ViewDimensions.Currency 
WHERE     
 CurrencyKey IN (@CurrencyFrom) )

 

Advertisements

About Karl Beran

I take interest and satisfaction from enabling people within an organisation to have an accurate, concise and flexible representation of the right information they need, when (or before) they need it. I believe this allows the business to make both operational and strategic decisions quickly and with more confidence, thus improving the agility and efficiency of the company. Having worked with BI stacks from the major vendors, the focus of my blog is on BI principals and strategy with examples from the Microsoft stack as I believe it has the most dynamic and engaged user community with the knowledge base and components fairly readily available to new developers.

Posted on March 19, 2010, in SSRS. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: