Using a Local Reporting Services 2008 Report with an ADO.NET Data Set

SQL Server Reporting Services is an incredibly full featured reporting tool. An often asked question though is “How can I use Reporting Services without setting up a full SQL Server just to run Reporting Services?”

Fortunately the folks at Microsoft thought of this, and created a version of Reporting Services that runs in Local, or what Microsoft calls Client mode. There are several ways to use client mode, you can bind the report right to the database if you wish, or to an object. However if you have an application that’s been around for a bit, or maybe you’ve been around for a bit, chances are you have a lot of ADO.NET DataSets you’d love to use for data in a report. Today we’ll look at how to bind those data sets to a SQL Server Reporting Services report.

Let’s say you have created an application to work with the AdventureWorks2008 database. You user has now asked you for one last feature. They wish to display a list of Vendors in a report. They want to preview the report, print it, and be able to export it to a PDF format.

Based on your experience you know that SQL Server Reporting Services would be a good choice. However your client does not have an instance of SQL Server Reporting Services running in their corporation. Thus the path is clear, use SQL Server Reporting Services in Client mode.

Preliminary Work

Prior to beginning our work, we’ll need to do two basic setup steps. First, if you don’t have it already, you will need to download the Adventure Works 2008 database from CodePlex. Install it following their instructions. Here is the current location for AdventureWorks2008:

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=18407

Now open up Visual Studio 2008 and create a new C# WinForms application. Note that while the ReportViewer control we’ll be using works fine in WinForms, ASP.Net, or WPF, for simplicity we’ll use a WinForms application. Give your project a meaningful name (I used ReportingServicesLocal).

Create the DataSource

Normally Reporting Services knows what tables and columns are available because you have setup a connection to a database. In this scenario however, we are going to bind the report to an in memory ADO.NET DataTable.

At design time then Reporting Services does not know about the DataSet, and so we must create a surrogate for Reporting Services. Thus we’ll create a special type of XML schema definition to stand in for our not yet created DataSet.

To accomplish this, first we need to create the Data Source schema by following these steps:

1. Right click on the project in the Solution Explorer window.

2. Select Add, New Item.

3. Click on the Data leaf of the Visual C# Items branch in the Add New Item window.

4. Pick the DataSet item. Give it a meaningful name, such as VendorList.xsd.

clip_image002

Now we need to add a table to the DataSet.

1. In your toolbox, under DataSet find the Data Table tool and drag it onto the design surface.

2. Click the DataTable1 and rename it to Vendors.

The last step in the process is to add our columns to the Vendors DataTable we just created.

1. Right click on the name of your DataTable and pick Add, Column from the pop up menu.

2. For the first column type in VendorName. Note that if we needed to, we could now go to the properties window and change the DataType to something other than the default of System.String. For this lab, everything we’ll use is a string so this won’t be needed.

3. Repeat step 2, adding these column names: AddressLine1, AddressLine2, City, StateProvinceName, PostalCode

When done it should look like:

clip_image004

Create the Report.

Now that we have a schema, we’re ready to create the report and add the components to it. To create the report, follow these basic steps.

1. Right click on the project and select Add, New Item.

2. In the pop up window, go to the Reporting leaf under the Visual C# branch.

3. Pick “Report”, and give the report a meaningful name such as VendorList.rdlc.

clip_image006

Now that the report is created, we need to add the components and data columns to it.

1. With the blank report, drag a Table control onto the report body.

2. Open the Data Sources window by selecting Data, Show Data Sources from the Visual Studio menu.

3. You should see the VendorList DataSet, under it the Vendors DataTable, and under it the columns.

clip_image008

4. Drag the VendorName to the first column of the table. Next, drag the City to the second column, and the StateProvinceName to the third.

5. Right click on the column header for StateProvinceName and pick “Insert Column to the Right”.

6. Drag the PostalCode to this newly inserted column. Your report should now look something like:

clip_image010

Adding the Report Viewer to the Windows Form

Now that the setup tasks are complete, it’s time to get to the user interface ready. First we’ll do some basic setup of the form.

1. When you created the basic project Visual Studio created a default Windows Form, Form1.cs. Start by changing the Text property to read “Report Viewer”.

2. While we’re at it, let’s change the (Name) property to frmReportViewer.

Now add the Report Viewer control to the form.

1. In the toolbox, navigate to the Reporting area, and drag a MicrosoftReportViewer control onto the form. Resize so it takes up the lower 90% or so of the form.

2. Change the name to rvwMain (or something meaningful).

Next add a button to the form. We’ll use it to trigger the report.

1. From the Common Controls area of the toolbox, drag a button control onto the form.

2. Change the (Name) property to btnDataSet.

3. Change the Text property to DataSet.

4. Double Click on the button to open up it’s code behind.

