SharePoint: Create Dashboard with SQL Server Reporting Services (SSRS) 2008

Posted by: Pravinkumar Dabade , on 5/6/2011, in Category SharePoint
Views: 58516
Abstract: In this article, we will see how to create and deploy a SharePoint Dashboard which contains SQL Server Reporting Services (SSRS) 2008 reports. We will also see some references of how to create a Chart and Tablix report and deploy them to Report Server.

In this article, we will see how to create and deploy a SharePoint Dashboard which contains SQL Server Reporting Services (SSRS) 2008 reports. We will also see some references of how to create a Chart and Tablix report and deploy them to Report Server.

The steps to create a SharePoint Site using a Template Business Intelligence Center remains the same as shown in my previous article SharePoint Dashboard with Common Filters using PerformancePoint Services 2010. I also assume you have downloaded the Dashboard Designer tool at your end. If you have not yet done so, please check my previous article for the steps.

 

The dashboard designer tool will help you to design the Data Sources, KPIs, Scorecards, Reports, Filters and Dashboards. The tool is shown below –

Sharepoint Dashboard Designer

 

On the left hand section, you will see the Workspace Browser. In this window, you will see two settings:

 

1) Data Connections - It is used for creating a connection with different types of data sources as shown below:

b. For our demonstration, we are going to use Analysis Services.

2) PerformancePoint Content – Using PerformancePoint Content we can design KPIs, Filters, Reports, Dashboards, Indicators and Scorecards as shown below :

Now let’s create two SQL Server reports using SQL Server Business Intelligence Studio as below–

1) Create a Chart report – For creating a chart report please refer to my article titled as SQL Server: Designing Chart Report in SSRS 2008.

2) Creating Tablix Report – For creating a Tablix report please refer to my article titled as SQL Server: Designing Tablix Report in SSRS 2008.

Once you create both the reports, deploy your reports to you report server. To deploy the reports, right click the project in solution explorer and go to properties. In the property dialog box, type the URL of your report server. In my case, the report server is installed on my local instance. So I have mentioned the address as given below –

Report Server URL

Now right click again on the project and click on ‘Deploy’ menu. This will deploy the reports to the Report server. To test the deployment, type the following address (without quotes) in your browser. Please note that your address may differ than what I have here.

“http://localhost/Reports”

You will see the deployed reports as shown below –

Report Server Manager

Now let’s get back to our Dashboard designer tool. Right click ‘PerformancePoint Content’ and then add a new ‘Report’. This will show you a ‘Report Template Dialog box’. Choose ‘Reporting Services’ template as shown below –

Reporting Services Template

Now let’s rename the report with ‘Sales Chart Report’ and configure the report as shown below –

1. Server Mode – you can install reporting services in two different modes –

  • Native Mode
  • SharePoint Integration Mode

Choose the appropriate mode. In my case, I will be making a choice of ‘Report Center’.

2. Server Name – provide the same path which we provided during the deployment of report to the report server. In my case, it is ‘http://localhost/reportserver’.

3. Report – now let’s browse the report by clicking the ‘Browse’ button. This will show you all the reports which are deployed to report server in a dialog box. Choose the chart report as shown below –

Reporting Services Report

4. Now you can choose the other properties as per your requirements. You can preview the report by clicking a ‘Preview’ button. You preview should look like the one shown below –

SSRS Sales Chart Report

Now let’s add one more report which will take the address of our ‘Tablix’ report. Repeat the steps above and choose ‘Tablix’ report and preview the same. It should look similar to the following  –

Tablix Report

Note: If you have parameters in your report, then you will have to provide the default values to the parameters. If you do not pass the default values, you will not see the preview of a report. It’s a common mistake that devs do.

Rename your report with ‘Sales Tablix Report’. Save both the reports.

Now let’s create a Dashboard. Right click ‘PerformancePoint Content’ and click on New Dashboard. Choose ‘Two Columns’ dashboard template. Add both the SQL Server reports into our dashboard from the ‘Details’ window, as shown below –

Sharepoint Dashboard

Rename the dashboard with ‘SSRS Sales Dashboard’. Now right click ‘SSRS Sales Dashboard’ and click on ‘Deploy to SharePoint’. This will prompt you for the deployment option. Keep the default values.

Once the deployment is successful, you will see the dashboard in our SharePoint Site, as shown below –

Sharepoint Chart Tablix Report

Summary – In this article, we have seen how to create and deploy a Dashboard which contains SQL Server Reporting Services 2008 reports. We have also seen how to create Chart and Tablix reports and deploy them to Report Server.

Give a +1 to this article if you think it was well written. Thanks!
Recommended Articles
Pravinkumar, works as a freelance trainer and consultant on Microsoft Technologies. He is having over 10 years of experience in IT and is also a Microsoft Certified Trainer(MCT). He has conducted various corporate trainings on all versions of .NET Technologies including .NET, SharePoint Server, Microsoft SQL Server, Silverlight, ASP.NET, Microsoft PerformancePoint Server 2007 (Monitoring). He is passionate about learning new technologies from Microsoft. You can contact Pravinkumar at dabade[dot]pravinkumar[attherate]gmail[dot]com


Page copy protected against web site content infringement by Copyscape


User Feedback
Comment posted by scot on Wednesday, November 9, 2011 10:46 AM
Very good article, PravinKumar, can also post on SharePoint: Create Dashboard KPI using dashboard designer with sharepoint List. Thanks
Comment posted by Mauro Marchi on Monday, January 7, 2013 6:17 PM
Ciao Many thanks for your post.
I am working with SSAS 2008 R2 next year with ssas 2012...
I try to understand if PERFORMANCE POINT SERVICES is a good product to do real and interesting dashboards...
What is SHAREPOINT'Version do you need to use Performance Point Services ???

Do you have any other good articles to understand better this product ???

Many thanks.
Comment posted by asif rehman on Friday, January 17, 2014 8:22 AM
i have deployed reports and in designer when i select Reporting Services in "Perofrmance Point COntent" it gives error "unable to load appropriate report editor. verify that the appropriate plug in is installed"...
can u help me out?

Post your comment
Name:  
E-mail: (Will not be displayed)
Comment:
Insert Cancel