Tag Archive for Reporting services

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:

image

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.

image

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.

Preparation
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 :).

backupdb

 

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.

backupkey

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.

r2db

r2db-2

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

restoreky

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 :)

reportserver

reportmanager

Presenting Trend with Reporting Services

I posted KPI report in previous entry, something that really cool in SSRS 2008. There is another method to display a trend without much attention on number and achievements. It’s called sparklines. There are many occasions when decision maker only need to know the trend regardless of actual values. A simple visualizations can help them to grab the fact quickly.

Below is an examples of sparklines:

sparklines1

How to create a sparklines report? It is actually simple. In general, there are 3 steps:

  • Create a chart of your choice. Lines chart is a popular form. Bind it to the data of interest such as sales amount, total cost, etc.
  • Squeeze the chart as small as possible but still maintain its readability. Remove all unnecessary details like X and Y axis, legend, series label, etc.
  • Drag and drop the chart in to data area of your report and tidy up for better presentation.

The good thing about sparklines is its dynamic behaviour. Since it is located inside data area, it follows whatever grouping setup in rows and column. The sparklines adopts to drill down report setup along with its grouping, like the following example:

collap expand

The most important thing of creating sparklines are data binding and removing unnecessary chart properties. In the following picture, the chart is bounded to Sum(SalesAmount). I removed X and Y axis and also its title. This is a straight forward task by right clicking every property and select disable or delete.

delete

The last step is to add additional column in report table for sparklines container. I use “Total Trend” as presented in the first picture, then drag and drop the chart in to column and tidy up for better visualization. What about source code? Download sample .rdl file from here:

As usual, it needs AdventureWorksDW2008 sample database which freely available from codeplex.

KPI Report with Reporting Services 2008

Reporting Services 2008 brought many enhancements for data visualization. The queen is Gauge, which is ideal tool to create scorecard and KPI. Imagine the report popping up in front of manager with colourful and fancy indicators like this:

thermo1

Gauge is only one among many other things. Charting is more fun than ever, with many 3D effects and customizable areas. I also like new feature such as calculated series that deliver moving average report in minutes. No more complex expression to calculate median or moving average data.

Gauge also comes with many type such as radial type with speedometer style. I can display sales indicator and turn the pointer to red, yellow, or green like this:

gauge1

I did demo and discussion about this topic in Singapore SQL UG meeting on June 25, 2009. As I promised to upload my slide deck, you can grab it here:

Of course I release the source code too for deeper exploration. Apologize that I don’t have enough passion to tidy up the reports for best appearance. It is available in my skydrive:

All reports need AdventureWorks2008 and AdventureWorks2008DW sample, available to download from CodePlex.

Below are some pictures from the meeting:

SQLUG-25Jun 2009-06-25 006

SQLUG-25Jun 2009-06-25 009

PPT from CTU 2009: Reporting with Data Mining

Well, traditional reporting is quite common. It connects to relational database, perform calculation and agregation, then display it in a nice and neat report. What is data mining reporting anyway?

It is also a report, but connects to mining model rather than traditional RDBMS. Example: a model to forecast the customer behavior created in SSAS. It predicts whether a customer will favor buy or not buy toward our product. Then we have another dataset of our potensial customers. We want to predict their behavior, will they favor BUY over not buying?

We can use SQL Reporting Services, connect to decision trees mining model inside SSAS, and use potensial customers data as input. The result is predicted behaviour and probability whether Mr.Bob will BUY (90%) and Mr. John not buy (60%). Another cool tool is Excel Data Mining Add-In. It uses Excel 2007 as data mining client tool, very nice and intuitive for end user. There is a very nice overview how does it work, read it here.

I also presented this tool, together with SSRS data mining reporting at Singapore CTU on May 23, 2009. The crowd were very enthusiast about this guy. Nice tool very end user power user, event for IT Pro.
My PPT can be downloaded here:

Sharepoint – Reporting Services Integration

