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.
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
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.