September 19, 2011

Dynamic Measures in Webi (on Any Data Source)

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).

I’m Using

  • BOBJ BI4.0 SP2 (but the steps apply in XI3.1 SP3+)
  • BI Launch Pad/Webi

I’m Assuming

  • You know your way around Webi

 

Let's get started.

 

  1. 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.
  2. 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.
    image
  3. Add a variable called Select A Measure, of type Dimension, with the value of “Revenue ($)”.
     image
  4. Click OK
  5. On the left side of the screen, click the Input Controls icon.
    image
  6. Add a new Input Control, and base it on the dimension called Select a Measure.
    image
  7. Make it a single-select input control, either radio buttons or a drop-down will do.
  8. In the List Of Values section click the ellipsis to the right.
    image
  9. In the box in the top left, type Revenue ($) (or the name of any of your measures).
  10. Click the > button to add it to the list.
    image
  11. 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.
    image
  12. When finished, click OK.
  13. Click Finish. You should now have an input control that looks something like this.
    image
  14. 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.
    image

    =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]
    Else 0

    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.
  15. Click OK to save the variable.
  16. Now add this measure variable to your report block and test. With Revenue ($) selected, it should return revenue.
    image
  17. With Margin (%) selected it will show margin.
    image
  18. 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.
  19. Click New Rule…
    image
  20. 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.
  21. Set the Operator to Equal.
  22. In the Operands box, type Order Quantity.
  23. Click the Format… button.
  24. In the Display section set the formatting under Format Number…
  25. In the Text section, set the font colour to black.
  26. 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):
    image
  27. Click OK.
  28. 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:
    image
    With Revenue ($) Selected

    image
    With Order Quantity Selected

And that’s it!