Forecasting: Getting future dates to multiple columns

[KiwiCC]

I need help with a cross tab forecasting report that shows the job plan number and estimated labor hours of that job plan for the next 1-2 years from current date. Job plans are sheduled according to sequence by monthly, quarterly, semi-annually, annually, ect...

Crystal Version 8, Oracle DB

Is there ways that I can continuously generate the next schedule date into multiple columns until the date exceeds the parameter date and get a row subtotal?

Desired report:

FutureSchedule
JobPlan# (Dates w/in Parameter) Total
---------------------------------------------------
01/20/05 04/20/05 07/20/05 .......
12345 2.0 2.0 2.0 6.0
67890 6.0 6.0 6.0 18.0
23456 3.0 3.0 3.0 9.0
---------------------------------------------------
Total 11.0 11.0 11.0

I am currently manually using multiple formulas to generate the dates. Is there a way I can use array to simplify this process.

@NextSchedule
-------------------
whilereadingrecords;
if not isnull({V_CR_EQUIP_PM_SCH.NEXTDATE}) and {V_CR_EQUIP_PM_SCH.JPSEQINUSE} = 'Y' then
if {V_CR_EQUIP_PM_SCH.FREQUNIT} = 'MONTHS' then
DateAdd("M",{V_CR_EQUIP_PM_SCH.INTERVAL}*{V_CR_EQUIP_PM_SCH.FREQUENCY},{V_CR_EQUIP_PM_SCH.FIRSTDATE})
else
if {V_CR_EQUIP_PM_SCH.FREQUNIT} = 'WEEKS' then
DateAdd("W",{V_CR_EQUIP_PM_SCH.INTERVAL}*{V_CR_EQUIP_PM_SCH.FREQUENCY},{V_CR_EQUIP_PM_SCH.FIRSTDATE})
else
if {V_CR_EQUIP_PM_SCH.FREQUNIT} = 'DAYS' then
DateAdd("D",{V_CR_EQUIP_PM_SCH.INTERVAL}*{V_CR_EQUIP_PM_SCH.FREQUENCY},{V_CR_EQUIP_PM_SCH.FIRSTDATE})
else
if {V_CR_EQUIP_PM_SCH.FREQUNIT} = 'YEARS' then
DateAdd("Y",{V_CR_EQUIP_PM_SCH.INTERVAL}*{V_CR_EQUIP_PM_SCH.FREQUENCY},{V_CR_EQUIP_PM_SCH.FIRSTDATE})
else
if isnull({V_CR_EQUIP_PM_SCH.NEXTDATE}) and {V_CR_EQUIP_PM_SCH.JPSEQINUSE} = 'N' then
DateAdd("Y",{V_CR_EQUIP_PM_SCH.INTERVAL}*{V_CR_EQUIP_PM_SCH.FREQUENCY},{V_CR_EQUIP_PM_SCH.FIRSTDATE})

@Next_NextSchedule
------------------------
whilereadingrecords;
if not isnull({V_CR_EQUIP_PM_SCH.NEXTDATE}) and {V_CR_EQUIP_PM_SCH.JPSEQINUSE} = 'Y' then
if {V_CR_EQUIP_PM_SCH.FREQUNIT} = 'MONTHS' then
DateAdd("M",{V_CR_EQUIP_PM_SCH.INTERVAL}*{V_CR_EQUIP_PM_SCH.FREQUENCY},{@NextSchedule})
else
if {V_CR_EQUIP_PM_SCH.FREQUNIT} = 'WEEKS' then
DateAdd("W",{V_CR_EQUIP_PM_SCH.INTERVAL}*{V_CR_EQUIP_PM_SCH.FREQUENCY},{@NextSchedule})
else
if {V_CR_EQUIP_PM_SCH.FREQUNIT} = 'DAYS' then
DateAdd("D",{V_CR_EQUIP_PM_SCH.INTERVAL}*{V_CR_EQUIP_PM_SCH.FREQUENCY},{@NextSchedule})
else
if {V_CR_EQUIP_PM_SCH.FREQUNIT} = 'YEARS' then
DateAdd("Y",{V_CR_EQUIP_PM_SCH.INTERVAL}*{V_CR_EQUIP_PM_SCH.FREQUENCY},{@NextSchedule})
else
if isnull({V_CR_EQUIP_PM_SCH.NEXTDATE}) and {V_CR_EQUIP_PM_SCH.JPSEQINUSE} = 'N' then
DateAdd("Y",{V_CR_EQUIP_PM_SCH.INTERVAL}*{V_CR_EQUIP_PM_SCH.FREQUENCY},{@NextSchedule})

I use many of these similar formulas to generate the future schedule date. This is cumbersome. It is a rather complex report. Anyone has better ideas? Any help will be appreciated.


KiwiCC
System Admin

 

[Madawc]

Are you allowed to create new tables? I'd have thought a table of dates would simplify everything.

Madawc Williams (East Anglia, UK)

 

[KiwiCC]

I am allowed to create tables. However, my skill is limited. How do I create a table of dates? If I had a table of dates, how would I generate the labor hours to the corresponding dates?

KiwiCC
System Admin

 

[Madawc]

Sorry, I've never done it either - someone else creates the tables where I work, though I can request what I need.

Use Search, check the FAQs and then ask again, start a new thread.

Madawc Williams (East Anglia, UK)

 

[KiwiCC]

Thanks! I will try again.

KiwiCC (CA,USA)