Crystal Reports with Dynamic Number of Columns with Typed Dataset

Introduction

While we were developing a trading application, I was asked for a Crystal report, which has a dynamic number of columns. There are factories having a number of distribution centers from 1 to 6. I needed to develop a report which would have dynamic columns so that we could display quantity in dynamic columns and also remaining quantity in the last column.

Background

There is very little information available on the web for dynamic columns for Crystal reports. So I decided to write an article so that users could easily develop Crystal reports with number of columns.

In this article, I just show how we can create dynamic Crystal reports, what we use in this article are typed dataset, Crystal report and a SQL server stored procedure that returns dynamic number of columns which we show in our Crystal report.

So first, we create a typed dataset like this:

Here, we create some columns with name col1, col2, col3, which help us in creating dynamic report generation. Our stored procedure returns a dynamic number of columns with header col1, col2 and we bind that to this typed dataset. If there are any number of columns for any factory stored procedure, it returns columns as it is in typed dataset.

We have one more stored procedure that only returns Names of dynamic columns. We use this stored procedure to set parameters in report used as headers of columns.

We have some formatting in these headers, if no value comes in parameter, we don't format it and nothing is displayed, and if we get any value we change the background color of that parameter field.

if {?col1} <>'' then Color (192,192,192)

And in the detail section, we can just place the fields of our typed dataset.

When we run this report, it looks like:

History

• 4^th August, 2010: First revision of this article