Tag Archive for SQL Server 2008 R2

KPI Report with Reporting Services 2008 R2

There is a new gem in SQL Server 2008 R2: Indicator control. It enables us to create KPI style report in easy way. You may ask how is it difference with Gauges that already exists in SQL Server 2008?

Well, Gauges is cool by displaying speedometer style indicator. But sometime they are just too much. I don’t want the fancy circle style, instead I want it to be a simple traffic light or dot indicator like this:


If you are a Reporting Services veteran then you know what to do: upload couple of traffic light symbols and bind them to a cell. Then write an expression to hide or unhide each symbol accordingly. We don’t need to do this anymore in R2. Here is the way to achieve it in easy way.

First, as usual we need to provide a cell as placeholder for this indicator. Second, place indicator control inside the cell then bind them to corresponding columns in the dataset.


The last step is configuring the expression logic for them. By default is only showing 3 state: Red, Yellow, and Green. But we can add as many states according to requirement. Once its done, just render the page to show KPI report.

Here is my sample KPI report. It requires Reporting Services 2008 R2 and AdventureWorksDW2008 database.

The same concept also applied to display trend in R2. I posted about creating trend sparklines in SSRS 2008, which is require to do a bit workaround. Not in R2 version, because It has dedicated sparklines control as well.

Upgrading to Reporting Services 2008 R2

Well now SQL Server 2008 R2 has been released, now what? I want to take advantage all of cool stuff in Reporting Services 2008 R2. The obvious thing is I have to upgrade the existing report. There are couple of upgrade path supported:

  • From SSRS 2005 to SSRS 2008 R2
  • From SSRS 2008 to SSRS 2008 R2

In either path, there are couple of methods that we can use:

  1. In-place upgrade, easy but I strongly not recommend this.
  2. Side by side by attach and detach the database. This will be my main focus in this post.
  3. Obtain report source code, convert with VS2008 and deploy to new server with R2 enabled.

First method was definitely not my choice because does not provide roll back scenario. The 3rd method is nice but it means I have to reconfigure all security settings I have done. I did the second method which is pretty easy as long as we plan carefully, and retain all report permission intact.

I had SSRS 2005 when decided to upgrade. Make sure that it already upgraded to service pack 2. The reason is SSRS 2008 will upgrade the database schema automatically as long as it’s already SP2.

Step 1
Backup ReportServer and ReportServerTempDb from old server. Actually the first one is the most important, but just take both backup just in case :).



Step 2
Backup encryption key from old machine. It’s needed later when we restoring the configuration on R2 machine. The key is important to prove that we are the legitimate person to perform restoration.


Step 3
Install SSRS 2008, this can be in the same machine with different instance or separate machine. Take note that we can actually run it as a service only and still use SQL Server 2005 as a storage for ReportServerDB. I won’t explain about the installation steps because it’s not the interest of this post. I assume that most DBAs know how to do it, otherwise do your homework in search engine :). Make sure to not configure RS during the installation because we will do it manually later.

Step 4
Restore both databases to a new machine. Actually it’s not necessary to restore it to SQL 2008 R2 storage because RS 2008 R2 supports backward compatibility. There are many cases that company just want to have a new RS R2 but still want to utilize old SQL 2005 storage, which is fine. In my case, I restore them to the same instance of SQL 2005 with SP2 because I could not get SQL 2008 one because of licensing issues. Take note that I restore them as different name for example: ReportServer2008R2 and ReportServer2008R2TempDB. This to avoid overwrite of existing RS 2005 DB. If you have separate machine you do not need to pay attention of those naming anyway.

Step 5
Configure Reporting Service 2008 R2. Open Reporting Service Configuration manager to configure virtual directory, service account, and the most important thing: connect to existing ReportServer2008R2. I won’t explain in detail about the basic configuration here, since most DBA should be familiar with it.

The one that needed attention is to connect to existing database rather than creating a new database. In my case, I connect to ReportServer2008R2 which is the one I restored earlier. And here is the magic: RS 2008 R2 will convert and upgrade all RS 2005 schema to R2 automatically. All reports and security configuration are safe inside.



The last thing is to restore the encryption key. We won’t able to start the service without supplying a valid key.


Step 6
Test your server and report. Navigate to http://yourservername/reportserver to check that the service is alive. Then open http://yourservername/reports and see that all reports has been migrated to R2 :)