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

 

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 17, 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: