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:

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:

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

OBIEE Scale markers

According to the documentation you can set a scale marker based on a column value.

[quote]Options for these values include a static value, a variable, a value
sourced from a column included in the request, or one derived from a SQL
query.
Advanced options enables you to dynamically set values based on
results of a
column, query, or presentation variable which is evaluated
while rendering the
chart as follows.[/quote]

It seems that if a query is very complex (5 union all and some dashboard prompts) you loose the column selector (undocumented feature?).

==>> After futher research I discovered that this feature seems to
be turned on or off based on the availble columntypes. If you make the column an aggragate (SUM,MIN,MAX etc) you can use it directly.... <<==

You can add the scale marker manually by first adding a static marker and then editing the XML directly by replacing the linePosition part:

(saw:linePosition>(saw:columnRef columnID="c59"/> (/saw:linePosition>

( Replace the ( with < )
where "c59" is the column you want to use as a scale marker.