WEBI - Dynamic Line Chart driven by Input Control

Saturday, May 23, 2015

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.

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

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

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.