Display Input Control Values on Report

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:

=Substr(QuerySummary();Pos(QuerySummary();”Filters”);Length(QuerySummary()))

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.

About these ads

14 Responses to Display Input Control Values on Report

  1. Jansi says:

    Michael, I have a question. I know we can also have this Store Name inside the Block1 too. But having it separate has some advantages. Can you please list them out for us here?

    • Very good question, Jansi. The primary advantage, in my mind, is saving space. In my experience, it is often very critical to save as much space on a report as possible. Often times, reports include as much information as possible in a small space. This solution would save quite a bit of space. A column in the first block would have duplicate values, and would, therefore, require much more space than a separate block.

      Also, I have often been asked to have a section on the report for Metadata. This solution allows for that, as well. The second block could be in a section that displays information about the report.

  2. Jansi says:

    Thank you! Good job!

  3. Hi Michael, I have tried this and this serves the purpose of check boxes and single/multiple values. I have 2 situations for which i was not able to find a solution.

    1. Date Range on the same object. I tried the Min and Max function on the coulumn but the selected dates may lie outside the data set (returned from the query) and hence cannot be displayed in the table.

    2. In situations where there is huge list in a combo box and we choose ‘All Values’ – The object in a table with the input control on it will replicate that list, the ideal way would be to display ‘all Values’.

    Let me know if you have an idea to work around these two.

    Appreciate the work on this blog, thanks!
    Regards,
    D

    • Dhaval, Thanks for the comments. In your first example, if the data range is determined by a set of prompts, you can use the UserResponse function to display the dates. Otherwise, if the dates are hard coded into the query, they can also be hard coded on the report.
      For your second question, I haven’t tried this, but you could probably use If, Then, Else logic to say “All Values” if the count of selected values is equal to the count of query values.

  4. Dave Rathbun says:

    … or you can use the ReportFilter() function. ;) For your example, you would use the following formula:

    =ReportFilter([Store name])

    • Good point, Dave. It all depends on how you want it formatted.

      • Dave Rathbun says:

        I mention it because you said in your post that there was no function to return the values selected in an input control, and to the best of my knowledge that’s what the ReportFilter() function does. If you want to show the values in rows (rather than combined into a single cell) then you can use the Replace() function trick I showed here:

        http://www.dagira.com/2009/11/25/displaying-userresponse-values-on-separate-rows/

      • I should have been more clear. The person that posted the question on BOB, if I recall, said that they tried the ReportFilter() function, but it didn’t give them what they wanted. I’ve used it with inconsistent results with Input Controls. So, although it works in this case, and even better with your trick, I don;t always trust it. :-)

    • Thanks Dave, The ReportFilter([Store name]) will return a full list, and is cumbersome to look at.

      Michael, The If-Then-Else worked in combination with the ReportFilter() suggested by Dave. :)

      Looks like this-
      =If(Count([Store])=NoFilter(Count([Store]));”All Values”;ReportFilter([Store]))

      As for the Date range, it is an interesting requirement, may not be met.
      There is no query filter on dates. From all the data that comes in, the webi users select a start and end date and then examine how many days have data for it. So we cannot rely on report data to display filter selection. 

      Let me know if this went from being a blog post to a BOBJ forum query. Don’t want that to happen to the blog. 

  5. bobj says:

    Michael, good job and it will be usefull… thanks

  6. Goiffon says:

    As usual very interesting info. Just from a pure license perspective since someone asked me this this feature is not include in the standard BI pacakge or whatever basic license. The ability to use predefined input controls is included in the BI viewer BI4 license model (Interactive viewing in XIR2 and XI3). The ability to define input controls is included in the BI4 Analyst license.

  7. Anir says:

    Very good post, but now how to pass these values to child report prompts via hyperlink.

    • Good question, Anir. I don’t think there’s a way to do that, since we don’t currently have a function to return the values selected in an Input Control.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,194 other followers

%d bloggers like this: