Display Input Control Values on Report
September 20, 2011 16 Comments
A common requirement in the reporting world is to display filtered values on a report. This could include Query Filters (including prompted values), Report Filters (including Drill Filters), or Block Filters. Of course, displaying Query Filters on the report is fairly simple. If the Query has one or more prompts, you would use the UserResponse() function to display the selected value(s) on the report. Otherwise, you could use a formula like this:
Or, of course, you could simply type the values into a cell. The same goes for Block Filters. Since they aren’t dynamic, you can simply type them into a cell.
However, there is no function to display the values selected in an Input Control. This question was recently posted on BOB: “How do I display the values selected in an Input Control on the report?” The solution was fairly simple, but sometimes the simple solutions can be the hardest to find.
To demonstrate this, I’ll use the eFashion universe. I started by creating the following query:
After running the query, I removed the Store name column from the block. This column will be used for the Input Control.
Next, I created an Input Control as follows:
- Object: Store name
- Type: Check boxes (Multiple)
- Number of lines: 14
For all other settings, use the defaults. Now, as you select one or more Stores from the Input Control, the values in the block adjust accordingly. Of course, we can’t see on the report which value(s) are selected. So here’s how we accomplish this:
From the Templates tab of the Report Manager, expand Tables, and drag a Vertical table on to the report. This gives you a blank table, with one column. From the Data tab of the Report Manager, drag the Store name object into the center of one of the cells in the table. At this point, the table should be showing all Stores.
Next, click the Edit button on the Input Control.
In the Edit Input Control box, go to the Dependencies tab. You see Block 1 is selected. Select Block 2 as well.
Click OK. Now, try it out. As you select values in the Input Control, Block 1 continues to show the filtered data, while Block 2 shows the selected Stores.
There you go. Now all you have to do is format as needed, and you’re done. Let me know what you think.