Use SQL Server Reporting Services

Posted Monday, May 1, 2006 9:38 AM by C-Dog's .NET Tip of the Day

Chances are there is something out there that you need to create reports on. Whether it is transactions, reservations, members, change orders, or bandwidth utilization, SQL Server Reporting Services is an easy way to build nice looking reports.

Let me start by clearing up any miconceptions about it that might be viewed as hurdles.

  1. You do not need a server running SQL Server 2005 to develop Reporting Services.
  2. You can query against any normal data source (i.e.: SQL Server 2000), so again you don't need to be runing SQL Server 2005.

SQL Server Reporting Services is developed using the Business Intelligence Development Studio (it's the Visual Studio 2005 IDE). All you need to do is install the client tools for SQL Server 2005 on your local machine. Basically all this does is install the project templates needed to create a Reporting Services project.

Creating a new report is easy with the report wizard. It will first ask you for a SQL Server connection, then it will ask you for your query. You can use any valid ad-hoc or stored procedure you want. Then it will ask you for a tabular layout or matrix layout. Unless you know how to do real data mining type stuff, you will probably typically go with the matrix layout for most simple reports. Last, you just select the columns you want and a layout and you will have the start of a basic report.

Typically with most reports, you will have the need for date parameters. You will also usually want to specify default dates for these. There are a couple of things you have to do to make this happen. First, add the parameters, by clicking on the layout tab, right clicking anywhere and selecting report parameters. Here you can add parameters in various types, but typically the most common is DateTime. Add the parameters you need and I'll explain how to set default dates down below.

After you add your parameters, click back on the data tab, so we can add them to the query. The syntax for using a parameter is Parameters!ParameterName.Value for example Parameters!StartDateTime.Value. Unfortunately, you have to change the query syntax slightly to make this happen. You have to convert the string to an excel expression.

For example if you had the following query:

SELECT Field1, Field2 FROM MyTable WHERE
TransactionDate > '4/1/2006'

It would have to be changed to the following to use a new parameter called StartDateTime:

="SELECT Field1, Field2 FROM MyTable WHERE " &
"TransactionDate > '" & Parameters!StartDateTime.Value & "'"

Note, once you make this change, it won't let you run the SQL Query any more with by clicking the ! button. You always have to click on the Preview tab at this point to view the report.

Now that you have a parameterized query built, you might want to set a default value. You can do this by either hard coding it (huh huh), executing another query, or using an Excel expression. Typically, I just use excel expressions. Here are a couple common ones that you might find useful. For dates, remember that you have to cast whatever you build back to a Date object.

  • =Now (Obviously now)
  • =CDate("1/1/2006") (Start of the year)
  • =DateAdd(DateInterval.Day, -7, Today) (7 days ago)
  • =CDate(Month(Now).ToString() & "/1/" & Year(Now).ToString()) (First Day of the Current Month)
  • =IIf(Weekday(Today) = 4 or Weekday(Today) = 6, DateAdd(DateInterval.Day, -2, Today), DateAdd(DateInterval.Day, -3, Today)) (3 days ago from Mondays, 2 days ago from Wednesdays and Fridays)

As you can see you can get as elaborate as you want with the expressions. Once you are done with development, you will want to deploy the reports to an instance of SQL Server 2005 with Reporting Services installed. Reporting Services can be installed to the same server running SQL Server. However, typically you will probably want it installed somwehere else (although that does require another SQL Server license). Again you do not have to have a full instance of SQL Server 2005 installed anywhere to install Reporting Services.

Once you have a Reporting Server to use, it is easy to deploy your reports from Visual Studio. Once deployed, you will be able to take advantage of execution snapshots and subscriptions. For example, you can create a subscription to have the report executed every other Wednesday and E-mail a PDF, Excel document, XML file, or whatever to someone.

I'll talk more in the future about what you can do on the server side of things. I hope this is enough to get you started though with this powerful and easy to use tool.

Read the complete post at http://www.dotnettipoftheday.com/blog.aspx?id=269