Multi-language reports in SSRS (SQL Server Reporting Services)

Introduction

In this world of globalization, everyone wants to have projects, reports etc being accessed by users across countries/cultures around the world. And thus one needs to support SSRS reports in multiple languages.
Different-2 applications provide multi-lingual support in different-2 manners. Though, SSRS does not have inbuilt support for this, we can achieve it using some code based technique.

Background

Since, in general report contains numeric data which are to be analyzed based upon some textual headers/groups. So to actually say that a report has multi-lingual support, all that textual information should be dynamically changing as per the culture/language of end user.
And since these fields basically are the labels/textboxes displaying textual information, we actually have to manage these labels/textboxes for multi-language support.
Also we must have some mechanism by which we can detect the language/UI culture of end user and can present the page/report in an acceptable format.

Solution:

In any way of its implementation, the resource information as what should be displayed in what language /culture, it has to be stored somewhere. This can be maintained in database or in a resource file etc. The structure of the resource storage table would be like:

Field Name Value Language
Column1 Total Amount En-US
Column1 Somme totale French

Now while displaying this column1 on the report, rather than hard coding its value, at runtime we have to detect the language of interest & display the corresponding value. We can make use of the global variable of SSRS report ‘User!Language’ to get the current language been set in the client’s browser and then pass it to the data table.
This can be done either by using a custom code (DLL/Assembly) in which we can have a function and this function will return the related value of fields.

Function: GetValue(fieldname, language)

Or we can use dynamic query & directly make join to the resource table in our main data set for all the required fields. In this query we can then pass the language retuned by User!Language.

Steps:
1. Create a hidden parameter (CurrentLanguage) of type string.

Lang_Param

 

 

 

 

 

 

 

 

 

 

 

 

 

 2. Use expression to assign a non-queried default value to this parameter as =User!Language

3. Use dynamic query for the main data set as:

  SELECT ColumnName, measure1, measure2

FROM MainTable MT JOIN resourceTable RT

ON MT.FieldName = RT.ColumnName (say ColumnName = Column1)

AND RT.Language = @CurrentLanguage

 

Now at runtime @CurrentLanguage will get the language of the client & display the lables & all in the correct language.

  

About these ads
This entry was posted in SQL Server, SSRS. Bookmark the permalink.

10 Responses to Multi-language reports in SSRS (SQL Server Reporting Services)

  1. Justin says:

    Do you know how to set export options such that a particular report defaults to excel export. I’m talking about report specific settings…not just limiting the output for all exports.

  2. Hi Justin,
    You can provide a parameter in the URL to the report by passing the below format option at the end of URL:

    &rs:Format=Excel

    Now every time that link/URL is clicked, report will be rendered in Excel format by default.

    Thanks,
    Munish Bansal

  3. Fardin says:

    The idea is nice, but can you tell me how can we change the Table headers in SSRS on runtime?

  4. Venk says:

    Hi Munish,
    Thanks for the nice article.

    I want to know how do you make use fo resource file in SSRS 2008?.
    The scenario is – I have the data values in local language which are directly queried from DB. And also I have labels/text values which are static. (Example: Grid columns, Report title, Printed by:#username#, Datetime: #printdatetime# etc.
    I am thinking of an old way I used in SSRS 2005. However not sure how to reference to the resource file and read the resources.

    PLEASE LET ME KNOW IF THERE IS AN IMPROVED METHOD FOR DOING IT in SSRS2008.

    Thanks a lot in advance. – Venk

  5. Raquel says:

    Hi Munish/All:

    Can you please help me with SSRS.
    I have a matrix report that display Student Schedule Time

    Ex.

    Rows: inc. by 30 min Data: schedule ex: 8:30 – 9:15 A.M.

    8:30 A.M. Ana Taylor
    9:00 A.M 8:30 – 9:15
    10:00 A.M.

    thank you.

  6. Pallavi says:

    Hi Munish,

    Can you please help me in below SSRS problem.

    I have a reportconfiguration table which stores all the reports made in SSRS.

    I have a multivalue parameter ‘A1′ in my Report ‘A’ that takes data from this reportconfiguration table and display list of reports I have.

    Now when the user selects more than 1 values from the parameter list ‘A1′ in Report ‘A’, I want those reports to be generated in my current report A.

    I hope you got my question.

  7. Hi Pallavi,

    First you must have all those reports/subreports into your main report ‘A’. And then set the visibilty of those invidual reports/subreports or the rectangles in which they are put, based upon the selected values in the multi-valued drop down list.

  8. RGM says:

    Hi Munish and everyone – Pls Help,

    I just to ask what is your approach or best approach if you need to get the Detail data and Summary Data uisng stored proc? Do you need two separate StoredProc or you can do it in one StoredProc?

    My stored proc is already using INSERT INTO EXEC spTest and It will not allow to nest another INSERT INTO Exec if I put the Summary in another stored proc.

    Thanks a lot.

    RGM

  9. santosh bhagat says:

    HI Munish,
    I have made report using ssrs fetchxml based report for dynamic crm 2011.
    Issues are:
    1.If user change it date time format date and time funtion not work properly.
    i have to change report language at runtime based on value get by fetchxml.
    but it show error that field,parameter cannot be used in report language.

    2.My another issue is time out error,If data is small report work fine but if dataset is large it show timeout error .
    ihave change CleanupCycleMinutes,Executiontime in rsserverconfig file
    and executiontime in web.config of dynamic crm

    Any solution !!
    Thanks in Advance

  10. Woah! I’m really loving the template/theme of this blog. It’s simple, yet effective.

    A lot of times it’s difficult to get that “perfect balance” between usability and appearance. I must say you’ve
    done a very good job with this. In addition, the blog loads very
    quick for me on Firefox. Superb Blog!

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