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.
This way any number of Metrics can be shown on Line Charts without making it look cluttered.
not a single picture is visible, please review your blog
Had to re-include all the images. Pls check now.
all images are visible now
Excellent !!!
Is the Derived table independent or Joined to any other table ??
How will the webi Sql look like ??
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
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.
Great Webi trick !
You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !
William
Hi Abhijit,
Nice trick and thanks for sharing.
Sampath
Hi Abhijit,
Is this possible when the source is Bex query or it's only possible with universe as data source.
Krishna
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
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
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.
Thanks for sharing the knowledge!
I appreciate the time , help and effort.
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
Did you tried report element function in WEBI ??