Note - This exercise has been carried out using OBIEE 11g. There are many ways to arrive at the solution - This one is coming to my mind and applicable at the front end.
One assumption : In the "Period" table - I have a unique identifier "Month_Id" that uniquely identifies YearMonth - Just like the Calender_Month_Id in the Times Table of SH schema that comes with Oracle database.
So let us start. In my front end - I have columns named Year and Month. I am gonna concatenate these 2 columns ( December -2002 ). I will call it as YearMonth. Now I want the user to select the
YearMonth between say December 2002 and December 2004 from the dashboard prompt and user would be shown the data filtered on YearMonth.
Look at the YearMonth prompt:
I selected the column Year and changed its column formula to -
"TIMES"."CALENDAR_MONTH_NAME"||CAST("TIMES"."CALENDAR_YEAR" AS CHAR)
I changed its Label to "Select YearMonth between"
In properties - Set the presentation variable A.
I selected one more column Monthname and new column check box and changed its column formula to -
"TIMES"."CALENDAR_MONTH_NAME"||''||CAST("TIMES"."CALENDAR_YEAR" AS CHAR)
I changed its Label to "And"
In properties - Set the presentation variable B.
I am NOT using a single column with the BETWEEN operator but 2 columns with slightly different column formulae and properties.
Now let us create 2 intermediate reports.
Carefully look at the columns, column formulae and filters.
1> INT Y-M
2>INTM Y-M
Now I am creating a main report - For the time being I have taken 3 columns namely Year, Month and Month Id.
Have a look at the filters: I have used these 2 intermediate reports as a source for these filters.
This is my main report: Final YM_2
Month Id column is sorted in ascending order.
Go to dashboard editor.
Section 2 properties - Select Condition - Give the path of main report Final YM_2
( condition : Rows > 0)
I have also added a text object - Look at the note and here is the output.
Let us hide the Month Id column from the Main report Final YM_2.
Thanks.
One assumption : In the "Period" table - I have a unique identifier "Month_Id" that uniquely identifies YearMonth - Just like the Calender_Month_Id in the Times Table of SH schema that comes with Oracle database.
So let us start. In my front end - I have columns named Year and Month. I am gonna concatenate these 2 columns ( December -2002 ). I will call it as YearMonth. Now I want the user to select the
YearMonth between say December 2002 and December 2004 from the dashboard prompt and user would be shown the data filtered on YearMonth.
Look at the YearMonth prompt:
I selected the column Year and changed its column formula to -
"TIMES"."CALENDAR_MONTH_NAME"||CAST("TIMES"."CALENDAR_YEAR" AS CHAR)
I changed its Label to "Select YearMonth between"
In properties - Set the presentation variable A.
I selected one more column Monthname and new column check box and changed its column formula to -
"TIMES"."CALENDAR_MONTH_NAME"||''||CAST("TIMES"."CALENDAR_YEAR" AS CHAR)
I changed its Label to "And"
In properties - Set the presentation variable B.
I am NOT using a single column with the BETWEEN operator but 2 columns with slightly different column formulae and properties.
Now let us create 2 intermediate reports.
Carefully look at the columns, column formulae and filters.
1> INT Y-M
2>INTM Y-M
Now I am creating a main report - For the time being I have taken 3 columns namely Year, Month and Month Id.
Have a look at the filters: I have used these 2 intermediate reports as a source for these filters.
This is my main report: Final YM_2
Month Id column is sorted in ascending order.
Go to dashboard editor.
Section 2 properties - Select Condition - Give the path of main report Final YM_2
( condition : Rows > 0)
I have also added a text object - Look at the note and here is the output.
Let us hide the Month Id column from the Main report Final YM_2.
Thanks.
No comments:
Post a Comment