Skip to Content
Author's profile photo Abhijit Ingale

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

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.

Assigned Tags

      16 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jürgen Lins
      Jürgen Lins

      not a single picture is visible, please review your blog

      Author's profile photo Abhijit Ingale
      Abhijit Ingale
      Blog Post Author

      Had to re-include all the images. Pls check now.

      Author's profile photo Jürgen Lins
      Jürgen Lins

      all images are visible now

      Author's profile photo RUC ..
      RUC ..

      Excellent !!!

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

      How will the webi Sql look like ??

      Author's profile photo Abhijit Ingale
      Abhijit Ingale
      Blog 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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Abhijit Ingale
      Abhijit Ingale
      Blog 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.

      Author's profile photo William MARCY
      William MARCY

      Great Webi trick !

      You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !

      William

      Author's profile photo Former Member
      Former Member

      Hi Abhijit,

      Nice trick and thanks for sharing.

      Sampath

      Author's profile photo Former Member
      Former Member

      Hi Abhijit, 

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

      Krishna

      Author's profile photo Abhijit Ingale
      Abhijit Ingale
      Blog 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

      Author's profile photo Former Member
      Former Member

      Hi Abhijit,

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

      Regards,

      Naveen D

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      Thanks for sharing the knowledge!

      I appreciate the time , help and effort.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo RUC ..
      RUC ..

      Did you tried report element function in WEBI ??