Dynamic Data Grouping Using Microsoft Reporting Services

• Download source files - 22.2 KB

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 sounds much better than 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 a single physical MS Reporting Services .rdlc file, which I am using as a template to produce different outputs. This is a perfectly applicable technique, which I am going to share with you now.

The application of the technique is not something new; we all have done similar things while dealing with reporting of data. What is new here is in this approach is I can call it a reuse of report (like we commonly reuse 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 said it right. What will you do if end-users want the same report using data grouped by CustomerID (image 1.1)? In most cases, you will end up writing a new report. In this article, I will demonstrate a way in which you can avoid this and reuse the report to produce a different output.

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

Three button technique

Now, to make life a little interesting, I added three extra buttons: Orders by Customer, Orders by City, and Orders by Country, to the user interface. These three extra buttons on the user interface do not have any built-in magic; they are just helping me to demonstrate the technique. So, without keeping you folks in much suspense any further:

Remember the game of “spot the difference” from childhood? May I ask you to play the same game with Images 1.0 and 1.1? Sure, they look different; the first image has the title “Orders List” and the second image has “Orders by Customer”, and so on…

If we pay close attention, then technically the difference is really the output format, the underlying data is the same (orders information). By this time, I am sure most of you have probably got the gist, what my technique is and how it will help you generate multiple outputs using a dynamic control for report generation.

How can one report produce four different outputs?

Imagine you are asked to develop a sales order system; one of the reporting requirements involved is to produce four different reports to calculate the freight paid for all shipped orders.

In a typical scenario, you will create four individual reports as per the specifications. Well, nothing wrong with this approach, we have done this in the past and continue to do so. However, since we do a lot of code reusing, why not try to reuse a report as a template and generate different outputs?

The quest to reuse report led me to MS Reporting Services. I am having a fun time doing this report reuse business. I though I will me share this with my friends here with the hope that it will help you the way it helps me.

The key to making your report generate more then one output is some of the design considerations. Here is what I did to generate four different outputs from this one report.

Report design considerations

Careful report design consolidation is required to create a single reporting template resulting in different outputs. We will start by making use of the Table control; first, we have to identify and lay down all the details columns that are common to all outputs. Please check the repot in the attached code for the details of formatting, etc.

Dynamic data grouping

If you will notice, apart from the detail section, the fact that makes all outputs look different is the way information is grouped together. Now you would start to wonder how I could change data grouping during run time to see different output.

Well, the solution to this problem is introducing some intelligence into our report, which the rendering engine can leverage on and produce the desired output. I have the following two parameters to pass onto the report so it can act differently.

I will make use of the parReportType parameter to pass the following four values: O-Orders, C-Customer, S-City, and T-Country. This one letter type (O, C, S, T) is provided as a dynamic value to group the data before producing the output.

Our dose of intelligence to the report designer is nothing but following the Grouping Expression, which changes the data group based on the information supplied through parReportType:

=iif(Parameters!parReportType.Value = "O","",
iif(Parameters!parReportType.Value = "C", Fields!CustomerID.Value,
iif(Parameters!parReportType.Value = "S",
Fields!ShipCity.Value,Fields!ShipCountry.Value)))

If you are not sure what iif() is, then so not worry, MS Reporting uses VB.NET syntax for coding expressions and custom code. If you have done any custom coding, for example, with Crystal Reports, then interacting with MS Reporting Services will not be a big deal.

The expression supplies instructions to the rendering engine on how to group and sort data based on our choice of report selection. It starts with checking if the choice is “O” which means simple output, not grouping. Subsequently, check for the rest of the choices and switch the behavior of the report generation.

We need to repeat the same expression in the sorting tab of grouping and the sorting properties window.

Handling of group header and 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 the visibility property of the header and footer.

Apply the following expression to group header and footer visibility property:

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

The above mentioned expression will take care of hiding the group header and footer in the case of the default report “Order List” selected.

As the group changes dynamically, we 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 the group header title takes care of dynamically changing the header based on the 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 put all sorts of intelligence into our report template; we made sure all steps are taken to achieve the desired result. However, what prompts the report to act in a certain way? How does the report know if it should generate a report based on Customer or City or it should ignore grouping altogether and produce a plain orders list?

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

Make sure to have a DataSet ready as per image 1.7.

I have written a method called loadReport and pass 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();
        }
    }
}

The code-behind the buttons 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");
}

Conclusion

I would love to participate in any discussion about the pros and cons of this approach. To me, the template based approach and reusability makes more sense than anything else. Any constructive criticism is always welcome.

I would leave you with this final thought here; if you think that I was wrong in saying “Four birds with one stone” and instead I could have said “Seven birds with one stone”, then you are 100% correct my friend. I would leave that up to you to figure it out. This is a bonus exercise for you, if you end up playing with the attached code.

And, here is a hint: imagine the end user asks you for three reports, but this time they do not care about the details. Just give me Customer…Freight Total, same for City and Country. The solution is, hide the detail!