Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Building a Dashboard in SQL Server Reporting Services

MSSQLTips author Rob Fisch By:   |   Read Comments (6)   |   Related Tips: > Reporting Services Dashboard
Problem
Your data warehouse is rock solid. You have multiple sets of fantastic reports. Departments utilize their own designated reporting areas, and you've increased productivity and value (ROI on the BI program) by focusing operations on critical areas by offering specialized filters and sorting as well as exception reports.

Great!

But top level executive management still doesn't have a clear picture of what direction the company is going in and can't make the strategic decisions needed to plan into the future. Your voluminous sets of reports look to them like the local telephone directory. They just don't have the time to wade through every report in all the (wonderful) departmental reporting solutions you have created.

Solution
Time for a Dashboard. (Or several).



Before we begin, let's begin with a basic definition of what a dashboard is. I like this one...

"A digital dashboard, also known as an enterprise dashboard or executive dashboard, is a business management tool used to visually ascertain the status (or "health") of a business enterprise via key business indicators. Digital dashboards use visual, at-a-glance displays of data pulled from disparate business systems to provide warnings, action notices, next steps, and summaries of business conditions." -Wikipedia.com


The Hard Part: Determining Content and Audience

The hard parts of building a dashboard are not technical at all. They are determining what key indicators should be presented and figuring out who the audience should be. For that, you need to work with the top level executives (perhaps through your boss and chain of command), on planning what these key indicators should be and who should be allowed to see them.

You may find you have to create one dashboard for executives and another for the rank and file (middle management and low level staff). If that is the case, then so be it. You just need to find out the score (the business requirements) and move on.

In order to determine content, you may have to ask leading questions like:

"If your BI portal could look like the front page of the NY Times, what would you like to see?"

"What are the top 10 metrics that are important to you as an individual and the Executive Management team?"

"For a top level overview, do you prefer charts and graphics, or are you strictly a numbers person?"

"Should we build one dashboard for top level executives and another for all personnel or should everyone see the same thing?"

Trust me on this, getting these answers will take longer than actually building your dashboard.

One more word of advice before we begin. Your dashboard is not your dashboard. It's the company's dashboard. As such, you need to find a high level business owner that will partner with you on it as you develop it. You need to show them your work at several points during the construction process, and make modifications when needed. Consider it a partnership.


The Tools

There are many commercial software products that provide dashboard solutions. I am sure, for the most part, they can add a great deal of value, and add a level of sophistication that is impressive. Consider them something to grow into. Even if you want to start there, you will want to try building your own mini-version, so you will more fully understand the features and functionality to look for in a commercial product. So regardless over whether you are going to "build or buy", this exercise will be useful for everyone.

I will make the assumption that you already have Microsoft SQL Server 2005 in some production worthy flavor (Standard or Enterprise). You also need an instance of Sharepoint running of one of the following: WSS2, WSS3, Sharepoint Portal 2003, MOSS 2007. Installation of Sharepoint and/or Reporting Services either in Sharepoint Integrated mode or Standard (pre SP2) mode will be outside the scope of this article.

Checklist:
  Microsoft SQL Server 2005 (with Reporting Services)
  Sharepoint (WSS2, WSS3, Sharepoint Portal 2003, or MOSS 2007)

Now that we have that out of the way, we have review some the integration features between Reporting Services and Sharepoint. From this point forward I will only be referring to Sharepoint as WSS2 or WSS3. Everything I say about WSS2 also goes for Sharepoint Portal 2003. Equally, WSS3 can be equated with MOSS 2007 in this regard.

If you are using Reporting Services in Native Mode, then you will likely want/need to use Sharepoint WSS2 as your portal/dashbaord. If you are using Sharepoint Integrated mode, by it's very nature you will be using WSS3 (or MOSS 2007).

Is it possible to cross cross these, either entirely or partially? Well yes, especially if you are good with Sharepoint Designer. But that is outside the scope of this article. For this article we will assume that if you are not using Sharepoint Integrated mode of SQL Reporting Services, then you are using WSS2.

Is this a bad thing? No.

Is it worth upgrading? (Ah, the eternal question.) It depends. If you do not have Reporting Services in Sharepoint Integrated mode, then I suggest you at least try it on a test server or virtual machine. It's worth the experience. You need to go through that experience in order the answer the "Is it worth upgrading?" question for yourself.

Native Mode

You need to install the Reporting Services web parts in order to get the Report Explorer web part. I really have no use for the Report Viewer web part, but it gets installed at the same time.

The installation is a little tricky. Follow these instructions which are direct from SQL Server BOL:

1. Copy the RSWebParts.cab to a folder on the SharePoint server. The .cab is installed with Reporting Services. By default, it is located in the C:\Program Files\Microsoft SQL Server\90\Tools\Reporting Services\SharePoint folder. You can copy it to any folder on the SharePoint server, and then delete it later after you install the Web Parts.

