Skip to Content

WEBI – Dynamic Line Chart driven by Input Control

Many times when there are multiple Metrics shown on Line chart, it becomes cluttered and difficult to see individual metric details clearly. Following steps will help in creating a Line Chart for multiple metrics in Web Intelligence that would show the Metric details dynamically based on the selection made by user through Input Control.

To explain this concept, We are going to create a Line chart showing State wise Revenue and Quantity. An Input Control will allow to choose either Revenue or Quantity line to show on chart.

Steps –

1. In order to create an Input Control, create a Derived Table in Universe with following query


2. Create a Self Join to this Derived Table and Include it in the Same Context as your Fact Table.

2-Self Join.jpg

4-Context Addition.jpg

3. Create Objects from this Derived Table. Save the Universe and Export.


4. Create a query on this Universe. Select the following objects as shown. Refresh the Query.


5. Create a Variable with name “Measure” with Following definition on the Report.

If [Metric]=”Revenue” then Sum([Sales revenue]) Elseif [Metric]=”Quantity” then Sum([Quantity sold])

6. Put the Line Chart on the Report and Assign the Data as shown below

8-Assign Data.jpg

7. Create an Input Control on Metric with dependency as shown below

9-Input Control Dependency.jpg

8. After this, you can select the Type of Metric (Revenue/Quantity) from Input Control and accordingly Line chart will show those details. When All is selected, it shows both Quantity and Revenue separately. See the Charts below for each selection.

11-Graph Quantity.jpg

12-Graph Revenue.jpg

10-Graph All Values.jpg

This way any number of Metrics can be shown on Line Charts without making it look cluttered.

You must be Logged on to comment or reply to a post.
  • The Derived table is independent. It is not joined with any other table. It only has a self join to put in the same context as fact table to avoid multiple queries in WEBI. Since it is not connected to any other table, it creates a Cartesian product. Please see the query below.









      Select distinct Metric from


    Select 'Quantity' as Metric from Article_lookup


    Select 'Revenue' as Metric from Article_lookup


      )  Metric_Type,



      ( Outlet_Lookup.Shop_id=Shop_facts.Shop_id  )

      AND  ( Metric_Type.Metric=Metric_Type.Metric  )




  • Nice post, I like your idea.

    Do you know a way to eliminate the "All Values" option in the Input Control? I would never want a user to see Quantity and Revenue added together.

    • Thanks James.

      "All Values" is the option automatically added by WEBI. So I do not think we can remove it by any trick. It may require SDK customization.

    • Hi Hari,

      If you notice, the main crux of this functionality is Derived table that creates Cartesian product. Since with Bex, derived tables can not be created, this will not be possible with Bex.



      • Hi Abhijit,

        can you please review my steps and advise..Because i have implemented the similar functionality by using Bex as source.


        Naveen D

  • Hi Hari,

    Yes..this is possible with Bex as source as well.Please see below steps to implement.

    Step1:Duplicate the chart.

    Step2:Create a Dummy variable with Name "Revenue" and type "Revenue" in the formula the variable

    Step3:Create a input control on the Revenue Variable and select single value option.

    Step4:Expand "List Values" option.Type "Quality" and click on right button.

    Step5:Apply Input control on the duplicate chart

    step6:Create another variable with below sample syntax

    =If(Report filter([Revenue])="Revenue";Revneue Measure Object;Report filter([Revenue])="Quality";Quality Measure Object;" ")

    Step7:Please use above variable in Chart Key figure column.

    Step8:Now apply the report level filter on original chart by using the above variable.If variable=" " then display the original chart.

    step9:Apply the report level filter on duplicate chart by using the above variable.If variable inlist ("Revenue","Quality") then display the duplicate chart.

  • Hi hari,it was a nice post and helpful.

    i got a requirement,in my report i have three bar charts,if i click on first chart the data should reflect on seperate table same for other two charts.can you please guide me how to acheive it.  Thanks in advance.