InfoSol We're passionate about inspired business intelligence
Related Articles
Many business users require similar report content with minor formatting differences. Rather than create individual, customized reports for each business user the report writer can leverage input controls and conditional formatting to put the power of customization back into the hands of the business users.
Below is a sample of the end result. By choosing fields in the drop-down input controls, the user chooses what to show or hide in each column of the report.
To get started, add the commonly requested objects to a new table. For this example, I am using eight objects – six dimensions (shown below in blue) and two measures (shown below in orange).
Next, create variables for each column in the report and set each one equal to null (“”). The values for each variable will be defined by the input controls that will be created in the next step.
Create a new Input Control and select the C01 Input Control variable that was just created.
Choose a Simple Selection Combo box, change the Name to reflect that this is the selector for column one in the report, then choose the ellipsis to create a custom list of values.
Type the names of all of the report objects into the values list. In addition, add a “None” placeholder to allow for blank columns. Hint: all fields can be entered at once by separating each value with a semi-colon. This is useful because it can be reused when creating more than one of the same input control.
Uncheck option to allow selection of all values, then enter a default value (in this example the objects will be entered in the order that they appear in the list of values for each column, which is alphabetically by dimensions then measures). Click finish to save new input control. Repeat for each column in the report.
Next we will create a custom variable to populate each column. The syntax is as follows:
=If [C01 Input Control]=”Customer City” then [Customer City]
elseif [C01 Input Control]=”Customer State” then [Customer State]
elseif [C01 Input Control]=”Employee City” then [Employee City]
elseif [C01 Input Control]=”Employee State” then [Employee State]
elseif [C01 Input Control]=”Product Group” then [Product Group]
elseif [C01 Input Control]=”Product Name” then [Product Name]
elseif [C01 Input Control]=”Number of Products” then [Number of Products]
elseif [C01 Input Control]=”Revenue” then [Revenue]
else “”
Create same variable for each column, adjusting input control variable in formula accordingly. Hint: you can duplicate the variable then edit rather than retype.
Change each column in the report to point to the newly created column value variables.
To fix the column names, create similar variables for each column with the name of the object instead of the object itself:
=If [C01 Input Control]=”Customer City” then “Customer City”
elseif [C01 Input Control]=”Customer State” then “Customer State”
elseif [C01 Input Control]=”Employee City” then “Employee City”
elseif [C01 Input Control]=”Employee State” then “Employee State”
elseif [C01 Input Control]=”Product Group” then “Product Group”
elseif [C01 Input Control]=”Product Name” then “Product Name”
elseif [C01 Input Control]=”Number of Products” then “Number of Products”
elseif [C01 Input Control]=”Revenue” then “Revenue”
else “”
Add each newly create column name variable to the corresponding column.
The final step is to create conditional formatting rules to “hide” the column if “None” is selected. Note: “None” is intended to be used from right to left, selecting in a middle column will result in a blank column in the report.
Click on the first column and create a new conditional rule. Choose the C01 Input Control variable as the filtered object and set operator Equal to None.
Next click on the formatting button. Set the background to no color and no pattern.
Finally, set the border to none by unchecking the default box and clicking the no borders button. Click OK to save and apply rule.
Apply the same rule to the corresponding column header.
Repeat for all columns. You now have a customizable report with input controls to select content for each column.
