Contributed by John White, BOCP & BOEP. When John isn't creating world-class Crystal Reports or teaching, he can be found at the local ice rink, either scoring goals for his hockey team or refereeing high school games.

Creating a Report with Dynamic Columns

One of the ways that report developers can make things easier for themselves is to reduce report proliferation – in other words, what is the fewest number of reports needed to meet the users’ requirement?  Keeping it to a minimum results in less time spent modifying and maintaining existing reports, and more consistency in terms of presentation and accuracy of data. 

Typically this is done using parameters and record selection.  Rather than having daily, weekly, and monthly sales reports, one Sales Report with a date parameter would fill all three needs.  However, record selection is not the only way we can leverage parameters to make our reports more flexible.  Consider a standard employee list:

The HR department might want to have a similar report that only lists employees and their phone numbers, and we can use a parameter to dynamically hide the fields that are not wanted.  Usually, though, this results in ugly white space:

We can get around this problem by making each column’s data dynamic.  Now the report is completely flexible in terms of which columns are displayed and in what order, while eliminating unwanted gaps between columns. 

The first thing we need is a string parameter field for each possible column – for this example we will need six.  Add values for each column name, set “Allow Custom Values” to false, and set the default value to “None”. 

To save time, you can manually add the values to the first parameter, export them as a text file, and then import that text file list into the other parameters.  You'll find these options under the "Action" dropdown.

Next, we need six formulas to correspond to each of the parameter fields.  We can use a case statement to associate each value in the parameter field to its corresponding database field.  Note that since the parameters are all string values, you may have to do some type conversion in the formula to change numbers or dates into text. 

There are now six parameter fields, and six formulas:


We now replace the fields in the detail section with the formulas, and replace the column headers with the parameter fields:

Once the report is refreshed, the user is prompted for the fields to be displayed, and the order in which to display them:

If all the fields are selected in the same order as the original report, the two will look identical:

However, if we select the fields in a different order, and leave the value of “None” in some of the parameters, we get a completely different report:



The last step is to suppress the parameter fields in the page header if the value of “None” is selected.  We can do that by clicking the X2 button next to the Suppress checkbox and entering a Boolean formula for each parameter field:

Now the column headers only display if the associated column is also displayed:

Running the report again with different parameters looks just as good:

You may have to play with the width and spacing of the fields to get a consistent look, but this is a powerful way to make one report do the work of many!

Disclaimers

To view the example Crystal Report featured in this article, you will need to have Crystal Reports XI or later installed.  If you don't own a copy of Crystal Reports, but you would like to preview this report, please visit the SAP Business Objects Crystal Reports Viewer page.

To download the sample Crystal Report and related database, click this download link.  Save the ZIP file and extract both the sample Crystal Report and Database files to the same folder.

While in theory the sample code shown will work with Crystal Reports release 7 or later, it has only been tested with Crystal Reports XI and later versions.

Tell us what you thought about this article at cnenews@cne.com