2. On the computer that has the installation of the SharePoint product or technology, open a Command Prompt window and navigate to the folder that has the Stsadm.exe tool. The path will vary depending on which version of Windows SharePoint Services you are running. If you are using Windows SharePoint Services 3.0, the path is C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN.

3. Run Stsadm.exe on the .cab, using the following syntax:

STSADM.EXE -o addwppack -filename "C:\ Program Files\Microsoft SQL Server\90\Tools\Reporting Services\SharePoint\RSWebParts.cab" -globalinstall


Sharepoint Integrated Mode

If you got through the Report Services installation in Integrated Mode, then you don't need to do anything else. I recently saw a very detailed (Word 2007) document on all the installation screens here.



The Sharepoint Dashboard

Below is an example of a Dashboard I created against Microsoft's sample Adventure Works database.



The top right report list is a scaled down view of the actual reporting library. The other three objects are actual Reporting Services charts run in real time.

They are displayed using standard Page Viewer Web Part in Sharepoint (not the special Reporting Services web part). I find this much easier and more direct than the Reporting Services web part.

What you don't see is the standard Report Server toolbar. That is because I have hidden it by appending the URL Access Parameter option after the Report Server URL path with the following syntax:

&RC:toolbar=false

In order to use this, you must append it to the Report Server URL path (not the Report Manager or Sharepoint server path). The following is an example of that configuration.



The other options I used were in the Appearance section of the web part. You may need to adjust the default settings for height and width of the web page. Additionally, you will want set the "Chrome Type" to None.




Native Mode

In a WSS2 Sharepoint dashboard you use the Page Viewer Web Page exactly the same way as in the Sharepoint Integrated Mode. Use the Page Viewer Web Part to display your charts and reports opened up.

In order to display the Reporting Services library, you cannot use a document library. Instead, you have to use the Reporting Services Report Explorer mentioned above (under "The Tools" section).

Drag the Report Explorer web part to one of the Web Part Zones.



  • In the configuration of the Report Explorer web part, enter the Report Manager URL (Not the Report Server URL).

    I'll digress for a minute here. In Native Mode, the URLs of the Report Server and Report Manager are as follows:
Native Mode Reporting Servers
Starting URL path
Report Manager
http://YourServerName/reports
Report Server
http://YourServername/reportserver

  • Enter the Start Path where the reports were deployed. You find this in the Report Designer in the Configuration Manager. (Right click on the solution and select properties.)

  • Change the View Mode the List.






  • Here's the WSS2-based dashboard. I'll show the WSS3-based dashboard right below it again so you can see the similarities and differences.


    Sharepoint WSS2 (Native Mode) Dashboard example:



    Sharepoint WSS3 (Sharepoint Integrated Mode) Dashboard example:

The primary difference is the appearance and functionality of the report library web part (top right) of each page. In WSS2, the Report Explorer pops open a  new browser when the user clicks on the report. That's actually an advantage over the WSS3 library. In contrast, the WSS3 library is more compact and appears in a single column, where the WSS2 always takes up 2 columns.

Dashboarding

At this point, we have a single high level web page with various charts and report libraries. In the reports we can add drilldowns and drill-throughs or even links to other sub-level dashboard pages that get narrower in focus. But in Sharepoint, how are we going to create web pages for other dashboards?

Well you could create another site for each sub-level dashboard. And you may want to do that, as your portal scales up. But it is not necessary to do in the beginning or even ever, if you plan carefully.

Here's where you begin:

Instead of a new site, start by creating a new document library.  When you create the new document library, you are prompted for a Document template. Change the default type to Web Part Page.

When creating a document library for additional dashboards, choose the Web Part Page document template. See screenshots below.
WSS2

WSS3



Then start a new page in the document library. When you do, you will be prompted for a web part layout. Choose one that seems appropriate for the dashboard style you want. Then, you can create a new Web Part page for each dashboard you want to build. In WSS2, you cannot apply permissions to each page, so you will to plan your security setup and the functional hierarchy carefully. Perhaps you will need multiple Web Part Page libraries in order to allow for different security boundaries.  In WSS3, you can apply security granularity right down to the document level within a single document library so the planning may be a bit different.


Let's Review

  • Determine the dashboard content and audience by working high level executives to develop your Key Performance Indicators.
  • You will need at least one instance of Microsoft SQL Server Reporting Services and one instance of Sharepoint (WSS2 or WSS3).
  • Plan your functional areas and security boundaries, and build sites and document libraries accordingly.
  • After gathering the business requirements, create several charts, tables, graphs that will depict the key items being measured.
  • Lay them out in Web Part Zones on the home page of a Sharepoint site or a Web Part Page, eliminating the toolbar if desired, to minimize screen real estate used.
  • Create drill-throughs and links to other sub-level dashboards and reports.
  • Before implementing the dashboard solution, stage a demo and get the advice of a couple of top level Executives on content, presentation and security. Let them think about it and get back to you.

