Developing Your First SSRS Report for SQL Server 2005

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. 

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. 
   
   
8. Once you click Next, you’ll be taken to the Table Style screen in the wizard, which may look vary familiar to many of you. Later, I’ll show you how you can modify the templates that are presented here to reflect your corporate style. For the time being though, let’s just select the Casual template.
9. Next, choose you’re deployment server. This is the SQL Server Reporting Services server that you’re going to send the reports to after they’re complete. This can be altered later once you’re ready to deploy to a different environment or server. The default is http://localhost/ReportServer so all you’ll have to do is change the localhost entry to your server name. Mine is http://REPORTS/ReportServer. You can also specify here what folder you’d like Visual Studio to create once you deploy your reports. A report helps you subdivide the tons of reports you may have and secure them at different levels. My folder name is SQLMonitor.
10. After clicking next, the last screen is the confirmation screen, where you type the report name. Spaces are allowed if you’d like. I called mine Job Monitor and clicked Finish.

Congratulations, you’ve finished your first report. Well, almost. Let’s explore some of the features now that you have your report essentially complete. If you haven’t already done so, click on the Preview tab above to view the report. The report is a pretty useful report for administrators but it’s very unattractive. As you can see the columns need widening and the Enabled column is pretty useless in its Boolean form for a novice user.

First let’s widen the first two columns, deploy the solution and then call it a day. To do this, go back to the Layout tab in Visual Studio. To adjust column width, select the column’s header you’d like to widen by left-clicking. You’ll then notice an extra row with no data will be presented to you above the column. Left-click and hold between the two columns and drag them to the appropriate size, much like you do in Excel or SQL Server. An alternate way is to select the column then change the column width in the Properties pane on the right of the design interface. Click the preview again and see your progress. It may take a few hundred times before you have configured it to the perfect width for your viewers. You may also want to adjust the report header width as well to make it look nicer. Here’s a sample of what my report looks like in Preview mode. I’ve shrunken it down a bit so it would fit in HTML cleanly.

Now that your report is ready for primetime, you can deploy it to the development server for others to view it. To do this, click Deploy Solution or Deploy SQLMonitor (if you only want to deploy the individual report project) under the Build menu in Visual Studio.

With your report deployed, users can now go to Internet Explorer (if they have permission to) and go to your deployment directory to view the report. There are other methods to view the report as well. Primarily, users could view it through your application or through a web service. Here’s how mine looks:

You probably don’t want users seeing the msdb data source in the default view. To remove that, you can click on the msdb data shared data source and check Hide in List View.

In future articles in this tutorial series, we’ll explore bite-sized chucks of Reporting Services. From administrating the server to more complex design components. Please save the report you’ve just created though! We’ll use it in each part of this series as we progressively become more complex.