SQL Developer

Hi Mark,

Before going into solutions, could you tell us about the problem you are trying to solve?

Well, OK, Sherry, you asked for it!     Long-winded explanation ensues.

First, forget about months.  I was just trying to use a simple example.  The actual attribute column at issue is Day.

My client needs to see a series of graphs on a dashboard, one line-bar graph of daily results for each day selected in the dashboard prompt.  (Luckily, only the past 30 days are presented in this particular dashboard prompt, so there will be a maximum of 30 graphs.)  There are no rules about which days can be selected.  The user can select any number of days, and they don't have to be contiguous.

The display requirement is that the graphs should be presented side-by-side on a dashboard page, in rows of 5 graphs each (maximum of 6 rows).

Because there is no way to know how many days the user will select, there is also no way of knowing how many graphs need to be presented on the dashboard at any given time.

So my thought is this: Create 30 almost identical analyses - same columns, same graph.  The only difference is in the filters.  Analysis #1 would look for the first value in the presentation variable as its filter condition for Day.  Analysis #2 would look for the 2nd value in the presentation variable, and so forth. This means that if we only select 4 values of day, then analyses #5 through #30 would fail to find their respective positional values in the presentation variable, and would return no rows.

Then we will put each graph into its own column/section on the dashboard, starting with #1 at the top left and #30 at the far bottom right.  And finally, we will display each section conditionally, so that if its corresponding analysis returns no rows, then that section is not displayed.

In summary, I need to be able to select the 1st, the 2nd, the 18th, generally the specified Nth value of a presentation variable to specify it in a filter condition.

Appreciate the long-winded explanation

Not quite get the side by side display requirement, as things could get inconsistent with display sizes and browser (I may be wrong with this). A slight variation from your idea would be to create 30 reports with two filters on the date. One with the hard coded date (since it will only be past 30 days, you can start from day 1 to day 30 (Using SQL_TSI and CURRENT DATE you can get the dates dynamically)) and the second will filter on the variable.

The other option is one report with sections. But that would display vertically. Though it seems you could use js to modify the display as seen in this blog Oracle Business Intelligence OBIEE 101: OBIEE Horizontal Pivot Sections

I hope someone else will jump in and provide a better solution. In the mean time I'll be seeing if there is a better approach.

Yeah, we considered the sections, but they want to to see sort of a trellis-type of approach - rows and columns. 

The information you referenced at this blog looks interesting.

Oracle Business Intelligence OBIEE 101: OBIEE Horizontal Pivot Sections Part 2

In this specific example, as currently defined, that could work, but the limit on the number of sections in a graph would make a section-based solution fall apart for larger numbers of selected time periods. Woe be unto us if management ever increased the number of potential days beyond the limits of the number of sections allowed in a graph.

I have discovered one very inelegant solution that involves an analysis that filters the Day column based on the presentation variable.  That analysis contains 31 columns, one for the Day column, plus 30 more columns, one for each potential day, with formulas that look like this (day 3 shown):

max(case when count(rsum(1)) - rank("Times"."DateofDay")+1 = 3 then "Times"."DateofDay" else null end)

Then in the each of the 30 analyses I use a 'based on the results of another analysis' filter and point to the correct column in this source analysis.  It works fine, but it just seems so darned inelegant, ya know?  It would simplify things enormously if I could just grab a positional value from the presentation variable.

I suppose another option would be to write a custom database function, then call it with EVALUATE.  But I'm trying to avoid going outside of OBIEE, of course, because an external function would only exist on that database, and therefore would not be appropriate for all uses in different physical environments.