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:
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:
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) )