I did presentation for MaxIT event on May 29, 2009 and one of the interesting discussion was SharePoint integration with Reporting Services. I know that SharePoint is hot here in Singapore. I also did demo on some new features of SSRS 2008 such as tablix and charting. My presentation slide can be downloaded here:

There are several advantages of integrating SSRS with SharePoint:

  • Seamless UI for end user. They do not need to jump from one screen to another to open report and SharePoint dashboard at the same time.
  • All reports are deployed as document library, so it follows all SharePoint library setting. It saves lots of time instead of managing report in separate report manager.
  • All reports are under SharePoint security setting, giving better consolidated administrative from SharePoint perspective
  • Render reports inside web part. It makes building SharePoint dashboard faster and easier.

What do we need to configure this:

  • SharePoint Services 3.0 or MOSS 2007, It’s better if upgraded to SP1 or SP2.
  • SQL Server 2005 Reporting Services with SP2, or Reporting Services 2008.
  • Reporting Services add-in for SharePoint. There are 2 versions of add-in: SSRS 2005 and SSRS 2008.

Configuration can be simple (all in one box) or scale up to separate boxes. Below is the logical architecture if SSRS and SharePoint are installed on separate boxes:

ssrs-sharepoint

Assuming that SharePoint already installed, below are the explanations to do integration.

Install SSRS 2005 + SP2 in the SharePoint box, or separate machine. Just make sure to select “Install Report Server but do not configure” when doing this. The default configuration is native mode instead of SharePoint integrated, so please take note. Once SSRS installed, upgrade to SP2 (minimum) or directly to SP3.

In the case of SSRS 2008, SharePoint integration feature is already built in. Upgrading to SQL 2008 SP1 is recommended but not compulsory. Another improvement of SSRS 2008 is guided installation wizard. Once the installation CD is plugged in, there will be a menu to install SSRS in SharePoint mode instead of native stand alone.

Configure SSRS to integrate with SharePoint. Open SSRS configuration manager and connect to SSRS instance then create a new virtual directory for ReportServer. This should be the address of existing IIS website where the report server to be configured.

reportserversharepoint1

Configuring Windows Service and Web Service identity is straight forward. Use Network Service as identity account if it is a single box installation. In the case of scale up scenario, it needs to provide a dedicated SSRS service account from AD and use it as SSRS identity.

reportserversharepoint2

The next step is to create a new database for ReportServerDB and make sure to specify database as SharePoint integrated mode. It has to connect to existing SQL Server database instance.

reportserversharepoint3

It is not necessary to configure IIS website for Report Manager because SharePoint is our report manager now. Email and execution account configuration can be ignored in the meantime, specially if we do not use automated report delivery by email.

Another part of configuration is inside SharePoint central admin. Install SharePointRS.msi (SharePointRS_X64.msi for 64 bit) in SharePoint box. This package needs to be installed at every front end web server if SharePoint is configured as web farm. By the way, this file is Reporting Services add-in for SharePoint I mentioned earlier. Once installation finished, do IISRESET and open SharePoint Central Admin. A new Reporting Services configuration menu is available.

cassrs0

SharePoint has to know the address of Report Server which is under IIS virtual directory. Security configuration should be in windows integrated mode. This can be done under “Manage integration setting” link.

cassrs1

SharePoint also has to connect to ReportServerDB that already created earlier. Specify the name of SQL Server instance and click OK and specify windows account to connect to SQL Server.

cassrs2

There is another note for multiple boxes scenario: Reporting Services need to have local access to SharePoint object model in order to communicate with SharePoint. It means SharePoint binary has to be installed in SSRS machine, but left it empty without web application. It just needs to join existing SharePoint farm during installation, it is not necessary to create a site collection.

That’s all configuration needed. Now time to test it. Create a new library in SharePoint with the type of Report Library, for example “Sales Report”. Open existing SSRS source code (with Visual Studio of course) and deploy them to SharePoint. The tricky part is now the deployment location has changed. Remember that SharePoint is our report manager now. Below is the deployment configuration in Visual Studio:

