Category Archives: SSRS
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) )
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