• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

Custom Group Header across fiscal periods

I am grouping data across a fiscal year.  I took a date (discharge) and used a formula to calculate an effective fiscal year like this month(disch_date) > 9 then fyear=dyear+1, else fyear=dyear.

I can group them just fine by period they belong to, but I am trying to create a custom header that takes a parameter into account.  The parameters are a start and date range to group data by.  I need the header to take these into account when reporting.

scenario 1) range doesn't cross fiscal period.
8/15/07-9/15/07 then header reads 8/15/07-9/15/07

scenario 2) range crosses fiscal period
8/15/07-11/15/07 then group one header reads 8/15/07-9/30/07
group two header reads 10/1/07-11/15/07
0
usczeus
Asked:
usczeus
  • 2
2 Solutions
 
peter57rCommented:
See sample attached.
The sample only copes with data which falls into either one or two fiscal years - if the selection dates span 3+ fiscal years it will not be correct.  Change the extension from txt to rpt.  (CRXI)
Reportfiscalgp.txt
0
 
usczeusAuthor Commented:
Here's what I have so far:

if {?2Start_Date} > {?3End_Date} then 'wrong date selection'

else if month({?3End_Date}) < 10 then
'Effective Year ' & {?2Start_Date} & '- 9/30/'& totext({FiscalYear},0,'')

else if month({?2Start_Date}) > 9 then
'Effective Year 10/1/' & totext({FiscalYear}-1,0,'') & ' - ' &{?3End_Date}

else if year({?2Start_Date}) = {FiscalYear} then
'Effective Year ' & {?2Start_Date} & '- 9/30/'& totext({FiscalYear},0,'')

else
'Effective Year 10/1/' & totext({FiscalYear}-1,0,'') & ' - ' &{?3End_Date}

The only caveat where it doesn't work is something like 9/1/07 - 3/1/08 because of the >9 part above
0
 
usczeusAuthor Commented:
Here's what I came up with, seems to work across multiple years.

numberVar dtetest := DateDiff ("d",{?2Start_Date}, {?3End_Date});

     if {?2Start_Date} > {?3End_Date} then 'Wrong date selection'
// don't cross Oct and < 1yr
else if dtetest <= 366 and
        (date({FiscalYear},10,1) <= {?2Start_Date} and {?3End_Date} < date({FiscalYear}-1,10,1)) or
        ({?2Start_Date} >= date({FiscalYear}-1,10,1)and {?3End_Date} < date({FiscalYear},10,1)) then
     ' Effective Year ' & {?2Start_Date} & ' - ' & {?3End_Date}

// do cross Oct and < 1yr before Oct
else if dtetest <= 366 and date({FiscalYear}-1,10,1) < {?2Start_Date} then
     ' Effective Year ' & {?2Start_Date} & ' - 9/30/'& totext({FiscalYear},0,'')

// do cross Oct and < 1yr after Sep
else if dtetest <= 366 then
     ' Effective Year 10/1/' & totext({FiscalYear}-1,0,'') & ' - ' &{?3End_Date}

// > 1yr before Oct
else if {?2Start_Date} >= date({FiscalYear}-1,10,1)  then
     ' Effective Year ' & {?2Start_Date} & ' - 9/30/'& totext({FiscalYear},0,'')

// > 1yr after Sep
else  if {?3End_Date} < date({FiscalYear},10,1) then
     ' Effective Year 10/1/' & totext({FiscalYear}-1,0,'') & ' - ' &{?3End_Date}

// > 1yr full  year
else
' Effective Year 10/1/' & totext({FiscalYear}-1,0,'') & ' Thru 9/30/' & totext({FiscalYear},0,'')
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now