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.

Conclusion

I would love to participate in any discussion about pros and cons of this approach.  To me, template based approach and reusability makes more sense then 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 I should have said; "Seven birds with one stone" then you are 100% correct my friend.  I would leave it that up to you to figure it out. This is bonus exercise for you, if you end up playing with attached code.

Ok, here is the hint: Imagine if the end user asks you I need another three reports, but this time I do not care about details.  Just give me Customer…Freight Total, same for City and Country.  Then the solution is; HIDE THE DETAIL!