Next Steps
  • If you missed them, review Part 1 (Security and Design Implicates) and Part 2 (How to Show and Hide Reporting Objects) of this series on Portal Management.
  • Stay tuned to Part 4 of this Portal Management series. The topic will be "Developing & Publishing reports in Teams". 
  • Take a look at these other Reporting Services tips.

 



Last Update: 10/29/2007


About the author
MSSQLTips author Rob Fisch
Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, December 03, 2008 - 9:48:42 AM - dbielski Read The Tip

This is a great post.  However, my problem is that the company I am consulting for is still on SQL Server 2000.  I have been having trouble trying to find an MS dashboard solution for 2000.  I was directed to DDRK 2.01 but that no longer exists. I was then pointed to DDRK 3.01 which is now under SharePoint; however, to use this, I have to have SQL Server 2005 (I think).  HELP!!!!!!  Can anyone tell me what MS solution I can use to develop a dashboard using SQL Server 2000?

 Thanks!


Friday, December 19, 2008 - 11:54:36 AM - Ismamad Read The Tip

 Hello,

I've installed the SQL Server 2005 on a WIndows XP Professional machine.

Im quite new to SSRS and trying to solve a problem realted to the web portal (dashboard) connected to my data warehouse, the thing is that I have a working portal (witch is accessible using the browser with the url http://localhost/dwind, witch then redirects me to the ASP page) but now I need to duplicate the datawarehouse in order to let one in production and make another one for testing issues.

Explaining the objective better: one server, two datawarehouses (production and tests), two portals, two report directorys inside Report Server, two Data Sources (again production and tests).

Problem is that I can't configure the portal to work with the test directory, it keeps calling the productions reports.

 I've mannaged to make the two portals, the two data sets and Data Warehouse.

 The  "http://localhost/dwind" links to the production portal and the "http://localhost/dwind_dev" to the development portal (tests).

When cliking on a link in one of the portals it calls the same report... meaning that the development portal is not correctely configured, instead of calling the test directory calls de production on.

 Next is a peace of the configuration file in witch we can see the connection strings to the data warehouse.

(..)

<add key="Connection" value="Data Source=PC-DWIND-DEV;User ID=dwind;password=dwind2008;Initial Catalog=web_dwind_dev"/>
  <add key="report_server" value="http://pc-dwind-dev/ReportServer"/>
  <add key="user_report_server" value="Worker"/>
  <add key="password_report_server" value="dwind"/>
  <add key="portal_version" value="Version: 2.0.0"/>
  <add key="MyReportViewerDomain" value="critical.pt"/>
  <!-- for open pdf files-->
  <add key="pc_name" value="pc-dwind-dev"/>
  <add key="web_server_name" value="web_dwind"/>
   <!-- Log files location -->
        <add key="logFiles" value="C:\dwind_v2\production\log"/>
    </appSettings>
    <connectionStrings>
        <add name="web_dwindConnectionString" connectionString="Data Source=PC-DWIND-DEV;Initial Catalog=web_dwind_dev;User ID=dwind;Password=dwind2008" providerName="System.Data.SqlClient"/>
        <add name="dwind_devConnectionString" connectionString="Data Source=CBRSQLPROJ\SQLPROJ;Initial Catalog=dwind_dev;User ID=dwind;Password=dwind2008" providerName="System.Data.SqlClient"/>
    </connectionStrings>

    <system.web>"

(..)

Do I have to configure a user for the database? can't I use the windows account to log on?

 I hope this can help you understand my problem, because it is a bit complex :(

 

Thanks for any reply in advance,

 

 


Wednesday, January 13, 2010 - 10:11:25 PM - aiynz Read The Tip

 hye,

have u heard about dashboard? n how to build it? i 've a poblem to develop dashboard using sharepoint....

if u know about it, please inform to me ASAP....

tq.


Friday, September 10, 2010 - 1:17:05 PM - David Kearns Read The Tip
Great article! Thank you for taking the time to post this.

It was a step in the right direction while building out a dashboard reporting solution.

 


Tuesday, May 15, 2012 - 9:13:16 PM - Ryan Read The Tip

Thanks, great article.

Just a note that when executing the STSADM.exe command line to install the web part you need to include the -URL parameter or you get the error "This solution contains resources scoped for a Web application and must be deployed to one or more Web applications."

It needs to know which web.config file to modify, i saw the solution here http://blogs.msdn.com/b/jjameson/archive/2007/06/17/issues-deploying-sharepoint-solution-packages.aspx


Monday, February 04, 2013 - 10:58:35 AM - Konstantin Read The Tip

I am a MS SQL Developer(BI). Before I used the Reporting Services. This was like the stone age.

Now I use QlikView. My life has become much easier and more pleasant. 

 

http://www.qlikview.com/

http://qlikviewpodcast.podbean.com/

 

Convince yourself.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.