We’ll be supplying the data to the ADO.Net dataset using SQL Server, so we need to go to the top of the form and add a reference to the System.Data.SqlClient.

using System.Data.SqlClient;

Now let’s go into the btnDataSet_Click event, and add some code to fill our dataset. This code snippet will bind to our local SQL Server, create a command to do a simple select statement to a view, and fill the dataset.

  /* Fill the Dataset ------------------------------------*/
  string qry = "select v.Name as VendorName "
                  + ", v.AddressLine1 "
                  + ", v.AddressLine2 "
                  + ", v.City "
                  + ", v.StateProvinceName "
                  + ", v.PostalCode "
               + "from Purchasing.vVendorWithAddresses v "
              + "order by v.Name ";

  string connectionstring = @"Server=(local);"
    + "Database=AdventureWorks2008;Trusted_Connection=True;";

  SqlConnection connection = new SqlConnection(connectionstring);
  SqlCommand cmd = new SqlCommand(qry, connection);

  SqlDataAdapter daVendor = new SqlDataAdapter();
  daVendor.SelectCommand = cmd;
  DataSet dsVendors = new DataSet();
  daVendor.Fill(dsVendors);

Note that in the first line of our select statement, we had to use v.Name as VendorName. The column names we return from our dataset must match the column names we entered in the Data Source back in Exercise 2 Step 3. Fortunately SQL easy to use “AS” syntax makes this simple.

Also, even though in this example we use SQL Server, the connection could be to any Data Source such as MySQL or Oracle. The important thing is we wind up with a DataSet to bind to.

In the same btnDataSet_Click method we now need to tell the report viewer control which report to run, then where to get it’s data. To tell the ReportViewer control to use a local report (as opposed to a report residing on a Reporting Services Server) we need to set the ReportEmbeddedResource property.

  rvwMain.LocalReport.ReportEmbeddedResource = "ReportingServicesLocal.VendorList.rdlc";

Note the format of the string we pass in. It has the name of the project, then a dot, then the name of the report complete with it’s rdlc extension. You should also know this is case sensitive.

Now we need to tell the report where our data really is. To do this, we’ll tell it to bind the Vendor data table from the VendorList data source to the dataset we generated in the step above.

  rvwMain.LocalReport.DataSources.Add(
    new Microsoft.Reporting.WinForms.ReportDataSource(
    "VendorList_Vendors", dsVendors.Tables[0]));

We need to create a new ReportDataSource to pass into the Add method of the reports DataSources. In the constructor for the ReportDataSource we pass in two parameters. The first is the name of the DataTable we are binding to.

Note that it’s syntax is a bit odd, you have to address it with first the DataSet name, then use an underscore to append the name of the specific DataTable.

The second parameter is the specific table from the dataset to bind to. Since we only had 1 we can use the simple .Tables[0] syntax shown here. We could have also given it a specific name.

One final note, in this simple example we are only binding one data source. However it’s possible for reports to have multiple data tables contained in them. To bind each one, we would simply have created a data table for each in the XSD, then added the code to the step above to read each one in, then bound them in this step by repeating this line of code for each one.

Finally we’re ready to display the report. Simply add this line to trigger the generation of the report.:

      rvwMain.RefreshReport();

Test your application.

Everything is now setup, you should be ready to run.

1. Launch the app from Visual Studio.

2. Once open, click on the DataSet button.

3. Your screen should look something like:

clip_image012

 

And there you go, you too can now easily create nice looking reports from your existing ADO.NET datasets.

About these ads

