Thomas Williams Tech Blog

Step 1 is to set up the parameter. In the "Parameters" dialog, add a new parameter called "ColumnsToDisplay", type some values and labels in, and mark the parameter "Multi-value". Make sure your parameter does not accept Nulls or Blanks:

Step 2 is to create a table with columns that can be hidden or shown:

The query behind the dataset is shown below:

SELECT 'Sales' AS Department, 'Established 1/Jan/2006' AS Column1, 'Located in Building A' AS Column2, 'Managed by Fred' AS Column3, '10 Staff' AS Column4
UNION
SELECT 'Payroll', 'Established 4/Dec/2005', 'Located in Building S', 'Managed by Paul', '3 Staff (2 Part-Timers)'
UNION
SELECT 'IT', 'Established 9/Jun/2008', 'Located in Building S', 'Managed by Julie', '4 Staff'
UNION
SELECT 'Executive Office', 'Established 1/Sep/2006', 'Located in Building G', 'Managed by Colin', '2 Staff'

Step 3 is to add a function called "IsColumnSelected" that accepts the multi-value parameter and a column name and returns a boolean value:

''' <summary>
''' Return whether the passed column name has been selected
''' in the multi-value parameter, whether it should be visible
''' or not.
''' </summary>
Public Shared Function IsColumnSelected(ByRef objMultiValueParam() As Object, ByVal strColumnName As String) As Boolean

    ' return whether the passed column name is in the multi-value
    ' parameter array
    Return 0 < InStr(vbNullChar & Join(objMultiValueParam, vbNullChar) & _
        vbNullChar, vbNullChar & strColumnName & vbNullChar)

End Function

This function does the job of looking through the multi-value parameter (an object array) and returning whether the passed column name is in the array, without a loop. It's based on old VB tip at http://www.devx.com/vb2themax/Tip/18364 which uses the Visual Basic "Join" function to combine the array to a string, and then check if the passed column name is in the string. Simple, and it works for small amounts of data (like this sample).

Step 4 hooks all the pieces together using an expression that needs to be put in each column's "Visibility" property which calls "IsColumnSelected". Put the expression below in the "Visibility" property of the first column:

=Not Code.IsColumnSelected(Parameters!ColumnsToDisplay.Value, "Column1")

This expression needs to be put in each column that needs to be hidden or shown, passing the correct column name as the second parameter.