February 17, 2009 at 1:16 am
I want to create a report in which user can select language and as per selection the report label and data should be changed.
Is there any inbuild setting available in ssrs 2005?
How can I change data and label language through Tsql query?
Thanks,
NRoy
February 17, 2009 at 6:51 am
I think you could almost get it to work with labels and hard-coded text with either IIF or switch statements, but for the data you would have to run it through some sort of translation program unless you already storing the data in multiple languages.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 17, 2009 at 8:27 am
I'm thinking check the collation settings for the database. Beware though, turning on collation for a database adds a lot of overhead. Even with doing that, I'm not sure you can accomplish what you are looking to do.
February 17, 2009 at 8:38 am
There is no built in multi lingual support in SSRS 2005, things changed a little in 2008.
You could get it through design of your reports by calling a stored procedure with each langauge as a parameter. So your users will execute the report with their language and I am not sure if it can be dynamic.
Kind regards,
Gift Peddie
February 18, 2009 at 12:06 am
Hi All,
I have tried to change the "language" from report properties to French for the report label but while previewing the same it does not reflect the changes in the labels.
Secondly, If I change the report language from property and open the report in xml it shows the change to French.
Can anybody please let me know why is it practically not reflecting in report preview?
Is there any other setting or configuration, which I need to change o that the changes are reflected in different languages in ssrs 2005?
Thirdly, please also suggest me any step by step approach through Sproc to implement multiple language in ssrs report.
Thanks,
NRoy
February 18, 2009 at 5:42 am
roy.neelanjana (2/18/2009)
Hi All,I have tried to change the "language" from report properties to French for the report label but while previewing the same it does not reflect the changes in the labels.
Secondly, If I change the report language from property and open the report in xml it shows the change to French.
Can anybody please let me know why is it practically not reflecting in report preview?
Is there any other setting or configuration, which I need to change o that the changes are reflected in different languages in ssrs 2005?
If you are running this in BIDS you may be getting a cached version of the report. Make sure you use a different paramater value or make a minor change in the report layout to make sure you do not get a cached version of the report.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 18, 2009 at 8:57 am
I am not sure using the property will work for all languages however you can create a View for each language and pull the data for that language as a parameter. You can use SMS to save your query and View definition for each language by clicking the arrow in the save as box in SMS. So each parameter executes a different language.
GreekCol nvarchar(10) collate greek_ci_as,
CharCol Nchar(10) COLLATE French_CI_AS
Check the thread below for all the collation you can use.
http://www.sqlservercentral.com/Forums/Topic655592-149-1.aspx#bm655749
Kind regards,
Gift Peddie
February 18, 2009 at 9:29 am
Are you trying to get SSRS to automatically translate your labels from one language (English) to another (French)?
I don't think you can do that in SSRS.
February 25, 2009 at 9:20 pm
Thankyou Lynn for posting what I was thinking. The OP is asking for automatic translation of data (label text, etc.) if I understand it correctly, yet we have a discussion about collation. WTF?
And Bob - "turning on collation for a database adds a lot of overhead". I don't follow - care to elaborate?
:blink:
Regards,
Jacob
February 26, 2009 at 6:26 am
if I understand it correctly, yet we have a discussion about collation. WTF?
Actually collation is used to render any language correctly in any application if started with translated text. So it is very simple in report using parameter to execute queries with each language. And no there is no valid automatic localization on the whole Microsoft development platform.
Kind regards,
Gift Peddie
February 26, 2009 at 7:10 am
I was under the impression that collation was a database wide setting. I have heard of instances where this caused some terrible overhead problems. Sounds like I have my terminology mixed up. Apologies if I've caused confused due to my lack of knowledge.....
February 26, 2009 at 7:41 am
Collation in SQL Server from 2000 can be applied at the column level but it was not easy but 2005 comes with collation used with DML as I posted in the thread you can add the collation to the column definition in the query and save the query for each language in SMS using the save as option.
In 2008 Microsoft have changed the bytes of the Nvarchar to the same as the .NET Char which is the same as unsigned integer in bytes 0 to 65535. So now you can change collation very easily for most languages, collation with Nvarchar can be used to render any language I have seen one with 32 langauges. The main reason collation is important you cannot use the Latin alphabet to render Chinese, Japanese and Korean and the right to left langauges like Thai and Arabic comes with different requirement.
Kind regards,
Gift Peddie
February 26, 2009 at 5:11 pm
@bob-2 - all good. Collation is specified at a number of levels - database, column then explicitly in your query if you wish. There is also a server-level default collation that new databases inherit. Collation is always "on", so there is no choice to *not* use collation. If you don't specify an explicit collation in your queries you will get the column collation (for columns) or the database collation (for variables).
Gift Peddie (2/26/2009)
Collation in SQL Server from 2000 can be applied at the column level but it was not easy but 2005 comes with collation used with DML as I posted in the thread you can add the collation to the column definition in the query and save the query for each language in SMS using the save as option.
You can specify a collation for columns and in your DML in SQL Server 2000 and if I recall correctly in SQL Server 7.0 (and probably even 6.5) - this isn't new in SQL Server 2005. And if you're going to be passing a language parameter into a reporting proc that dictates which language strings are returned (as you and others have suggested here) you shouldn't need to save a seperate copy of the query for each language.
Gift Peddie (2/26/2009)
In 2008 Microsoft have changed the bytes of the Nvarchar to the same as the .NET Char which is the same as unsigned integer in bytes 0 to 65535. So now you can change collation very easily for most languages, collation with Nvarchar can be used to render any language I have seen one with 32 langauges. The main reason collation is important you cannot use the Latin alphabet to render Chinese, Japanese and Korean and the right to left langauges like Thai and Arabic comes with different requirement.
I'm not sure what you mean about the "bytes of NVarchar changing in 2008"... are you referring to SQL Server 2008? SQL Server has always stored Unicode character data in UCS-2 format, which is very similar to (but not quite) the UTF-16 format that the .NET framework stores all strings as internally.
Collation is important when dealing with multilingual data, but please don't mistake its purpose: it's primarily used to control the sorting and comparison behaviour of strings. It only has an effect on the codepage for non-Unicode data (eg char/varchar/text) - the actual codepoints used to render the string. Unicode data doesn't have or need a codepage since you can store and display almost any character in any language in it. If you go around casting non-Unicode strings from one collation to another and the codepages differ you're just going to end up with garbage.
The general approach is to simply use Unicode data types everywhere. Only worry about collation where it matters to your comparisons and sorting - don't cast your data from one collation to another unless you need to.
Unfortunately for the OP there is no automatic string translation feature in SQL Server - that would be a bit much to ask from most RDBMS packages out of the box. I'd probably go down the route suggested by most here: build a "string table" in the DB and have your reporting surface pull out the data in the correct language via your reporting procs with a language param. The task of actually translating the strings will need to be done seperately beforehand.
Regards,
Jacob
February 26, 2009 at 5:37 pm
(You can specify a collation for columns and in your DML in SQL Server 2000 and if I recall correctly in SQL Server 7.0 (and probably even 6.5))
SQL Server 7.0 to change collation requires a reinstall.
I'm not sure what you mean about the "bytes of NVarchar changing in 2008"... are you referring to SQL Server 2008? SQL Server has always stored Unicode character data in UCS-2 format, which is very similar to (but not quite) the UTF-16 format that the .NET framework stores all strings as internally.
Actually NO even SQL Server 2005 Nvarchar was not UTF16 in bytes look up collation in the MSDN pages for SQL Server 2008 moving to the same bytes with the .NET char is a feature we requested.
Collation is important when dealing with multilingual data, but please don't mistake its purpose: it's primarily used to control the sorting and comparison behaviour of strings.
Tell that to the Cyrillic, Greek and Celtic users in Europe, Hebrew, Arabic, one version each of Japanese and Chinese.
The general approach is to simply use Unicode data types everywhere. Only worry about collation where it matters to your comparisons and sorting - don't cast your data from one collation to another unless you need to.
Send me the code for a web application you developed in .NET using Unicode data only for 20 langauges I would like to send it to Microsoft. I still get emails in languages I don't know from automatic localization pages.
Kind regards,
Gift Peddie
February 26, 2009 at 8:10 pm
Gift Peddie (2/26/2009)
SQL Server 7.0 to change collation requires a reinstall.
I'll take your word for it. It's been close to 10 years since I last used that version - happy to concede this point.
Gift Peddie (2/26/2009)
Actually NO even SQL Server 2005 Nvarchar was not UTF16 in bytes look up collation in the MSDN pages for SQL Server 2008 moving to the same bytes with the .NET char is a feature we requested.
I didn't say SQL Server used UTF-16 for Unicode char types - I said it used UCS-2. Had a quick check on MSDN - can't find anything that mentions anything that sounds like your description. Here's the overview of new collation features and changes from 2005-2008: http://msdn.microsoft.com/en-us/library/ms143503.aspx. Can you point me at a source for the change you are talking about?
Gift Peddie (2/26/2009)
Tell that to the Cyrillic, Greek and Celtic users in Europe, Hebrew, Arabic, one version each of Japanese and Chinese.
Sorry, I must be missing your point. Huh?
Gift Peddie (2/26/2009)
Send me the code for a web application you developed in .NET using Unicode data only for 20 langauges I would like to send it to Microsoft. I still get emails in languages I don't know from automatic localization pages.
Again, huh? What's the relevance of this statement? I'm afraid I can't send you code - most of the stuff I've developed belongs to my employer. Do you have a specific design point you want to discuss of such an application? Happy to do that...
We're also hi-jacking this thread a little - I'm sure most of this doesn't change anything for the OP - he's still stuck with no easy solution. Apologies to anyone reading for the digression.
Regards,
Jacob
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply