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

1-Derived_Table.jpg

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.

3-Object.jpg

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

5-Query.jpg

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.

To report this post you need to login first.

16 Comments

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

        1. RUC ..

          Excellent !!!

          Is the Derived table independent or Joined to any other table ??

          How will the webi Sql look like ??

          (0) 
  1. Abhijit Ingale Post author

    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

      Outlet_Lookup.State,

      Metric_Type.Metric,

      sum(Shop_facts.Amount_sold),

      sum(Shop_facts.Quantity_sold)

    FROM

      Outlet_Lookup,

      (

      Select distinct Metric from

    (

    Select ‘Quantity’ as Metric from Article_lookup

    Union

    Select ‘Revenue’ as Metric from Article_lookup

    )

      )  Metric_Type,

      Shop_facts

    WHERE

      ( Outlet_Lookup.Shop_id=Shop_facts.Shop_id  )

      AND  ( Metric_Type.Metric=Metric_Type.Metric  )

    GROUP BY

      Outlet_Lookup.State,

      Metric_Type.Metric

    (0) 
  2. James Brogden

    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.

    (0) 
    1. Abhijit Ingale Post author

      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.

      (0) 
  3. hari krishna

    Hi Abhijit, 

               Is this possible when the source is Bex query  or it’s only possible with universe as data source.

    Krishna

    (0) 
    1. Abhijit Ingale Post author

      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.

      Regards,

      Abhijit

      (0) 
  4. Naveen Donthineni

    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 section.save 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.

    (0) 
  5. suresh maddineni

    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.

    Thanks

    Suresh

    (0) 

Leave a Reply