Tutorial: Designing Your First Report in SQL Server Reporting Services

Trying to get past the Microsoft SQL Server Reporting Services learning curve? Well never fret, this article is a first in a series of tutorials to walk you through creating a practical report for your company to monitor your SQL Server’s databases. In this sample report, we’re going to create a job monitoring system. Specifically, we’re going to connect to our SQL Server and report if the jobs succeeded or failed the last time they ran.

Easy enough right? This article assumes you already have Reporting Services installed on the servers and the client installed on your side. If you haven’t installed these components yet, please go out to this link and download and install the client and server. http://www.microsoft.com/sql/reporting/productinfo/trial.asp#download. For your development PC, you should only have to install the client component unless it is also acting as your server. Also for the purpose of this report, we’re going to steal a query written by another SQLServerCentral.com member and can be downloaded in clear text here: http://www.sqlservercentral.com/scripts/contributions/916.asp.

With the installation complete and the housekeeping complete, let’s open up Visual Studio and begin designing our report. Visual Studio 2003 will act as my design interface.

1. Open Visual Studio and go to File | New | Project
   
2. Select Business Intelligence Projects under the Project Types box. If you do not see this project type, then the Reporting Services client is not installed on your machine. Once you’ve selected Business Intelligence Projects, click Report Project Wizard. Then, type the name of the project. For the purpose of this example, I typed SQLMonitor as my project data. Click OK once you have those steps completed.
   
3. In the Select Data Source screen, type a logical name for the connection you’re going to make in the Name field under New Data Source. I named mine msdb. You may also see the Shared Data Source drop-down box populated if you have created one in the past. Next, click Edit to set the connection properties. I’m connecting to a server called REPORTS and connecting to the msdb database. Alter enter your login credentials and whether you’d like to connect with Windows or SQL authentication. You can also specify that you’d like the user to be prompted for login credentials when viewing the report under the Credentials  area. I also checked Make this a Shared Data Source. This means other reports that we create later will be able to use this same connection information. It’s important to note here that this connection may or may not be your reporting services server. Instead, this is the SQL Server who’s jobs you’d like to monitor. After entering my data, the Connection String looked like this:
   data source=REPORTS;initial catalog=msdb
   
   
4. The next screen allows you to enter the query manually that you’d like to run against the data source. My query was taken from the earlier mentioned script by one of our users but I have modified it so we’re prepared for another part in this article series. So, please use this script in the Query String text box instead of the one in the link:
   
   select distinct j.Name as "Job Name",j.enabled, j.category_id, j.description as "Job Description", h.run_date as LastStatusDate, case h.run_status 
   when 0 then 'Failed'
   when 1 then 'Successful'
   when 3 then 'Cancelled'
   when 4 then 'In Progress'
   end as JobStatus
   from sysJobHistory h, sysJobs j
   where j.job_id = h.job_id and h.run_date =
   (select max(hi.run_date) from sysJobHistory hi where h.job_id = hi.job_id)
   order by j.Name
   
   
   
5. Notice that you can also click the Edit button to write the query in a GUI design fashion. Fancy and familiar interface for those like me who hate writing queries!
   
6. By clicking the Next button, you will be taken to the Report Type screen. For most reports including this one, you’ll want to do a Tabular report and click Next.
   
7. You must then design the table by highlighting the SQL Server column that you’d like in the page, group or details and select the corresponding button as shown in the below screenshot. The Page selection will create a new page any time that the column’s row changes. The Group selection will group the data by that column. The Details selection is the column that we’re concerned with and we want to use all the columns except for the CategoryID column as shown below.