Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Custom Group Header across fiscal periods

Posted on 2008-06-19
3
Medium Priority
?
599 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 500 total points
ID: 21829768
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
ID: 21832736
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
ID: 21848366
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Integration Management Part 2
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

885 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