Building a SQL Server Report Using Report Builder 2.0

This is the fourth post in the series Surfacing Company Data through Sharepoint 2007 and SQL Server 2008 Reporting Services. In the previous post titled Part 3 – Configuring SSRS on the SharePoint Web Servers, we walked through the installation and configuration of the Reporting Services Add-in for SharePoint technologies. In this article we will get into the fun parts of the solution – building SQL Server reports and viewing them in SharePoint 2007.

Let’s say you have some data in SQL Server 2008 that you wanted to report on.

Here is my data:

We want to get a real time count of the number of customers in a hosted service plan. We need a simple TSQL query that will give us a SUM of customers and group by HostedService. We will build and test this query using Microsoft SQL Server Management Studio. The simple query that would yield the result set I am looking for is:

SELECT  COUNT(*) AS 'Number of Customers', HostedService
FROM    CustomersList
GROUP BY HostedService

The result set:

Note: This is obviously a simplified example. In the real world, you’ll have complex joins, date aggregations, and averages.

Now that we have our query we’ll go ahead and fire up Report Builder 2.0 on our local workstation. If you still haven’t downloaded it yet, it can be downloaded here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=9f783224-9871-4eea-b1d5-f3140a253db6&displaylang=en

Below is a screen shot of Report Builder 2.0 after loading

Now we’ll use the chart wizard along with our TSQL query to create a bar graph displaying the amount of customers in each hosted service.

Step 1: Use the chart wizard to build a bar graph

1. Click the Insert tab

   

2. Click the dropdown under Chart and select Chart Wizard
3. We’ll now need to add a database connection to our SQL Server 2008 database
4. Click New on the Choose a connection to a data source screen
5. Enter in the server name and credentials you want to connect with
   • I use a SQL account that just has db_reader rights to my LOB database
   • Use the Test Connection to make sure your credentials and server name are correct
6. Make sure your new data source is highlighted and click Next
   
7. Click Edit As Text – This should look very similar to the Query Designer tool in SQL Management Studio
8. Copy and paste your TSQL query into the top part of the window from Management Studio
   
9. Click the “!” button to run the query. You should get back the same results as you saw in Management Studio.
10. Click Next
11. Now choose your chart type. As you can see from the screen shot below, there are many different options.

    

12. Choose Bar
13. Click Next
14. Now we’ll associate our result set columns to Bar Graph columns and values
15. Drag “Number_of_Customers” into the Values section
16. Drag “HostedService” into the Categories section

    

17. Click Next
18. Choose your color scheme
19. Click Finish

Step 2: Test the report

1. Click the Home tab
2. Click the Run button – You should see “Report being generated” and then the bar graph with a legend

   

Step 3: Clean up the chart

1. Click the Design button to go back to the editing view
2. Left click on the chart to select it
3. Drag the bottom right corner so that the chart expands down and to the left
4. Click on each axis title and vertical axis title and type in “Hosted Service”
5. Click on the horizontal axis title and type in “Number of Customers”
6. Click on Chart Title and type in “Number of Customers per Hosted Service”
7. Click the Run button to test out your modifications
8. You should get a better looking result like the one below – The look and feel of the chart is totally customizable. The increments are auto generated based on your result set numbers.

   

9. Click Save and save the report (.rdl file) to your file system – Note you can also save it directly to a SharePoint document library

Step 4: Ensure that SharePoint is ready to surface the report. We have already setup everything on the Central Administration side, but we need to make sure to create a document library to store our reports.

1. Open Internet Explorer and navigate to your SharePoint site.
2. Click Site Actions > Create > Document Library
3. Name it Reports
4. We also need to make sure and activate the Report Server Integration Feature at the site collection level. Click Site Actions > Site Settings > Site Collection Features
5. Find “Report Server Integration Feature” and Activate it – Note This adds some web parts and ECB menu items for special interactions with .rdl files

Step 5: Upload and view your report in SharePoint

1. Now that we have configured a new home for our reports, go ahead and upload your newly created report (.rdl) file to the Reports document library
2. Hover over the title of your report and click the drop down
3. Click on Manage Data Sources
4. You should see the name of the data source that you created in Report Builder – Click on the data source name
5. Click on Stored Credentials and enter the SQL user which has db_reader rights
   • This is a simple way of authenticating from Sharepoint to Report Server to the SQL Backend
   • I chose this method because I do not have Kerberos setup
   • You can also create a Shared Data Source instead of specifying the credentials for each report
6. Click OK
7. Once you are back in the Reports document library, click on your report and witness your new bar graph displaying in SharePoint!
   • Note that you can click Actions and export your report into all kinds of formats: PDF, Word, Excel, Print, CSV
   • When you export to PDF, you can print the report without the IE toolbar

Now you can see the power and flexibility that the SSRS and SharePoint 2007 integration offers. The next versions of SQL Server (SQL Server 2008 R2), Report Builder (3.0), and SharePoint will have even more exciting features! With the new spatial data capabilities of SQL Server 2008 R2, we can now build mapping applications with data in SQL. Another important point, reporting on list item data in SharePoint is not trivial. SQL Server 2008 R2 Reporting Services has a built in SharePoint data source which allows you to directly query against SharePoint lists. You can do this now by using SSIS packages to pull data from SharePoint into SQL on a schedule, but take my word for it, that is a bit ugly.

Well I hope you’ve enjoyed the series! And I hope you’ve learned a few things about SSRS and SharePoint along the way. We covered a lot of material over the course of this series, so if you have any questions or other resources you’d like to share, please don’t hesitate to post them in the comments. Thank you for following!