Imagine that you have database that contains a number of different measures, and the reporting requirements call for many identical reports where the only difference is the measure. For example, the one report layout may be required to show either Revenue, Gross Margin, Net Margin, Volume, Average Sale, etc.
Rather than duplicate the reports that are shown, you can let the user decide which measure to show, and design only one report. Previously I’d written a post showing how to do this using a derived table in the universe. This post shows you how to get the same results without a universe (for example if your source is BEx or MSAS or you simply don’t have access to edit your universe).
- BOBJ BI4.0 SP2 (but the steps apply in XI3.1 SP3+)
- BI Launch Pad/Webi
- You know your way around Webi
Let's get started.
- Create a new Webi Intelligence report, or open an existing one. For the instructions to make sense, you’ll need at least two measures and one dimension.
- On your report canvas, create a new block that contains a dimension and all of the measures that you want your users to be able to chose between.
- Add a variable called Select A Measure, of type Dimension, with the value of “Revenue ($)”.
- Click OK
- On the left side of the screen, click the Input Controls icon.
- Add a new Input Control, and base it on the dimension called Select a Measure.
- Make it a single-select input control, either radio buttons or a drop-down will do.
- In the List Of Values section click the ellipsis to the right.
- In the box in the top left, type Revenue ($) (or the name of any of your measures).
- Click the > button to add it to the list.
- Repeat for any other measures that you want your users to select from. Since there's no way to reorder them once added, add them in the order that you want them to appear to your users.
- When finished, click OK.
- Click Finish. You should now have an input control that looks something like this.
- Now you need to add a measure variable that will return the appropriate measure, based on what the user has selected from the input control. So, add a variable measure called Selected Measure, and enter in the formula below.
=If ReportFilter([Select a Measure]) = "Revenue ($)" Then [Revenue ($)]
ElseIf ReportFilter([Select a Measure]) = "Margin ($)" Then [Margin ($)]
ElseIf ReportFilter([Select a Measure]) = "Margin (%)" Then [Margin (%)]
ElseIf ReportFilter([Select a Measure]) = "Order Quantity" Then [Order Quantity]
Note that the names that you present to your user don't need to be the same as the actual measures, but it makes sense to do so. Also, it's bad practice to use if statements like this in a measure variable, but since I know that my dimension can only ever have one value, I'm going to allow it.
- Click OK to save the variable.
- Now add this measure variable to your report block and test. With Revenue ($) selected, it should return revenue.
- With Margin (%) selected it will show margin.
- But the format of the selected measure won't always be right. For this we will use Formatting Rules (formally known as Alerters). So, click on the Analysis tab, then the Conditional tab.
- Click New Rule…
- The logic you will use is this:
if the dimension "Select a Measure" is "Order Quantity" then format as a whole number, if the dimension is "Margin (%)" then format as a percentage, otherwise format as currency.
So, in the the Filtered object or cell box, click the ellipsis, select the option Select an object or variable, then select Select a Measure from the list. Lots of selecting.
- Set the Operator to Equal.
- In the Operands box, type Order Quantity.
- Click the Format… button.
- In the Display section set the formatting under Format Number…
- In the Text section, set the font colour to black.
- Add another condition (in 3.1 a 'Sub-alerter') and repeat similar steps for Margin (%).
The end result will look like this (pity you can't see the number format in the preview):
- Click OK.
- Now when creating your report, simply use Selected Measure in place of a normal measure, and let your users select what they would like to see. A finished report might look something like this:
With Revenue ($) Selected
With Order Quantity Selected
And that’s it!