10 Responses to “Using a Local Reporting Services 2008 Report with an ADO.NET Data Set”

  1. Esteban Arrubla Says:

    Hi, I don’t speak in english but I’d like to know something…I tried to do this example, but I have a trouble…When I do click on the Button DataSet, I have a ErrorMessage that say me this: “Se ha producido un error durante el procesamiento local de informes.
    No se ha especificado la definición del informe ‘ReportingServicesLocal.VendorList.rdlc’ “….what can I do?….please, I’ll be waiting for an ans…Thanks…

  2. Esteban Arrubla Says:

    Se me había olvidado….muchas gracias por el ejemplo, está muy bueno…

  3. Esteban Arrubla Says:

    Ya lo solucioné…..muchas gracias ;)

  4. Manu Says:

    Buenas,

    ¿¿podrías poner la solución??

    Gracias.

  5. James Says:

    Thanks! I’ve been looking for a simple way to do reporting for ages. All the examples are to do with running reporting services, rather than this local mode.

    Now for a leisurely afternoon creating stored procedures and report templates, rather than a frustrated afternoon trying to understand the MSDN :-)

    • Ed Crane Says:

      Great starter article. I just wanted to add a few things that might be helpful to folks wanting to do more advanced reports.

      When you have multiple datasets(really just tables), you need to be able to distinguish fields from them. With tables/matrices/lists there is a property DataSetName that you can set to the name of your dataset. Be careful, the Report Designed sometimes obliterates this property when you add new datasets. For textboxes, you have to use the First function where the first argument is the Fields!.Value and the second argument is the name of the dataset, e.g., =First(Fields!City.Value, “VendorList_Vendors”).

      When you want to have dynamic control values in the header/footer regions, you can’t reference dataset fields directly. Most folks put secondary controls in the report body, tie them to the dataset fields, hide them and then tie the header/footer controls to the hidden ones in the body.

      That’s sort of sloppy and I’ve heard it doesn’t always work properly. A better way is to directly manipulate the xml in the rdl/rdlc files and then calling the the LoadReportDefinition method like so: b. viewer.LocalReport.LoadReportDefinition(new MemoryStream( System.Text.ASCIIEncoding.ASCII.GetBytes(xDoc.InnerXml))); where xDoc is an XmlDocument object with the modified rdlc xml.

      When it comes to dynamic images in the the header/footer, a good way to achieve this is to add dummy embedded images to the report and point your header/footer image controls to the embedded images. Once again, you modify the value of the embedded images in the rdlc xml prior to loading the report definition in the viewer. Here’s a method:

      public void SetHeaderImage(XmlDocument xDoc, string name, byte[] imgData, string format)
      {
      XmlElement elem = null;
      XmlNode img = SelectSingleNode(xDoc.ChildNodes[1], “//Report/EmbeddedImages/EmbeddedImage[@Name='” + name + “‘]”);
      if (img != null)
      {
      elem = (XmlElement)SelectSingleNode(img, “./MIMEType”);
      elem.InnerText = format;
      elem = (XmlElement)SelectSingleNode(img, “./ImageData”);
      elem.InnerText = Convert.ToBase64String(imgData);
      }
      }

      and here’s a helper method for selecting the right node:

      public XmlNode SelectSingleNode(XmlNode start, string path)
      {
      XmlNamespaceManager nsmgr = new XmlNamespaceManager(start.OwnerDocument.NameTable);
      nsmgr.AddNamespace(“rptDef”, “http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition”);
      if (path.StartsWith(“//”))
      path = “/” + path.Substring(1).Replace(“/”, “/rptDef:”);
      else if (path.StartsWith(“.//”))
      path = “./” + path.Substring(2).Replace(“/”, “/rptDef:”);
      else
      path = path.Replace(“/”, “/rptDef:”);

      XmlNode node = start.SelectSingleNode(path, nsmgr);
      return node;
      }

      Now you can have truly dynamic headers/footers.

      For optional report sections, many folks use subreports, but I’ve heard that can be slow. Instead, I put all of my report sections inside Rectangle controls in the report body and manipulate the xml once again. Depending on what sections the user wants to see, I cut out the unwanted sections and adjust the elements based on the cumulative elements. Be careful: the report designer usually removes the element for the top-most control (when it’s located at position 0,0) and the element for the bottom-most control.

      I hope this helps out some folks who want to do some cool stuff with client-side RS reporting.

      Cheers!

      • Ed Crane Says:

        Whoa, the reply mechanism doesn’t seem to like angle brackets. It butchered the following:

        “Depending on what sections the user wants to see, I cut out the unwanted sections and adjust the elements based on the cumulative elements. Be careful: the report designer usually removes the element for the top-most control (when it’s located at position 0,0) and the element for the bottom-most control.”

        It should have been (with angle brackets around Top and Height):

        “Depending on what sections the user wants to see, I cut out the unwanted sections and adjust the Top elements based on the cumulative Height elements. Be careful: the report designer usually removes the Top element for the top-most control (when it’s located at position 0,0) and the Height element for the bottom-most control.”

        Also, set the BorderStyle to None for the rectangle controls (report sections).

  6. Kritul Tulsidas Rathod Says:

    Hi There is just a small improvement here …
    I followed the steps mentioned in the article and was able to get desired result. However, I learned that once the report has been loaded with the data it doesn’t change the value.

    My requirement was to add a text box along with the button to refresh the report. when user put some integer value in the text box and hit the button the data should be refreshed by using that integer value as filter (where clause in the query (variable: qry here).

    Since the rvwMain.LocalReport.DataSources is a collection the report implicitly takes the first added source as the source to refresh the report in subsequent button clicks.

    To resolve this issue I just cleared the DataSources collection of the LocalReport to get the desired result.
    Here is how to do it.

    rvwMain.LocalReport.DataSources.Clear();
    rvwMain.LocalReport.DataSources.Add(…);

    Just thought share it with everyone hoping that it might help the readers.

    Thanks,
    Kritul

  7. Agustin Says:

    Muchas gracias por el ejemplo, ¡me fue de gran ayuda!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 106 other followers

%d bloggers like this: