Recently on my question page I was asked to make a step by step example on how to use SQL based scale markes in a graph.
Let's start with a simple report based on the sample sales repository:
And put it in a vertical bar graph:
If we want to add a scale marker goto the 
Click on the edit button 
Now the trick is to enter an SQL statement for which the first column gives back the value you want to use for your scale marker. In this case:
SELECT MIN("F1 Revenue"."1-01 Revenue (Sum All)" ) saw_0, "D0 Time"."T02 Per Name Month" saw_1 FROM "Sample Sales" ORDER BY saw_1
Do the same for the MAX marker:
SELECT MAX("F1 Revenue"."1-01 Revenue (Sum All)" ) saw_0, "D0 Time"."T02 Per Name Month" saw_1 FROM "Sample Sales" ORDER BY saw_1
Play around with the Marker caption, color en line width:
And have a look at the results:
Till Next Time
9 comments:
Shouldn't the SQL statement for the MAX marker be Max not Min?
Lex,
Yep, CTRL-C error. Fixed it! Thanks for noticing.
Regards John
Awesome!
Could one make BOTH the columns and the line prompted, so the user can toggle between different departments’ results? Can such a prompt be incorporated into the SQL for the line? I don't see how to make that happen?
Dan,
Just fill a presentation variable in your prompt and use that in your SQL :
SELECT MIN("F1 Revenue"."1-01 Revenue (Sum All)" ) saw_0, "D0 Time"."T02 Per Name Month" saw_1 FROM "Sample Sales" WHERE Deparment = @{prDepartment}{HR}
regards
John
@KG,
I have no idea how to supress them, why do you want to do that?
regards
john
BTW: Off topic questions can be posted here: http://obiee101.blogspot.com/2009/04/obiee-questions.html
Hello John,
I tried using Presentation Variable in Edit Scale Marker and here is the syntax for it
select ("Fct Route Ask Budget"."Rpk"/"Fct Route Ask Budget"."Ask")*100 from Etihad where "Dim Date"."Month Code"='@{PV_Time}'
However it is not working.Please let me know the correct syntax to use it.
Post a Comment