Multilingual Value Formats in SSRS (Part 2)

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


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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your 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: