Skip to Content

As we know, Web Intelligence is a very flexible reporting tool with advanced analytic functionalities. In this post, I’ll demonstrate how to implement a dynamic measures selection solution with Webi. Before we start, let me explain what is “dynamic measures selection”: for example, we have measures including sales quantity, sales revenue, discount amount, target sales revenue, etc. Users want to select which measures to be displayed on the report dynamically. The business background of this requirement is that for each measure, there’re actually multiple measures like this year value, last year value, variance, growth rate, etc. So displaying all measures at once may result in a too large report. So users want to just focus on some of the measures they are interested in.

Given the powerful interactive viewer of Webi, users can drag in measures they need and format the report layout, of course. But what we need is an “interactive report” solution targeted at casual users with little IT skills, rather than power user/data analyst. So we need to create a formatted report with only very simple interactive interfaces: click the check box to select measures you want. Below is the sample report, users select measures on the left panel, and measures get displayed/hidden on the right side report area:

Figure 1: Sample report 

I use following techniques to accomplishthis solution:

1. Webi cross table.

2. Webi Input Control (this isavailable since XI 3.1 SP2).

3. Universe derived table.

 

Follow below steps to create above samplereport:

1.      Build simple Universe based on eFasion connection. Add a derived table named “measure_names” with below SQL statement:

select distinct ‘Sales Revenue’ as mname from outlet_lookup

union

select distinct ‘Sales Quantity’ from outlet_lookup

union

select distinct ‘Margin’ from outlet_lookup

2.      Create a dimension object based on the only field of above derived table. The Universe structure is shown inbelow figure:

Figure 2: Universe structure 

3.      Create a Webi query based on this Universe, with objects: State, Measure Name, Margin, Sales Revenue and Sales Quantity. Note that in the Universe, the derived table is not joined with any other table, a Cartesian product will be resulted by this query, and this is what we want.

4.      After the query run, we get the default report as below: 

Figure 3: Initial query results 

5.      Create a report variable named “Measure Value” with below definition:

=If([Measure Name]=”Margin”) Then [Margin] ElseIf([Measure Name]=”Sales Quantity”) Then [Sales Quantity] Else [Sales Revenue]

6.      Create a Cross Table, put State object on the row, Measure Name object on the column, and Measure Value variable on cell.

7.      Create an Input Control based on the Measure Name object. Choose check boxes as the control type.

 

Well done! Now you can select measures to be displayed on the report dynamically, with intuitive interface. For production implementation, the table measure_names may be replaced by a physical table for better performance. 

To report this post you need to login first.

16 Comments

You must be Logged on to comment or reply to a post.

      1. Bob Zenker
        You are creating a derived table that produces a cartesian product.  So a standard analyst and end user now needs to know that the supporting objects should not be “used” in any other functionality.  BI is about producing objects that support business and the analysis to achieve a better business practice.  What your suggesting provides a method to support people who have not been trained, simply train them.  The original objects developed: margin, sales qty and sale revenue are good object, but this object called Measure Value is not worth anything, what “value” does it have? 

        …Just because you can build something doesn’t mean you should.

        (0) 
        1. Ajay Das
          “Simply train them”.

          Try explaining – why a good hand on any toolset is needed – to the user; those who are at decision making levels. Especially if they are not heavy users of the system and, have been similarly told by IT to ‘learn’ similar tools every couple years (or months).

          A ‘hack’ does have its use in real world. Whether we (should) like it is another discussion.

          (0) 
        2. Chris Cai Post author
          If we can make it more user-friendly, why not?

          BTW, this technique doesn’t prevent the universe to be used in the ordinary way, just exclude the “Measure Name” object form query.

          -Chris

          (0) 
          1. David Lai
            I agree that this is a smart hack and if the business “REALLY” demands this feature then okay do it, but you will need to warn them that this will add complexity to maintenance in the future.

            Someone new coming in will have to figure out why/how you created the new derived table, figure out why there is a cartesian product, and figure out the new variable that was created. Instead of training a user to on how to just drag and remove the columns 🙂

            Either way, it shows the Input Control being used and definitely I love this new feature 😉

            (0) 
  1. Atul Chowdhury
    At a minimum, this blog, and Chris, both do a good job at helping illustrate the benefits of using newer XI3 SP2+ capabilities like input controls and directing end-users to create more relevant and useful reporting scenarios.

    The simple fact of the matter is that not *everyone* requires:

    – a query panel
    – filter bars
    – drill-by/drill-through menus
    – right-click calculation menus

    >> Culture Eats Strategy for Breakfast

    BI has paid dearly for not heeding this famous adage in that companies have tried time and again to hand over obtuse analysis machines to, say, a field “sales guy”, hoping that somehow they’d figure out all the knobs, dials and gauges in time for their pipeline meeting with their DSM.  Of course, like many BI stories with sad endings, this too ends in failure and “sales guy” ends up going back to his trusty MS Excel 2000 spreadsheet and good ‘ol auto-filter with a pivot or two that his buddy, the “*tech savvy* sales guy” while waiting at US Air Admiral’s Club to board a flight to a client location.

    I’m with Bob, in that I don’t think we teach or train end-users well enough.  And the Universe *is* meant to deliver an optimal mechanism for ad-hoc analysis.   But rather than “Analyze->Plan->Build->Deploy->Train->Support->Refine”  we see all to many of”Build -> Disappear -> Watch software erode into shelfware”.  It’s probably beyond the scope of this entry to get into it more, but I think it’s a great topic for discussion.  As an architect, consultant and user – and someone who absolutely enjoys working with SAP technology – it definitely troubles me to see the folks who need analysis tools the most use them the least.  We’re obviously not doing a good enough job somewhere along this chain.

    We need to be more creative.  This entry helps us with this.

    (0) 
    1. Ray Li
      Atul understands this post well with an in-depth comment to it. Aside from the lengthy theoretic debate, I see creative methods in solving the customer’s polytropical, irrational requirements.
      (0) 
  2. Jeremy Zagorski
    How are you hiding/displaying the columns? I do not have access to universes so I created a variable similar to “Measure Name” and then an input control but the columns remain there and are blank while the checked controls have values in their repective columns.
    (0) 
  3. RUC ..

    I tried implementing the same on the column chart. Let’s take 2 measures : Gross Revenue and Net Revenue.

    If click Gross Revenue then Column Chart shows Dimension with Gross revenue and if click Net Revenue then column chart shows dimensions with Net Revenue.

    I am using Radio Button in the input control.

    My Question is What if I click on the ALL ??

    My assumption or user assumption is to show both gross revenue and net revenue on the column chart with dimension values with different color bars but seems not working and I cannot remove ALL from the input control.

    (0) 
    1. RUC ..

      I am in 4.0. I think we a need a work around process.

      What if I click on the ALL ??

      My assumption or user assumption is to show both gross revenue and net revenue on the column chart with dimension values with different color bars but seems not working and I cannot remove ALL from the input control.

      (0) 

Leave a Reply