Cal Answers Analysis Training Part III – Advanced OBIEE – Dashboard Reports
March 2012 Page 10
Defining Your Dashboard Prompt
At this point, your dashboard prompt editor will look something like this, with one row for the “page,”
or set of prompts, and then one row for each of the individual prompts:
To edit these, either double-click a row or
highlight it and then click the pencil icon
“Edit” toolbar button. If you edit the “Page 1”
row, for instance, you’ll see the dialog shown
to the right. You can change various things
about the formatting and headings here.
Now let’s move on to the Academic Year
prompt. Open the “Edit Prompt” dialog for
that field, and try setting some of those
options. Remember that we want to let the
user choose any set of academic years from
2000-01 onward. So, we’ll want to use a
choice list (or possibly a list box or check
boxes), with appropriate display and default
values, and a nice label of some sort.
Under “Options – Choice List Values,” you’ll have to use “SQL Results,” since we only want a specific set
of years to show. Note that this refers to the OBIEE logical SQL that we talked about briefly way back in
the first class, rather than actual database SQL. The good news on that is that you don’t have to write it
all yourself, although it will give you a head start. You can just create a query in Analysis and copy the
logical SQL (from the Advanced tab) that it produces.
Anyway, for the “Choice List Values,” you want an SQL statement that will only retrieve academic years
between 2000-01 and 2011-12. It would look like this:
SELECT "Calendar - Snapshot Date"."Academic Yr" FROM "Student Counts - Degrees" WHERE
"Calendar - Snapshot Date"."Academic Yr" BETWEEN '2000-01' AND '2010-11' ORDER BY
"Calendar - Snapshot Date"."Academic Yr" DESC
Note that you can’t just say, WHERE "Calendar - Snapshot Date"."Academic Yr" >= '2000-01'
because that will also retrieve future years (the academic calendar is typically defined five years out)
that have no data yet. So, you’ll have to change this SQL each time a new year’s worth of data gets
loaded into the EDW. Well, OK, there is a way to select only years that actually have degree data, but it
requires a fiendishly complicated SQL statement:
SELECT t.AY FROM (SELECT "Calendar - Snapshot Date"."Academic Yr" AS AY, "FACT -
Academic Degree Counts"."University Nm" AS JF FROM "Student Counts - Degrees" WHERE
"Calendar - Snapshot Date"."Academic Yr" >= '2000-01' ORDER BY "Calendar - Snapshot
Date"."Academic Yr" DESC) t