Category Archives: SSRS

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

 

Limit number of multi-select parameters in SSRS

I recently came across the need to limit the number of selections a user could make in a multi-select parameter in SSRS.

The driver was quite simple: we didn’t want users creating reports with 100,000+ rows. The source data was foreign exchange rates for which we held daily rates fully triangulated across a set of 173 currencies. With three plus years of history it didn’t seem to make much sense to allow enthusiastic users to return 40 odd million rows from the DW!

The proposed solution was to allow multiple FromCurrency (or source currency), only one ToCurrency (or destination currency) and a default date range of one year. A reciprocal report was also built to allow only one FromCurrency and multiple ToCurrency, in order to keep currency conversion nice and simple by always multiplying values.

The only problem was that pesky (Select All) entry, that Microsoft still won’t let us disable ( http://connect.microsoft.com/SQLServer/feedback/details/249227/multi-value-select-all-parameter-in-reporting-services ).

To fix this I applied a sub-query with a top 10 clause in the main dataset and changed the prompt to say “Currency From (max 10)” as below.

Whilst not the perfect solution as it doesn’t prompt the user, it is a useful method of preventing runaway reports based on large datasets.

We all know a report isn’t a good report unless it displays the chosen parameters in the header so that we can actually determine what the report contains. The only problem is by using the standard method of displaying multi-select parameters in a text box as follows doesn’t work, as it will display ALL the selections made by the user, not the top 10 used to constrain the dataset.

=Join(Parameters!CurrencyFrom.Label, ",")

 

I managed to resolve this with some pretty simple custom code, which I will write about tomorrow as have to head off to the SQL user group meeting at MS in Victoria (surely I should be doing something else on St Paddie’s Day?????)

SELECT     
  DCFrom.CurrencyCode AS CurrencyCodeFrom     
 ,DCFrom.Currency    AS CurrencyFrom     
 ,DCFrom.Country     AS CountryFrom     
 ,DD.FullDate        AS ExchangeDate     
 ,DCTo.CurrencyCode  AS CurrencyCodeTo     
 ,DCTo.Currency      AS CurrencyTo     
 ,DCTo.Country       AS CountryTo     
 ,FDER.ExchangeRate  AS ExchangeRate 
FROM     
 ViewFacts.DailyExchangeRates        FDER     
 INNER JOIN ViewDimensions.Currency  DCFrom  ON 
  DCFrom.CurrencyKey = FDER.CurrencyFromKey     
 INNER JOIN ViewDimensions.Currency  DCTo    ON 
  DCTo.CurrencyKey = FDER.CurrencyToKey     
 INNER JOIN ViewDimensions.Date      DD      ON 
  DD.DateKey = FDER.ExchangeRateDateKey 
WHERE     
 CurrencyFromKey IN         
    (SELECT TOP 10             
       CurrencyKey         
 FROM             
      ViewDimensions.Currency         
 WHERE             
      CurrencyKey IN (@CurrencyFrom))     
 AND 
 CurrencyToKey = @CurrencyTo     
 AND 
 DD.FullDate BETWEEN @DateStart AND @DateEnd

 

%d bloggers like this: