Solved

Custom Group Header across fiscal periods

Posted on 2008-06-19
3
593 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
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

776 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