Jeroen ter Heerdt

Multilingual SSRS reports – Scenario 1: Assembly

This is the second post in my series about making SSRS report multilingual. You just missed the first post, the introduction and comparison of the solution scenarios.

This solution consists of an assembly registered in each report that should support multiple languages / localization. The assembly retrieves translation from a source, such as a data source, resource file, web service or online translation service. Anything is possible here.

This solution is limited in the fact that it will not translate dataset results nor parameter prompts. In addition there is an impact on the report creation process as you will see down the line. The upsides are that it is a relatively straightforward solution that has only little impact on report rendering performance.

1. Creating the assembly
To implement this solution we first need the assembly that will do the actual translation / localization. To build this assembly fire up Visual Studio and create a new Class library project. I will use C# here (and in all my samples on this blog, but another language would work just as well). I named the project SSRSMultiLingualAssembly.

When the new solution and project has been created, rename Class1 to something that makes more sense. I renamed it to SSRSMultiLingual.

Open the class and add the following method to it:

What you would need to do is implement the class and translate the item using the cultureInfo and return the translated result. This is very dependent on your actual situation and also fairly independent on the actual scenario you choose to implement.

For now, let’s continue to the SSRS side of things to tie things together. Later we can deal with actually making it do something.

After you have added the code, build the solution and make sure there are no errors.

2. Copy assembly to the SSRS folders

To make the assembly you have just built assessable from reports, you will need to copy it over to the SSRS directories, including any resource files or whatever your assembly needs to work. Of course you could make this part of a custom build action and kind of automate this action, but for now here is the manual method.

Copy the assembly into the Report Server path: C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin
(this path may change based on installation location and version of SQL you have installed). This will register the assembly with the report server, which is strictly only required after deployment of the reports to the server and not during design / development time.

In order to make it accessible during design time, the assembly also needs to be copied to the report designer folder. Copy and paste the assembly to C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies (again, this path may change based on installation location and version of SQL / Visual Studio you are working with). Note the (x86) in C:\Program Files (x86), since the same directory also exists in C:\Program Files if you are working on a x64 system (which I assume you are).

By the way, if you use reporting services in native mode (i.e. not integrated with SharePoint) you also might have to copy the same assembly to C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportManager\bin. I have not tried this since I am running SSRS in SharePoint integrated mode.

Note that you could also add the assembly to the global assembly cache (GAC).

3. Add the assembly to your reports
In order for your reports to successfully use the assembly to translate text open a report in Report Designer (your Visual Studio environment).

From the report menu select report properties. In the dialog box click references.

Click add and then click on the ellipsis button (…) at the end of the newly added row. In the next dialog box (add reference) click browse, locate the assembly and click ok.

Verify that your assembly has been added. In the bottom part of the dialog enter your class name (assemblyname.classname) and set up an instance name. Note that the class name is case sensitive. Your instance name can be anything, I chose to set my instance name to ‘myML’.

Close the dialog by clicking ok.

Now that we have added the custom assembly to the report it is time to call the translate function to translate text!

4. Calling the Translate function
Now that the report has a reference to the custom assembly, let’s wire up the last part to actually translate text. Start by either adding a textbox to your report or selecting a textbox (or any other label for that matter). Right-click the textbox and select expression… (Expressions allow you to do some limited programming that gets executed when the report renders). In the expression dialog box, enter the following code:

For example, with the settings I made the expression dialog box looks like this:

Click ok to close this dialog. Never mind the red squiggly line.

Now, the big moment is there! In report designer click preview and see the results of your labor!

Come on, pat yourself on your back, you have successfully registered a custom assembly and used it in a report. That wasn’t too hard now was it?

Note that you will have to set up this expression with the correct parameter value for every single text on the report that you want to translate. This is very laborious and error prone. Also, this is the reason why this solution does not translate parameters.

5. Change Reporting Services configuration and deploy report
Once you have completed the report and am certain it works OK it is time to deploy the report to the report server. For the assembly to work there you will need to have the assembly in the appropriate places (see above) make some configuration changes and you will have to restart your Report Server after any change in the assembly, including first registration.

