Solved

Custom Group Header across fiscal periods

Posted on 2008-06-19
3
592 Views
Last Modified: 2011-10-19
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
Comment
Question by:usczeus
  • 2
3 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 125 total points
Comment Utility
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
 

Author Comment

by:usczeus
Comment Utility
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
 

Accepted Solution

by:
usczeus earned 0 total points
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now