Using SQL Server Reporting Services in Client Mode

Recently I did a presentation at the March BSDA meeting. I showed how to use SQL Server Reporting Services without a SQL Server, or more specifically a SQL Server running Reporting Services. It got an enthusiastic response so I thought I’d add to it here by adding some reminder documentation, as much for myself as for all of you wonderful readers.

Using Reporting Services in Client, or Local mode is a 4 step process. First, you will need an XSD schema file to create the report on. Once you have the XSD you will be able to move to the second step, creating the report. Third you will need to place a Report Viewer control on your windows form, WPF form, or ASP.NET page. Finally you will need to write some code that generates an ADO.NET dataset, loads the report in the report viewer control, then binds it all together. Lets look at this step by step.

Normally when you create a report you connect to a database, then base it off of some object like a query, view, or stored procedure. The report is then uploaded to a Reporting Services server, which takes care of hosting it, displaying it, and generating the data for it. With client mode you have no server available, so we have to instead create a surrogate. That’s where our XSD file comes in.

Right click in Solution Explorer and “Add a new item”, and from the list of goodies select “XML Schema”. Name it something appropriate, letting the default extension be XSD. For this example I will be getting customer order data, so I’ve given it the name CustomerOrders.xsd. Visual Studio will think about it then add it to the project, and even helpfully open it for you. I don’t know about you, but hand typing XML Schema’s isn’t my idea of fun, so you should glace at it, go “that’s nice” then close it.

Now right click on the XSD file in Solution Explorer, and pick “Open with….”. In the dialog that appears, select “Dataset Editor”. When you do, Visual Studio presents a big scary warning message letting you know that you could lose contents, and that this will forever be a dataset XSD file. We have nothing in the file, so we’re cool with this, just click OK.

You will now be presented with a big surface area. In the middle it tells you to drag items from the server explorer or right click. If you have a table, view, or stored procedure you are free to drag it in, but most of the time you’ll want to base this off of a SQL query. Right click on the surface, and select Add…., Table Adapter from the menu. The first screen asks you for the database connection. This is the only time you’ll actually need a connection, in this example I am using the good old Northwind database. I pointed at Northwind and clicked next.

Next we are asked how we are going to access the data. Since we have a SQL Statement just pick the default of “Use SQL statements” and click Next.

Now take your SQL Statement and paste it in, and click Next.

OK, click Finish to wrap up the addition of the XSD. By default the adapter has a generic name, we should give ours something more meaningful. Click in the top bar, then enter a new name. Since my example report is for customer order data, I’ll name it CustomerOrders. I then went to the bottom bar and renamed the TableAdapter1 to CustomerOrderTableAdapter. You should now see something like:

Note that this will become your Data Source for the report. The data source will have the name of the XSD followed by the name of the source, in this case it will read CustomerOrders_CutomerOrders. OK, now it’s time to create the report.

Go back to Solution Explorer, right click and pick Add New Item. Navigate to the Reporting area and pick Report Wizard. Note the file extension should end in RDLC. If you have used Reporting Services before, you will know that reports typically end in RDL. However, client mode reports have just a slightly different syntax to them, thus the RDLC extension to differentiate the two. While you can modify an RDL to become an RDLC and vice versa, you have to do so by hacking the XML behind the report.

Note you can also choose just Report, but then you’ll have to setup everything manually. For this simple example though, we’ll just use the Report Wizard.

Give your report a meaningful name and click Add. The report wizard then shows you a welcoming screen if you’ve never run it before, just click Next.

Now we need to pick the data source. In this example, you want the CustomerOrders branch, so select it and click Next.

The next screen asks if we want a Tabular or Matrix report. Select the one for you, in my example I picked Tabular and clicked Next. The next screen asks how we want to display the data. For my example, I opted to group by the customers company name and contact name, then the order data went into the details area. Fill out as appropriate for your report and click Next.

The next screen asks how we want things laid out. This affects the look and feel of the report. For my example I just took the default and clicked next, however you are free to play with this to experiment with the different looks and feels your reports might have.

Likewise the next screen is also a look and feel one, asking what colorings we want to apply. Pick one that makes you happy and click next. You can always change it later, many times I pick the Generic one (which adds no colors) then fix it up afterward.

The final screen is the wrap up. Give your report a meaningful name and click Finish.

OK, you have a report, now you need a container. Open up the user interface you want to place the report viewer control on. In my example I went with a very simple Windows Forms application.

In my toolbox, I navigated to the Reporting section, where I only found one control, the MicrosoftReportViewer control. (Note I am using Visual Studio 2008 SP1, if you are on an earlier version your names may differ slightly). Grab it and drop it onto your design surface. I also added a Button control to the form to kick off the report display process.

Now it’s time for the last step, adding some code. In this example I’ve used a Windows Form. Opening it, the first thing we find in the form load area is::

(Note I left my report viewer control named reportViewer1.) Delete it, we’ll have it refresh elsewhere.

Now we need to add some using statements to the top of our class.

The first two will be used in accessing our Northwind database, you may need to use different libraries if you were going to another database. I’ve also included a referenced to the Reporting.WinForms library so we can manipulate the report programmatically.

Now let’s go to the code for the button click event. First, we need to reset the report viewer in case we’d been using it to host another report.

Next We need to set the report viewer to local mode. This tells it we’ll be supplying the report name from a local file, and binding the report to a local ADO.NET datasource.

Our third step is to create a local report variable, and set it’s reference to the report viewer’s local report. This will make it easier to work with. Then we’ll set the location of the report we want to use.

Now we need to create an ADO.Net dataset, and populate it. I implemented most of that functionality in a method called GetCustomerOrders, which I’ll append at the bottom of these instructions. It’s very straight forward code.

At this stage we have told it where our report is, and have created the dataset. Now we need to create a datasource for the report itself. We’ll use the ReportDataSource object. For the name, we’ll use the same name as the XSD schema, CustomerOrders_CustomerOrders. Then for the value we will give it the table from the dataset we created in code. It’s possible for a report to have multiple datasets, in the report we’d give each one it’s own name (based on the XSD) then here we’d bind the dataset table to the name we’d used in the report. Once done we will then add the new ReportDataSource to the local reports DataSources collection. Finally, we’ll referesh the report viewer to make it generate the report.

You can download a copy of these instructions, along with the entire sample project including code and reports, at the Microsoft Code Gallery site http://code.msdn.microsoft.com/SqlServerRSClient . As promised, below is a copy of the GetCustomerOrders routine, for your reference.