International Reporting with SQL Server Reporting Services 2005
Did you ever try to setup international reporting with SSRS? It will be a very hard job or better, it doesn’t work. I hope that this will change (perhaps with SP1) but I don’t know for sure.
So why isn’t multilingual reporting easy? Microsoft introduced the Unified Dimensional Model (UDM) as a common data source for reporting. This is a cool thing, I like it very much. The UDM also has “translations” for each member/dimension/level name and you can define different sources (fields) for each language you define.
So what? This seams to be perfect, what am I complaining about? From the standpoint of the UDM everything is fine. But now we try to use these features in a report.
Which language to use?
So first you have to know which language you want to display in the report. How do you tell the UDM which language you’re currently working with? That happens with a parameter in the connection string. IDW15 has a bug in it, so this will not work perfectly. Brian has a blog about that… http://blogs.msdn.com/bwelcker/archive/2005/07/03/435130.aspx
Translating Names
OK, so you dropped your tables in the report, you have column headers and footers, everything seams to be fine. And now you expect that these header/footer are translated automatically!? No, this is not possible (out of the box). The only way I know is by creating a new cube (or a separate UDM) where you store all the report/dimension/level/attribute names and issue a query (well, you need one query per name you want to have!) and exchange each multilingual text with a link to the query result… That’s a bunch of work and it’s not easy to manage.
Perhaps now you say: Why a new UDM, the names are all already translated in the UDM so why setup a new database/UDM to store them? The answer is easy: Maybe you simply need other labels in the report than you have already in the UDM. I.e. “Transactions per Second” might be a good measure name but on the report you only want “TPS” because it’s shorter… Where do you want to store this information? Perhaps in a different language “TPS” is not the correct abbreviation? So you need a different table to store this information because the UDM simply doesn’t have any place to store it!
Translating Report Manager
Microsoft did a good job to translate the Report Manager. It switches automatically the language depending on the system’s language settings. That’s great. But the report/folder names don’t switch! So your report “Sales Report” appears with the same name for German users, too. That’s not good… The only workaround we found out is that you create folders per language and place each report in with localized names in the corresponding folders. Much work, hard to support…
Translating Parameters
If you work with parameters you want different parameter captions for each language. Guess what you can do? Correct: Nothing. It’s not possible; at least we didn’t find any workaround for that but to create a report per language and well, that’s not what we/you want…?!
Free-Text Parameters
Well, a little off-topic… But did you ever try to pass free-text parameters to an UDM? Did you ever try to tell your users that they have to enter “[Time].[Year].[2005]” instead of “2005” when they want only 2005’s data? Then you have to deal with strtomember() functions and so you loose the functionality of the query designer… Or do you see any alternative for that? I don’t…
So thanks for my colleagues at Software4You (http://www.software4you.com) for providing input for this post. We are all waiting for some feedback (from Microsoft?) about what is planned in the future to address these issues…
Thomas