I need to translate a user¡¯s regional setting into one of our own language codes before I send it through as a filter to the model query. If our language codes were the same, the filter would look like this in the report filter -

Language Code = GetUserCulture()

Which translates to this in the database query (for us english) -

table.language_code = 'EN-us'

And of course I need it to look like this -

table.language_code = 'ENG'

I would like the logic to be globally available to all report writers (ie not forcing each report writer to have an iif or case stataement). I was thinking custom assemblies or maybe a database function, but at this level of the filter, I cannot seem to figure out how to embed a database function call to apply to the filter criteria like this

Language Code = dbo.ConvertFcnIWrote(GetUserCulture())

Or how I would access the custom assembly in the filter expression.

Do you have a recommended implementation for this situation


Toni Fielder

Re: Can I apply a database function or assembly call in an Expression for Filter Data?

Shyam Sundar

Have the language mappings in a resources file (.resx) which is anm xml document. Create an assembly in any .Net language and write a function which will take the user language and key as input and return the corresponding language text from the resource file.

Then copy that assembly to Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies folder if you want to use it from Visual studio or copy it to Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportServer\bin folder if you want to use from report manager or any other external application.

In your report, go to Report -> Report Properties -> References tab and browse to the above folder and select the dll file and provide the namespace.classname and instance name (say objLangConv). In your code you can refer to that function in assembly by using the syntax Code.objLangConv.GetText(User!Language.Value) where User!Language (global variable provided by SQL Reporting services) is the language settings on the client's machine.


Re: Can I apply a database function or assembly call in an Expression for Filter Data?


Actually, since you already have the transalations in the database, you don't have to worry about the resource files. The rest of the steps described are needed, and I would definately go with a custom assembly in this case, since you want to share the functionality.

Re: Can I apply a database function or assembly call in an Expression for Filter Data?


You guys are terrific! Thanks for the information and proper direction.