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