Create Dashboard with SQL Server Reporting Services (SSRS) 2008

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 articleSharePoint 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 –

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 –

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 –

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 –

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 –

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 –

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  –

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 –

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 –

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.