Date Prompts In Reports

I am working with designer 6.1a and a DB2 database. My problem is this:

I currently have a universe that prompts for a to and from date based on if the user selects 1 of 2 different date objects. The code is:

CASE 
WHEN @Prompt('Date Criteria:','A',{'Transaction Date','Settlement Date'},mono,constrained) = 'Transaction Date' 
THEN @Select(Transaction Details\Transaction Date) 
WHEN @Prompt('Date Criteria:','A',{'Transaction Date','Settlement Date'},mono,constrained) = 'Settlement Date' 
THEN @Select(Transaction Details\Settlement Date) 
END >= @Prompt('From Date:','D',,mono,free)

AND

CASE 
WHEN @Prompt('Date Criteria:','A',{'Transaction Date','Settlement Date'},mono,constrained) = 'Transaction Date' 
THEN @Select(Transaction Details\Transaction Date) 
WHEN @Prompt('Date Criteria:','A',{'Transaction Date','Settlement Date'},mono,constrained) = 'Settlement Date' 
THEN @Select(Transaction Details\Settlement Date) 
END <= @Prompt('To Date:','D',,mono,free)

This code needs to be changed so that there is no longer a to and from date entered, but rather they get prompted for:

last week (Monday 00:00:00 through Sunday 23:59:59)
last month (00:00:00 1st calendar day of previous month through 23:59:59 of last calendar day of previous month)
last quarter (00:00:00 of 1st calendar day of the calendar qtr through 23:59:59 of last calendar day of the calendar qtr)
last year (Jan 1 through Dec 31)

They should still be able to choose either the transaction date or settlement date for either of these options. Do I need to create new objects for each of the above parameters and try modifying the existing prompt to incorporate them? You’ll have to forgive me, I haven’t worked much with prompts like these.

Any help is appreciated.
Thanks,
Shelley


Shelley (BOB member since 2003-09-15)

One more thing I should have mentioned … I’m working with DB2 for IBM390 - that seems to limit my capabilities. My biggest obstacle seems to be defining these time frames.

Thanks in advance for any help,
Shelley


Shelley (BOB member since 2003-09-15)

I thought I would post this solution for other future struggling souls. This hasn’t been fully tested against the database, but does parse in BO. This was done on DB2/0S390.

To reiterate the request, the users asked to be prompted to select a date (ability to choose between 2 dates in this example) and be given a list of values consisting of last week, last month, last quarter or last year. The defined ranges for these values were:

last week = Monday - Sunday
last month = 1st calendar day of previous month - last calendar day of previous month
last quarter = 1st calendar day of last calendar quarter - last calendar day of last calendar quarter
last year = Jan 31 of previous year - Dec 31 of previous year

The prompt logic is as follows:

CASE 
WHEN @Prompt('Date Type:','A',{'Transaction Date','Settlement Date'},mono,constrained)='Transaction Date' 
THEN (EF.TEF_PIN_DETAIL.D_TR)
WHEN @Prompt('Date Type:','A',{'Transaction Date','Settlement Date'},mono,constrained)='Settlement Date' 
THEN (EF.TEF_PIN_DETAIL.D_STLM) 
END

BETWEEN

CASE 
WHEN @Prompt('Date Range:','A',{'Last Week','Last Month','Last Quarter','Last Year'},mono,constrained)='Last Week' 
THEN (current date) - (dayofweek(current date)+5)days
WHEN @Prompt('Date Range:','A',{'Last Week','Last Month','Last Quarter','Last Year'},mono,constrained)='Last Month' 
THEN (current date - 1 month) - dayofmonth(current date - 1 month)days + 1 days
WHEN @Prompt('Date Range:','A',{'Last Week','Last Month','Last Quarter','Last Year'},mono,constrained)='Last Quarter' 
THEN 
(CASE quarter(current date) 
WHEN 1 THEN date(cast((YEAR(current date)-1) as char(4))||'-10-01')
WHEN 2 THEN date(cast((YEAR(current date)) as char(4))||'-01-01')
WHEN 3 THEN date(cast((YEAR(current date)) as char(4))||'-04-01')
WHEN 4 THEN date(cast((YEAR(current date)) as char(4))||'-07-01') END)
WHEN @Prompt('Date Range:','A',{'Last Week','Last Month','Last Quarter','Last Year'},mono,constrained)='Last Year' 
THEN date(cast((YEAR(current date)-1) as char(4))||'-01-01')
END

AND

CASE 
WHEN @Prompt('Date Range:','A',{'Last Week','Last Month','Last Quarter','Last Year'},mono,constrained)='Last Week' 
THEN (current date) - (dayofweek(current date) - 1)days
WHEN @Prompt('Date Range:','A',{'Last Week','Last Month','Last Quarter','Last Year'},mono,constrained)='Last Month' 
THEN last_day(current date - 1 month)
WHEN @Prompt('Date Range:','A',{'Last Week','Last Month','Last Quarter','Last Year'},mono,constrained)='Last Quarter' 
THEN
(CASE quarter(current date) 
WHEN 1 THEN date(cast((YEAR(current date)-1) as char(4))||'-12-31')
WHEN 2 THEN date(cast((YEAR(current date)) as char(4))||'-03-31')
WHEN 3 THEN date(cast((YEAR(current date)) as char(4))||'-06-30')
WHEN 4 THEN date(cast((YEAR(current date)) as char(4))||'-09-30') END) 
WHEN @Prompt('Date Range:','A',{'Last Week','Last Month','Last Quarter','Last Year'},mono,constrained)='Last Year' 
THEN date(cast((YEAR(current date)-1) as char(4))||'-12-31')
END

Shelley (BOB member since 2003-09-15)

Shelley,

We find the best solution is to create aliases of a calendar_date and a date_parameters table, so we can then connect this pair as needed to as many date fields as necessary on each fact table. Then we create predefined condition objects in the universe based on this parameter table, making it very easy for users to choose, use, and mix’n’match them: “Next Fiscal Qtr”, “Previous 13 Months to End of Prev Month”, etc…

See more details in Linking summary tables for aggregate aware or Time condition in two contexts, and of course by searching on this topic.


JP Brooks :us: (BOB member since 2002-10-22)