All,
Relativly new at some of the tricks of the trade.
Oracle 11g and BO3.1
I am trying to build a Current Quarter object using a Calendar table.
I have sucessfully built and used Current and Previous day and month measures to build reports.
I am trying to build one that looks at the Quarter Start date for the current day and returns the Key date for all rows in the current quarter. This would then be used as a selection against my Fact table.
I am using Trunc(sysdate) for current date, add_months(trunc(sysdate,‘MM’), -1) for Previous Month Start date and calculating the end dates accordingly.
In my report I am selecting the Business Date on my fact table using the between operator and objects, selecting my Start and End Month objects.
I can do the same using selected dates off my calendar table to create any sized range.
What I am now trying to do is build a Calendar Quarter object and select the rows off my Fact where the Business Date is in the list of dates on the Calendar table where the Quarter is the current quarter.
The following query returns data from my fact table for all dates in the current quarter.
SELECT BUS_DT FROM DW_TXN_FCT WHERE
BUS_DT IN (SELECT KEY_DT FROM DW_CAL
WHERE QRTR_ST_DT = (SELECT QRTR_ST_DT FROM DW_CAL
WHERE KEY_DT = to_date(sysdate,‘dd-MON-rr’) )
);
I am having trouble building the object that will satisfy this. Once that is created I know I can copy it to use a date prompt to select any other quarter( or year ect)
Thanks for your help!
Greg
amosgreg (BOB member since 2012-01-27)