If you do not make the required configuration changes you will get the following error when deploying:

Assuming you have already copied the assembly to the correct directories as indicated above, let’s make the required configuration changes. You will have to open the rssvPolicy.config file located in C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer. Open the file and find the <CodeGroup> tags. There will be a bunch of them. Basically what we need to do is add one of our own after the last <CodeGroup> tag. This is what you will have to add:

Change the Url value to match your situation. This code group gives the assembly (your assembly in the directory specified above (which you will have to change based on your installation) full trust permissions. For testing purposes you could get rid of the SSRSMultiLingualAssembly.dll part and give any assembly in the directory full trust, however in production you will have to set up the code group as specific as above, so why not do it right from the start?

Now it is time to restart the Reporting Services service. To do this either use services.msc or use SQL Configuration Manager.

Now, deploy the report and render the report. You can test different languages by switching your Internet Explorer to another language (Settings à Internet Options à Languages).

Rejoice! You have successfully implemented this scenario of localization of SSRS reports. It may be time to check out the other implementation scenarios.

 

 

 

 


Multilingual SSRS reports

When thinking about supporting multiple languages for SSRS reports, most people think about changing the display language of one or more of the following items:

  • Labels (text boxes, axis labels, table headers, etc.)
  • Dataset results
  • Parameter prompts

When considering presenting SSRS reports in multiple languages, one also needs to consider where to store translations. Translations can either be:

  • stored in a data source (database, cube) or resource file, either accessed directly from the report or through a service call
  • retrieved from an external translation service, such as Bing Translate

In most solutions retrieving a predefined translation would be preferable to doing an automatic translation using an external translation service, since the quality of the results can be questionable. A scenario I come across often is that the external translation service is only used when a predefined translation is not available. The scenarios discussed in this series of posts do not pose any requirement on how the translation is retrieved. Reporting Services can pass the user’s language setting along. That can then be used to get the right translation.

Additionally, some of the items considered when thinking about a multilingual SSRS solution are:

  • Can the report be developed once and presented in multiple languages? All of the scenarios discussed in this series provide this capability.
  • Impact on report creation process. Does the solution chosen require manual activities when designing the report?
  • Performance
  • Implementation complexity

In this series of blog posts we talk about options for implementing multilingual SSRS reports. Below is a quick scoring of each option on the above capabilities and requirements. Of course the importance of requirements and the correct choice depends on the situation.

Each scenario will be discussed in a post in this series.

Requirement / Scenario Assembly Change RDL Report Definition Customization Custom ReportViewer
Translate labels (textboxes, axis labels, table headers, etc.)

Yes

Yes

Yes

Yes

Translate dataset results

No

Yes

Yes

Yes

Translate parameter prompts

No

Yes

No

Yes

Impact on report creation

High

Low

Low

Low

Impact on report rendering performance

Low

Low

Medium

Medium

Implementation complexity

Low

Medium

High

Very
High

 

Did I miss a requirement or solution? Please let me know!

Next time: Scenario 1: Assembly.

Do you want to jump to a specific scenario? Here you go:

Scenario 1: Assembly
Scenario 2: Change RDL
Scenario 3: Report Definition Customization
Scenario 4: Custom ReportViewer

Installing Master Data Services add-in for Excel 2013

I recently picked up my new laptop, which of course runs Office 2013 and Windows 8.
When you try to install the Master Data Services add-in for SQL 2012 you may run into a warning that says you need Office 2010.

There is an easy fix: just install the Master Data Services add-in for SQL 2012 SP1 (get it here: http://www.microsoft.com/en-us/download/details.aspx?id=35581 . Be sure to pick 32 / 64 bit to match your Office version.

Oh and of course you will have to get the other pre-req as well: Visual Studio 2010 Tools for Office runtime (find it here: http://www.microsoft.com/en-my/download/details.aspx?id=35594).

See http://support.microsoft.com/kb/2774422 for more information.

Meet Paul

Those of you who have attended one of my talks on BI probably know this story. I get asked about it a lot so wanted to share this more permanently. For me this story sums up the chance we have with Microsoft BI to fix one of the biggest issues in the corporate world.

Some time ago I worked as a BI Consultant on a data warehouse project at a major customer. All floors in the 20 floor office were like the ones you see in movies, all mindless, endless rows of cubicles. My cubicle was one in what they called ‘the front row’, which I think meant ‘at the central aisle’, which ran from the door to the manager’s offices in the back.

One day the door opened and someone that looked a bit like Charlie Chaplin walked out onto the floor. He was dressed like an old school English gentleman; complete with hat, newspaper and umbrella. He wore a yellowish dress shirt, blue suspenders and a brown tie with little blue bears on it. I estimated him to be about 70 years old. I am not that good at guessing ages, he might have been 75. Anyway, it was clear that he was well beyond retirement age. He looked around a bit and waited. In the back of the floor his arrival was noticed and someone hurried over to him and guided him to one of those identical cubicles a little further from where I was. Since I felt this was going to be interesting I went to get some coffee and made sure I passed along that cubicle on my way. ‘Charlie’ sat at an old computer (remember those CRT monitors?) and I saw him do something that grabbed my attention. He started Microsoft Excel version 5.0. When I got back from the coffee machine I stopped at this cubicle again and I saw him busily typing away. Some moments later a matrix printer which also stood there sprang into action and started spitting out some papers. He started to collect his stuff, took a quick glance and the papers and handed them over to guy who greeted him at the door and left.

I had the chance to peek at what was on those papers and I am no expert but to me it seemed a lot like a profit and loss statement. That got me puzzled even more, so instead of returning to my cubicle I walked over to the office of the BI manager, who was also my project lead. I described what I saw (‘older man came in, sat in a cubicle, pushed some buttons, printed some pages and left’). The BI manager looked at me and nodded: ‘You just met Paul’.

He continued: ‘Paul used to work for us and retired about five years ago. In this long employment here he made a big Excel spreadsheet that enables us to generate a profit and loss statement. We hire Paul twice a year just to come in here, push some buttons and get us that statement. We pay him handsomely for that service because we need that statement for the financial authorities here. If we do not provide the statement one time twice a year we might lose our license’.

Stunned, I looked at him and said: ‘I am going to ask you a tough question.’ He replied: ‘I know what you are going to ask so go ahead’. I said: ‘Let’s imagine that, heaven forbid, Paul dies tomorrow.’. He froze, looked me straight in the eye and said: ‘We would go bankrupt or lose our license.’

Although this might seem a little over done, this is a true story. Think about it for a moment what this could mean for you and your company. Do you think you have a Paul in your company? I am sure you have; every customer I talk to recognizes this story in some share or form. Do you have any idea what he has built and how dependent the company is on it?

It is time to find Paul, talk to him and make sure you understand what he built. If you can, migrate his stuff over to a more corporate solution. In any case, we need to get this under control. This is not a tiny little company I am describing here, this is a multi-million dollar business and the P&L statement comes from a black box Excel 5.0 sheet that Paul built and only Paul knows how to run.

MDS / DQS integration on a domain controller

Normally I would never advice you installing anything on a domain controller, let alone SQL, MDS and DQS. However if you have BI demo machine you will probably have all this (and more) running on the same box. At least I do J

If you do you will probably get this error message when you try to enable the DQS integration from Master Data Services Configuration Manager after you successfully installed DQS and MDS.

When clicking the button ‘Enable integration with Data Quality Services’ an error will pop-up:

Here is where it gets a bit confusing. If you read the error message closely, it seems that MDS is looking for a local account on your machine instead of a domain account. However, with it being a domain controller, you cannot create local accounts…

To make this work you need to do the following:

  1. Add a Windows User Login into SQL Server for [YourDomain]\MDS_ServiceAccounts.

     

  2. Then run the following query against your DQS_MAIN database, which creates a user on the DQS_MAIN database which maps to the login you just created and adds the user to the DQS_Administrator role. Of course you can also do this using the UI. Make sure to enter your DOMAIN in the query below before executing.

    use [DQS_MAIN]
    GO
    IF NOT EXISTS (SELECT * FROM SYS.SYSUSERS WHERE NAME = ‘MDS_ServiceAccounts’)
    CREATE USER [MDS_ServiceAccounts] FOR LOGIN [YourDomain\MDS_ServiceAccounts]
    exec sp_addrolemember @rolename=N’dqs_administrator’,@membername=N’MDS_ServiceAccounts’
  3. When done go back to the Master Data Services configuration manager and hit the button again. Now it should come back with:

Victory ! J

 

Excel Song – we all work with Excel spreadsheets

I recently found this on YouTube: a song about Excel. The quality of the vid is awful by the way, but I like the song. As far as I know Excel is one of two Microsoft products that have their own song!

SQL Server 2012 and SharePoint 2013 – Better Together session on repeat

June 10th, we will be hosting a SQL Server 2012 and SharePoint 2013 – better together session aimed at partners at our Microsoft office in the Netherlands! This is the third delivery, because the first two deliveries were overbooked and highly valued.

More information at http://blogs.microsoft.nl/blogs/sharepointonline/archive/2013/02/08/uitnodiging-sql-server-2012-and-sharepoint-2013-better-together.aspx

Looking forward to meeting you there!

(Please note that this session will be in Dutch..)

SQL Server 2012 Unboxing

Just about every new consumer technology device will be greeted with “unboxing” videos on YouTube. A lot of the people I talk to really need to start unboxing SQL Server 2012 and start to understand what is in the box. Most of them already have access to SQL Server 2012 and still think it is just a database. There is so much more! This post is aimed to providing a quick overview of what exactly is in the box with pointers to where you can find documentation.

  1. Database Engine (SSDE)
    First off, let’s start with the product that gave SQL its name: the database. This is without doubt the best known product of the whole SQL suite and also the most used. More often than not this is also the only product people use and know. Find out more here: http://technet.microsoft.com/en-us/library/ms187875.aspx
  2. Data Quality Services (DQS / SSDQS)
    Introduced with SQL Server 2012, DQS is a knowledge-driven data quality solution that works on the premise of specifying what defines data quality in a knowledge base and using to cleanse data automatically during ETL (see SSIS below), Master Data Management (see MDS below) processes or manually.
    See: http://technet.microsoft.com/en-us/library/ff877925.aspx
  3. Analyis Services (SSAS)
    Analysis Services is SQL Server’s analytical database or cube. It features both more traditional cubes and tabular models, provides self-service analysis capabilities and includes data mining. See http://technet.microsoft.com/en-us/library/bb522607.aspx
  4. Integration Services (SSIS)
    Integration Services is a full-blown ETL tool and can be used for all sorts of data integration solution. SSIS features a drag and drop interface to build the solution and provides a lot of components out of the box with connectors to and from just about any database, file storage or file format. If need be, you can also use the power of .NET to build the exact behavior required. SSIS also integrates with DQS to use data quality knowledge bases during ETL processes. For more info visit: http://technet.microsoft.com/en-us/library/ms141026.aspx
  5. Master Data Services (MDS)
    Master Data Services enables users to build a Master Data Management solution on top of SQL Server. MDS integrates with DQS to make data quality aspects a part of the overall MDM solution. See http://technet.microsoft.com/en-us/library/ee633763.aspx
  6. Reporting Services (SSRS)
    Reporting Services is the enterprise reporting solution that delivers web-enabled reports that can get information from a variety sources and be rendered in various formats (including Excel, Word and PDF). Also, reports can be retrieved on demand, on subscription bases or based on a alert. Find out more here: http://technet.microsoft.com/en-us/library/ms159106.aspx
  7. StreamInsight
    StreamInsight is Microsoft’s Complex Event Processor (CEP). CEP technology enables high throughput and real-time (low latency) processing of streams of data (events). Examples include financial trading, Web analytics, sensor data, etc. StreamInsight is provides a familiar development platform based on .NET to quickly start using real-time information. See: http://technet.microsoft.com/en-us/library/ee391416.aspx

That concludes the quick unboxing of SQL Server 2012. Although there is a lot more to say (about features, but also around editions and capabilities) , this should give you a good idea of what is in the box. Bottom line: there is a lot more to SQL Server than just a database!

%d bloggers like this: