Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Custom Group Header across fiscal periods

Posted on 2008-06-19
Medium Priority
598 Views
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.

scenario 2) range crosses fiscal period
8/15/07-11/15/07 then group one header reads 8/15/07-9/30/07
0
Question by:usczeus
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 2

LVL 77

Assisted Solution

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

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

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

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
###### Suggested Courses
Course of the Month6 days, 7 hours left to enroll

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

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