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 axis scaling menu and press Edit Scale markers, press add:
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