Chain of command

This article was originally published in VSJ, which is now part of Developer Fusion.
One of the better elements of SQL Server 2005 is its Reporting Services. Early versions of SQL Server left you to your own devices; if you wanted a report, you bought software such as Crystal Reports and created the reports locally, or you hard coded them in a suitable programming language.

Reporting Services offers the means to both create and manage reports from within SQL Server 2005. It can be accessed from within Visual Studio 2005 using various controls, and the controls can be freely distributed, so giving you an excellent way to provide reports in your VS2005 applications.

What you may not know is that those of you working with SQL Express, the free version of SQL Server, can also make use of Reporting Services. This wasn’t the case with the initial release of SQL Server Express, but the updated version released earlier this year includes Reporting Services. If you’re using an older release, it’s definitely worth downloading the most recent version. Note that this is a different version to the one listed on the downloads page at the time of writing.

However, there are some differences between the SQL Server 2005 Express edition and that of ‘real’ SQL Server, most of which seem designed specifically to make life harder for the developer. For example, SQL Server 2005 Express comes with a cut-down version of the SQL Server Management Studio called Management Studio Express. One of the missing elements from this is the ability to administer your report server. You have to use the Reporting Services Configuration tool instead, and the Report Manager to manage individual reports.

One thing you’ll discover is that when you install Express, you don’t get the sample database that you can use to learn about writing reports. Most of the sample code you’ll find on the Microsoft site expects you to have the AdventureWorks sample database installed and attached. You’ll need to download it from the Microsoft web site, and then attach it to your SQL Server Report Server. You can find the sample by doing a search for AdventureWorks. The match you’re looking for is titled SQL Server 2005 Samples and Sample Databases (July 2006).

If you’re trying to follow samples of how to create and use reports, you should also know that the connection strings in Express are different to those in normal SQL Server, as are the Reporting Services URLs. One reason for this is that SQL Server Express always installs as a named instance, and your URLs and connection strings have to include the instance name.

If you’re setting up a connection string to AdventureWorks on your local SQL Server Express instance, it should look like this:

Data Source=localhost\SQLExpress;
	Initial Catalog=AdventureWorks
Similarly, a URL to your report server endpoint would be:
http://localhost/
	reportserver$SQLExpress
The endpoint in SQL Server Express Reporting Services is the place that the servers monitor for requests to execute reports.

Figure 1
The first step is to create a report…

Figure 2
…then use the ReportViewer control to add the report to your application

Getting started

So what can you actually do in SQL Server Express Reporting Services? The main use is to provide the means to let users view reports.

When a user requests a report in your application, SQL Server Express runs the query behind the report, the data is merged back into your report layout, and the report is then rendered. You can choose between HTML, Acrobat and Excel formats. You are limited to using only SQL Server format databases – full SQL Server Reporting Services let you work with Analysis Services databases, Oracle, XML, OLE DB and ODBC format data.

Another restriction is that the databases must be running locally in the SQL Server Express Database Engine – you can’t retrieve remote data sources.

Other bits that are missing and only available in full SQL Server are the more sophisticated reporting features such as scheduled report processing, report snapshots, report caching, and the ability to allow users to subscribe to reports so they receive updates.

One missing element that might be a problem is the fact that you can’t set up custom role assignments. You have to map existing Windows domain user and group accounts to predefined role definitions. The role definitions you get are Browser, which lets the user view folders, reports, and subscribe to reports; Content Manager, which lets the user manage all aspects of content, including creating folders, reports, and data sources; My Reports, for users who want to publish reports, create folders, and manage resources in their My Reports folder; Publisher for publishing reports; and finally, Report Builder, which you can use to view report definitions.

However, the overall message is that so long as you don’t want to do anything too exotic, and you want to work with local SQL databases, the reporting facilities are really very good – especially when you consider that they are free.

Creating Reports

Before you can use a report in your Visual Studio application, you need to create it, and this involves knowing about yet another bit of SQL Server Express that isn’t installed by default. You need to use the SQL Server Express Toolkit, which includes the cut-down version of the Business Intelligence Development Studio that is used with the SQL Server Express edition. The SQL Server Express Toolkit is installed separately from other SQL Server Express components. To find it, you’ll need to search for SQL Express Toolkit – it gives only a single match. Once you’ve managed to find and install the toolkit, you can create reports that are as snazzy as the ones created in the other editions of Reporting Services, so you can set up reports with sub-reports, drill-through reports, use parameters, and make use of charts. If you have reports from older versions of SQL Server, you can re-use them in the Toolkit.

When you’ve created your report definition, you then need to publish it to your report server. Reports are only made available to users who have been assigned to particular role assignments. A SQL Server role assignment is a security policy that specifies whether a user or group can access a specific item and perform an operation. To set up a role assignment, you link a user or group account name with one or more role definitions. These themselves are linked to specific items or branches in the report server folder hierarchy. If you’re using SQL Server Express, you’ll need to use the Report Manager to set up role definitions. You’ll find the choices for role assignment in the Site Settings option.

Once the reports are working in Report Manager, you can then go on to add them to your database applications in Visual Studio 2005. To do this, you’ll make use of the ReportViewer controls in VS 2005. There are two controls: one for Windows Forms applications and one for ASP.NET applications, so you can create Web applications.

