In my first post on this topic I showed how to create a CultureInfo parameter in a SSRS report, and pass the input value to the report’s Language property.
Now, in a more complicated setting, you may use formats that are not among the pre-defined format strings. In my case, I needed a DateTime format to produce day-of-week + month + date, which, depending on the culture’s format, should show as
- ddd. dd.MM.
- ddd. dd/MM
- ddd. MM/dd
- ddd MM/dd
Solution 2: LanguageId Parameter and Language Dataset
Therefore I created a table Language which contains the following definitions
| Id | Name | CultureInfo | ShortDateFormat |
|---|---|---|---|
| 1 | English (UK) | en-gb | ddd. dd/MM |
| 2 | English (US) | en-us | ddd. MM/dd |
| 3 | German | de-de | ddd. dd.MM. |
| 4 | Japanese | ja-jp | ddd MM/dd |
A report parameter @LangId is based on the query
SELECT Id, Name FROM Language ORDER BY Id
which allows the user to select one of the pre-defined languages in the database.
And here’s how to retrieve the other fields of the selected language:
I created another dataset (dsLanguageSettings) in the report which uses the query
SELECT CultureInfo, ShortDateFormat FROM Language WHERE Id = @langid
The query parameter @langid gets its value from the report parameter @LangId (Dataset Properties, Parameters).
Next, I added two more report parameters, @CultureInfo and @ShortDateFormat.
For both parameters, select
- Available Values: Get values from a query
- select dsLanguageSettings as DataSet
- select value and label fields (CultureInfo and ShortDateFormat respectively)
- Default Values: Get values from a query
- select dsLanguageSettings as DataSet
- select value field (CultureInfo and ShortDateFormat respectively)
- Advanced: Automatically determine when to refresh
The trick is the combination of Available Values and Default Values: as the user changes the value of the Language parameter in the report viewer form, the depending queries for @CultureInfo and @ShortDateFormat are refresh again, cause the Available Values to determine the values of the dropdowns, and the (refreshed) Default Values query to set the new value for both parameters.
As stated previously, the selected CultureInfo value is passed on to the report’s Language property.
The @ShortDateFormat can be applied like this:
- in Design mode, select the textbox containing a DateTime value
- right-click, select Text Box properties
- select Number, Category Custom
- click the fx button next to Custom format
- enter: =Parameters!ShortDateFormat.Value
- OK, OK
Done, the selected format will be applied. Of course, this method can be applied for any value in any culture-specific non-standard format.
devioblog 