Free - Dynamic Data Grouping using MS Reporting Services

Image: 1.0

Introduction

We hear this all the time, “Two birds with one stone.”  What if I say, “Four birds with one stone”?  I am sure four sound much better then two.  So, what are my four birds and one stone?

My four birds are four distinct different outputs generated using source NorthWind->Orders (SQL Server 2000) and my stone is single physical Ms Reporting Services .rdlc file, which I am using as template to produce different outputs.  This particular figure of speech is perfectly applicable to the technique, which I am going to share with you.

The application of this technique is not something new; we all have done same or similar while dealing with reporting of data.  What is new here is the approach, which I can call to reuse of report (as we commonly reuse the code).

Let us discuss a practical scenario here. If I ask you, what kind of output you see in (image 1.0); you would probably say a simple report listing orders information.  Well, you guessed it right.  What will you do if the end-users want same report using data grouped by CustomerID(image 1.1)?  In most cases, you might end up writing a new report.  In this article, I will demonstrate how to reuse the report to produce the demanded output without the need of writing a new report.

I assume the reader of this article is comfortable using Visual Studio 2005, C#, Sql Server 2000 and Windows Forms.  Basic understanding of how report designer works is helpful to work with attached code.

Handling of group header & footer

We are dealing with three different groups in this report and one output has no grouping required.  We have to do the following to generate proper group names and handle visibility property of header & footer.

Apply following expression to group header & footer visibility property:

=IIF(Parameters!parReportType.Value = "O", True, False)

The above-mentioned expression will take care of hiding the group header & footer in case of default report "Order List" selected.

As group changes dynamically, we do have to change the output to reflect the current scenario.  If the user selects "Order by Customer" then we have to make sure to change the group header to "Customer: xyz" and so forth.

The following expression entered as group header title takes care of dynamically changing the header based on provided grouping criteria:

=iif(Parameters!parReportType.Value = "O","",
iif(Parameters!parReportType.Value = "C",
"Customer: " & FIRST(Fields!CustomerID.Value),
 iif(Parameters!parReportType.Value = "S",
"City: " & FIRST(Fields!ShipCity.Value),
"Country: " & FIRST(Fields!ShipCountry.Value))))

Coding time

So far so good, we have created an intelligent report template; we made sure all steps taken to achieve the desired result. However, what prompts the report to act in certain way?  How does the report know, it should generate a report based on by Customer or City or if it should ignore grouping altogether and produce a plain orders list?

Now, we have done the design part of report.  Now we have to provide a mechanism to collect data from SQL Server and bind it to reporting engine.  Out of many different ways data can be bound to reporting engine, my favorite is using DataSet.

Image: 1.7

Make sure to have DataSet ready as per image 1.7.

I have written a method called loadReport and passing a single parameter to it as reportType.  I am calling this method from all four buttons, every time passing a different argument.

Following is the code for the method:

private void loadReport(String reportType)
{
    //declare connection string
    string cnString = @"Data Source=(local);
Initial Catalog=northwind;" +
"User Id=northwind;Password=northwind";
    //use following if you use standard security
    //string cnString = @"Data Source=(local);Initial
     Catalog=northwind; Integrated Security=SSPI";
    //declare Connection, command and other related objects
    SqlConnection conReport = new SqlConnection(cnString);
    SqlCommand cmdReport = new SqlCommand();
    SqlDataReader drReport;
    DataSet dsReport = new dsOrders();
    try
    {
        //open connection
        conReport.Open();
        //prepare connection object to get the data through reader and
    populate into dataset
        cmdReport.CommandType = CommandType.Text;
        cmdReport.Connection = conReport;
        cmdReport.CommandText = "Select * FROM Orders
 Order By OrderID";
        //read data from command object
        drReport = cmdReport.ExecuteReader();
        //new cool thing with ADO.NET... load data directly from reader
 to dataset
        dsReport.Tables[0].Load(drReport);
        //close reader and connection
        drReport.Close();
        conReport.Close();
        //provide local report information to viewer
        reportViewer.LocalReport.ReportEmbeddedResource =
         "DataGrouping.rptOrders.rdlc";
        //prepare report data source
        ReportDataSource rds = new ReportDataSource();
        rds.Name = "dsOrders_dtOrders";
        rds.Value = dsReport.Tables[0];
        reportViewer.LocalReport.DataSources.Add(rds);
        //add report parameters
        ReportParameter[] Param = new ReportParameter[2];
        //set dynamic properties based on report selection
        //O-order, C-Customer, S-City, T-Country
        switch (reportType)
        {
            case "O":
                Param[0] = new ReportParameter("parReportTitle",
 "Orders List");
                Param[1] = new ReportParameter("parReportType", "O");
                break;
            case "C":
                Param[0] = new ReportParameter("parReportTitle",
                            "Orders by Customer");
                Param[1] = new ReportParameter("parReportType", "C");
                break;
            case "S":
                Param[0] = new ReportParameter("parReportTitle",
                            "Orders by City");
                Param[1] = new ReportParameter("parReportType", "S");
                break;
            case "T":
                Param[0] = new ReportParameter("parReportTitle",
                            "Orders by Country");
                Param[1] = new ReportParameter("parReportType", "T");
                break;
        }
        reportViewer.LocalReport.SetParameters(Param);
        //load report viewer
        reportViewer.RefreshReport();
    }
    catch (Exception ex)
    {
        //display generic error message back to user
        MessageBox.Show(ex.Message);
    }
    finally
    {
        //check if connection is still open then attempt to close it
        if (conReport.State == ConnectionState.Open)
        {
            conReport.Close();
        }
    }
}

Code behind each button is as follows:

private void btnOrders_Click(object sender, EventArgs e)
{
    //orders list
    loadReport("O");
}
private void btnByCustomer_Click(object sender, EventArgs e)
{
    //orders by customer
    loadReport("C");
}
private void btnByCity_Click(object sender, EventArgs e)
{
    //orders by city
    loadReport("S");
}
private void btnByCountry_Click(object sender, EventArgs e)
{
    //orders by country
    loadReport("T");
}