If you’ve tried an earlier release of Reporting Services, in particular the one included with SQL Server 2000, you may have decided it was just too much like hard work to create a Web-based application. There were too many elements that you had to handle yourself such as dealing with images, and tying together Reporting Services sessions with report requests. In addition, you couldn’t have interactive reports. In short, it was fiddly and limited, and most people resorted to using the HTTP handler, which accepts requests in the form of an HTTP GET or POST request specifying the URL of the report. The difficulty with this method is that you are forced to make the details of the report available in some way. If you embed the URL link in your application (which has the benefit of simplicity), you weaken your security, particularly if you include the report parameters.

The Report Viewer in Visual Studio 2005, combined with the version of Reporting Services that comes with SQL Server 2005, overcomes all of these problems by providing an ASP.NET HttpHandler that takes care of images, report sessions, and interactive report use.

The way ReportViewer works is to be the presentation layer to the Report Server. When a user asks to view a report, the control submits the request to the server side of your application. The Web client never accesses the server directly. Once the Report Server has generated the report, the ReportViewer control handles the sending of the report data to the browser, where the report is rendered.

The report viewer controls that you get as part of Visual Studio 2005 can be distributed, and Microsoft has provided a ReportViewer Redistributable 2005 package. The viewers do need to have .NET Framework 2.0 in order to work, so you also need to make sure that’s installed on the client machines.

You have two ways to work with the ReportViewer controls: using the smart tags or properties window, or via code. If you’re using code, it’s worth noting that you can work either in local or remote modes, and the programming interfaces are very similar, so it’s easier to develop locally then swap from the LocalReport object (reportViewer.LocalObject) to the remote ServerReport object (reportViewer.ServerObject).

The reports you work with in the viewer will usually have been designed using SQL Server’s Report Designer or the SQL Server Express Toolkit, and deployed to SQL Server’s report catalogue. This central repository can be used to manage and deploy all SQL Server Reporting Services reports. By using the remote mode, you can make use of various other features of Report Server, such as the session management; My Reports collection for particular users; report scheduling; and its built in security. The viewer passes user requests to zoom, print, search and so on to the Report Server. One effect of this is that any errors that occur while the report is being processed are displayed in the ReportViewer pane. If you want to avoid this happening, you’ll need to add some code to trap the ReportError event and deal with it more sympathetically.

If the report you want to embed doesn’t need any parameters, the code needed to get it into your application is remarkably short and simple:

reportViewer.ProcessingMode =
	Microsoft.Reporting.WinForms.
	ProcessingMode.Remote;
reportViewer.ServerReport.
	ReportServerUrl = new Uri(Settings.
	Default.ReportServerEndPoint);
reportViewer.ServerReport.ReportPath =
	“/VSJ/ShowCity”;
reportViewer.RefreshReport();
This code sets the ReportViewer control’s processing mode to remote because we’re working with a report on the server. It then sets the URL property to the Report Server endpoint. It then specifies the name and path of the report, and finally uses RefreshReport to display the report.

Sadly, in real life few reports are this straightforward. You will almost certainly want to pass parameters back to your reports to let your users decide what data they’re interested in. The ReportViewer control has some tools for handling report parameters. If you set the ShowParameterPrompts property to True, the user will see parameter placeholders in the toolbar, and can enter the data they’re looking for. The options aren’t bad; for example, you get a calendar control if the parameter needs a date or time. If you want to provide more hand-holding, you can hide the parameters area, and use normal form controls to get the data. One option here that may well impress your users is the support Report Server offers for multiple value parameters. These let you enter several (or at least more than one) values for selecting data – a city parameter could return all records for Newcastle and Leeds, for example.

There’s a GetParameters method that lets you find out what the parameters for the report should be, and you can pass the parameters back using code such as:

Dim pInfo As
	ReportParameterInfoCollection
Dim params As New Generic.List(
	Of ReportParameter)
params.Add(New ReportParameter(
	“CustID”, “1024”))
params.Add(New ReportParameter(
	“City”, “Leeds”))
ReportViewer.ServerReport.
	SetParameters(params)
If you want to use multiple value parameters, things are a bit more complicated because the parameters expect (and return) an array, not a scalar. For this reason, you’ll need code such as:
Paramname=”City”
Paramvalues(0)=”Leeds”
Paramvalues(1)=”Newcastle”
ReportParams(3).value=paramvalues
There are many other ways you can customise the report viewer control; what’s less clear is how many are worth pursuing. As users of the Express version, some options such as clickthrough reports are beyond your reach, but it’s amazing how much you can achieve with very little coding and a database that didn’t cost you anything. If you want some examples of the type of reports you can create, have a look at GotReportViewer. This is mainly aimed at people who have the full version of SQL Server 2005, but a lot of the examples will work with SQL Server Express, and it gives a good idea of the type of thing you’ll be able to do.


Kay Ewbank, editor of Server Management magazine, is a highly experienced database analyst and developer who has followed the development of database technology from dbase through to today’s SQL servers.

You might also like...

Comments

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Nine people can't make a baby in a month.” - Fred Brooks