Tuesday, April 7, 2009

OBIEE Using SQL based scale makers

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:

image

image

And put it in a vertical bar graph:

image

If we want to add a scale marker goto the image axis scaling menu and press image Edit Scale markers, press add:image

Click on the edit button image

image

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:

image

And have a look at the results:

image

Till Next Time

9 comments:

Lex said...

Shouldn't the SQL statement for the MAX marker be Max not Min?

John Minkjan said...

Lex,

Yep, CTRL-C error. Fixed it! Thanks for noticing.

Regards John

Dan Gerena said...

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?

John Minkjan said...

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

Karn Ganeshen said...
This comment has been removed by the author.
Karn Ganeshen said...
This comment has been removed by the author.
John Minkjan said...

@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

Karn Ganeshen said...
This comment has been removed by the author.
Himanshu Shrivastava said...

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.