Yes. One of the challenges of developing reports is to limit the amount of reports developed and to make a report "flexible", meaning that one report can present different outputs for different end users.  One method I use is to add all of the fields selected in the dataset as columns in the report but to allow the user to hide or show the columns depending on their desired output.  In this example I will present a multi-value parameter which will present the user with all of the columns on the report. Here is the full report.

Rather than having a separate parameter for each column, as has been presented in other FAQ entries, in this occasion I simply use a multi-value parameter where I build the list of columns that are available on the report. This multi-value parameter can be seen below:

I set a default value of XX which simply means show all columns.  As you can see I use a descriptive name for the label and for the value I simply put in a string which represents each column. For the default value I could use anything as long as it doesn't match any of the columns. For the "ProductNumber" field I use that same text as the label but behind the scenes I use "PNum" as the value. For the "ProductName" field I use Name for the value. For each of the months I use an abbreviated string for the value suck as M1, M2, M3, etc.

Now, once again I am using the Hidden Property under the Visibility section to set it to True or False based on whether the user chooses it or not. The Hidden property can be seen below.

The key here is to add an expression to the Hidden property of the column (in this case TableColumn1 which is the ProductName) that tests for the existence of that particular field or column in the multi-value array that is represented by the parameter.

I am using the Join as well as the Instr functions and I am testing whether or not the text string "Name" is in the array of the values that the user has chosen. If the result of the search for the string is equal to 0 it means that it was not found. So if it was not found we want to set the Hidden property = False or inother words show the column. If it was found this then this means that the end user wants to hide the column so the Hidden property is set to True.

The parameter is presented to the end user as a multi-value parameter as seen below:

As an example of how this technique works I have decided to hide 4 fields, the Product Number and the first 3 months as I am not interested in 1st quarter results for this report.

The result when you run the report can be seen below:

This exact same technique could be used to show columns rather than hide columns. You may want to change the name of your multi-value parameter to ColumnstoShow and you may want to use this technique for allowing the user to show some additional columns that they would not necessarily want to see. Maybe the parameter would be AdditionalColumnstoShow or something along these lines. The only real difference would be in the expression the parameter name would change and the True and False would be reversed.

Last updated on July 11, 2009 by Bob