vsdeployrs2sharepoint

What is the difference? Now it connects to SharePoint site as report server instead of http://myserver/reportserver. Data source and report folder location should point to address of “Sales Report” which is created earlier. The Sales Report library should display all reports after the deployment completed successfully.

reportdeployed

Clicking “Product Line Sales” bring up SSRS report under SharePoint, nice :).

ssrsinsharepoint

I don’t like full screen report, can we render them as web part?

Yes, report can be rendered as web part, and with all SharePoint flexibility, just drag and drop to any zones to display them. This web part is actually part of SharePointRS.msi installed previously. The name is Reporting Services viewer web part.

addssrswebpart

One page may render multiple web part and display them in a nice dashboard as follow:

reportwebpart

Managing the report is easy. Everything can be done under SharePoint. Click one of the report and java script popup tells us what all related reporting services setting can be done.

sharepointreportadmin

Nice feature eh? Now we have another more reason to implement BI under SharePoint :).

One more thing, Microsoft does provide a comprehensive guide to achieve this integration here.

Extract and Transfer RDL files from SSRS

I was in the situation when I had to modify some reports, but I did not have the source code (RDL) files on hand. I tried to go to SSRS server, and looking for download or save-as menu. I was expecting there are some ways to download the report definition so I can modify it under Visual Studio.

Unfortunately not, there is no built-in features in SSRS that for download or extract report from the server. Once deployed, it is there forever. I just thought to create small application using SSRS web services or API to extract the definition. But I was lucky enough that Jasper Smith created an amazing tool for this purpose.

I downloaded the RSScripter and install it on my machine. It can connect to both SQL 2000 and 2005, and generate the RDL, user, roles, and also the data source. This free tool is really fantastic.

rsscripter1

I can also use this stuff to transfer the SSRS content from one server to another. A very good toolkit for server migration and deployment. The configuration is under option button, specify to transfer and as well as destination server.

rsscripter2

Configuring SSRS 2005 on Vista and Win Server 2008

I never think that this is a difficult or exceptional. I never configure it before, because my job does not really require to deploy such configuration. Someday in the morning, one student asked my this question: Why I cannot run Reporting Service 2005 on my Vista? This guy used Vista as his development machine and SQL Server was running fine except the SSRS.

Well, I saw some documentation so I’m sure there is away to do it. The problem is not Vista specific. This is IIS 7 matter instead of Vista. Vista runs IIS 7, and so does Win Server 2008. IIS 7 has several breaking changes compare with IIS 6, and some developers do not really know how to deal with some changes.

I decided to configure it on my Vista 64 bit desktop and worked like a charm. Nothing special, just couple of IIS 7 configurations and security setting. Here are the steps:

Step one: Make sure the Vista machine already upgraded to SP1. In Win Server 2008, It comes with SP1 by default.

Step two: Configure role services on IIS 7. This menu is accessible from IIS 7 setting or Control Panel > Program and Features. Make sure that the following features are activated:

IIS 6 Management Compatibility:
IIS 6 WMI Compatibility
IIS Metabase and IIS 6 configuration compatibility

Application Development Feature:
ASP.NET
ISAPI Extensions
ISAPI Filters

HTTP Features:
Default Document
Directory Browsing
HTTP Errors
HTTP Redirection
Static Content

These are the screen capture in my Windows Server 2008 machine:

ssrsiis71ssrsiis72

The next step is to install SQL Server 2005, including Reporting Services. proceed with SQL Server SP2 after that. After the installation finished, browse http://localhost/reports. The reporting services should run properly.

However, I noticed there are several small issues that may varies from machine to machine. If you got trouble to load SSRS after the above procedure, do some checking as follows:

Make sure the Reporting Services web sites (both reports and report server) run on top of the classic mode application pool. IIS 7 comes with 2 mode to run the pipeline: classic and integrated. The application pool for SSRS has to be in classic mode.

 iis7ssrs3

ssrsiis74

ssrsiis75

The complete step by step guide from Microsoft is here.