A few days back one of my friends came up with a problem in OBIEE, is it possible to show a line graph having 2 lines in the same graph. One line representing Revenue by a selected Sales Person and another line as the average Revenue by the remaining Sales Persons.
This might be achieved through different ways, but what I preferred was to using a Presentation Variable.
1. I have created a union report, the 1st criteria contains, Month and Avg Revenue. 'All Sales Person' is a hardcoded dummy column.
Avg Revenue is coming directly from a fact, and it is a simple Average on Revenue.
2. The 2nd criteria contains Month and Avg Revenue with Filter in the formula.
I am using the formula = FILTER("Base Facts"."Avg Revenue" USING ("Sales Person"."E1 Sales Rep Name" IN ('@{var_SP}')))
Basically it will filter the Avg Revenue for a Sales Person, and the value of the Sales Person will be passed through the Presenation Variable var_SP.
And in the dummy column I put '@{var_SP}'. This will help me show the name of the selected Sales Person in the line graph.
3. Next I have created a Prompt to filter the Year and Sales Person (to pass the value for var_SP)
4. I have put them all together in the Dashboard. When I first run the report without any Sales Person selected, it shows me only the Avg Revenue for all Sales Persons.
But the moment I select a specific Sales Person, an additional line appears.
Now it is time to validate, if my report is giving right data or not. And for that purpose I have created 2 simple seperate reports with Month, Avg Revenue and Month, Sales Person (Aurelio Miranda only) and Avg Revenue.
The result is matching with the chart, and that validates the Line chart report.
This might be achieved through different ways, but what I preferred was to using a Presentation Variable.
1. I have created a union report, the 1st criteria contains, Month and Avg Revenue. 'All Sales Person' is a hardcoded dummy column.
Avg Revenue is coming directly from a fact, and it is a simple Average on Revenue.
2. The 2nd criteria contains Month and Avg Revenue with Filter in the formula.
Basically it will filter the Avg Revenue for a Sales Person, and the value of the Sales Person will be passed through the Presenation Variable var_SP.
And in the dummy column I put '@{var_SP}'. This will help me show the name of the selected Sales Person in the line graph.
3. Next I have created a Prompt to filter the Year and Sales Person (to pass the value for var_SP)
4. I have put them all together in the Dashboard. When I first run the report without any Sales Person selected, it shows me only the Avg Revenue for all Sales Persons.
But the moment I select a specific Sales Person, an additional line appears.
Now it is time to validate, if my report is giving right data or not. And for that purpose I have created 2 simple seperate reports with Month, Avg Revenue and Month, Sales Person (Aurelio Miranda only) and Avg Revenue.
The result is matching with the chart, and that validates the Line chart report.
I found this to be a very good and informative post about Tableau and many other aspects to look for extreme and complex database operations.
ReplyDeleteTableau Rest Api Connection