Dynamic Measures in Webi (Using a Derived Table)

Dynamic Measures in Webi (Using a Derived Table)

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 prompt the user to show a particular measure, and design only one report. Here’s how…

I’m Using

• BOBJ XI3.1 SP3 FP3.5
• Universe Designer
• InfoView/Webi
• MS SQL Server 2008 R2
• Microsoft AdventureWorks database.

I’m Assuming

• You are familiar with Universe Designer
• You know your way around Webi
• Are generally familiar with SQL databases and syntax

Since this trick is mostly SQL, it should apply to any version of Webi. This post unfortunately doesn’t apply to OLAP (MSAS or BEx) data sources. An OLAP version of this post is on its way.

First, since you need to prompt your users with a list of measures, you will need to create this list in the universe, you do this by creating a derived table.

1. In Universe Designer, in a blank area of the screen, right click and select Derived Tables…
   
2. Enter a name for the derived table and the following SQL code:
   SELECT 'Revenue ($)' as Select_a_Measure
   UNION
   SELECT 'Order Quantity' as Select_a_Measure
   UNION
   SELECT 'Gross Margin ($)' as Select_a_Measure
   UNION
   SELECT 'Gross Margin (%)' as Select_a_Measure
   UNION
   SELECT 'Standard Cost ($)' as Select_a_Measure
   UNION
   SELECT 'List Price ($)' as Select_a_Measure
   UNION
   SELECT 'Unit Price Discount (%)' as Select_a_Measure
   
   Note that if you’re using an Oracle data source, you may need to use the dummy ‘DUAL’ table, since Oracle requires a FROM clause for each SELECT clause. The end result (for a Microsoft SQL database) should look something like this:
   
3. Click OK.
4. Right click on your new derived table and select Table Values…
   
   
   You should see something like this:
   
5. Click Close.
6. Drag the Select_a_Measure column to the object tree to the left of the screen to create a new dimension.
   
7. Click Insert > Object… from the top menu.
8. Name the object Selected Measure.
9. Enter the following in the Select box:
CASE MeasureList.Select_a_Measure
  WHEN 'Revenue ($)' THEN Sales.SalesOrderDetail.LineTotal
  WHEN 'Order Quantity' THEN Sales.SalesOrderDetail.OrderQty
  WHEN 'Gross Margin ($)' THEN @Select(Measures\Gross Margin ($))
  WHEN 'Gross Margin (%)' THEN @Select(Measures\Gross Margin (%))
  WHEN 'Standard Cost ($)' THEN Production.Product.StandardCost
  WHEN 'List Price ($)' THEN Production.Product.ListPrice
  WHEN 'Unit Price Discount (%)' THEN Sales.SalesOrderDetail.UnitPriceDiscount
ELSE 0
END

10. Click OK.You must be careful when using these objects; because they are not joined to any other table, they will multiply the number of results your query returns (you’ll get a Cartesian product).
11. Create a new Webi Intelligence report, or open an existing one.
12. Edit the query, and drag across the two new objects. Your query should now have some normal dimensions and measures, and the two new objects, like so:
    
13. Add a filter on the Select A Measure object and select only one of the measures, e.g. Revenue ($).
14. Run the query. Note that since only one item was selected for the Select a Measure filter, the query will return the same number of rows as it normally would (in this example, a modest four rows).
    
15. Note that the values for the Selected Measure object are the same as the Revenue ($) object (although the formatting is different, we’ll sort that out soon). Now change the query and select a different item in the filter. I’ll use Order Quantity.
    
    Which means I’ll now see order quantity in the values for Selected Measure
    
16. If you’re not clear on what’s actually happening here, try changing your filter to select multiple items:
    
    Which will essentially give me multiple result sets. I’ll now have three rows for each of my four rows of data.
    
    See how in the first row, our magic measure is showing Gross Margin ($), in the second row it’s showing Order Quantity, and in the third it shows Revenue ($). Remember this, because even though the end result looks like you’re changing the measure that is represented by the Selected Measure object, you’re not. You’re simply filtering rows, and this object has different values in different rows. Understanding this difference makes life easier when working with these sorts of reports.
    
    Now you have a choice to make. If it is likely that your users will want to quickly switch between measures, then you can run your query to include all of them. The query will be many times the size, but you can then add a simple filter to allow the users to change which measure is shown. Alternatively, you can use a query prompt to allow your users to change which measure to show in the report. This results in smaller queries, but more clicks and seconds for the end user, and more action on the database side since users are refreshing. For now we’ll go for a compromise and include three measures in the query, and use a quick filter to select which to display.
17. Create your report as you would to show a single measure, then add the Select A Measure object as a quick filter. An example report might look something like this:
    
    Changing the drop-down at the top of the screen to a different measure will change the values shown:
    
    
    The formula for the header looks like this:
    
    =" Sales Statistics Report - " + [Select A Measure]
18. If you don’t select a measure, you will get #MULTIVALUE errors. If you want to avoid this, then I would suggest using a mandatory, single select query prompt.

The one thing left to do is get some formatting in place. We’ll do this with alerters.

1. Click the alerters button in the toolbar and add a new alerter.
2. For the first alerter, in the Filtered Object or Cell field, click the ellipsis and click Select an Object or Variable, then click the Select A Measure object.
3. Set the operator to In List and then select the values Revenue ($) and Gross Margin ($).
4. Click the Format… button and then click the button next to Number Format: near the top of the window, and select the currency format you would like.
5. Add another sub-alerter and do the same, this time defining the format you would like for your other measures. The end result will look something like this:
   
6. Click OK and apply the alerter to the cells in the table. Note that now when you change the selected measure, the formatting in the table will change. With order quantity selected:
   
   …and with revenue selection:
   
   
   I don’t think there’s a way to apply the same formatting to the Y-axis values in the chart, but would be delighted if corrected in the comments below.

And that’s it!

- See more at: http://blog.davidg.com.au/2011/07/dynamic-measures-in-webi-using-derived.html#sthash.TgT0Vtoi.dpuf