Current Year for certain dates

[mfernandez8]

I have a few sub-reports in a crystal report that lists clients who went into suspense between 1) October-December, 2)January-May and 3)June-September. My formula below has the dates for each group but would like to adjust the formula if possible so I don't have to fix the 'Year' for each report every year. Our fiscal Year starts October 1st, current year to September 30th, next year. So (CurrentYear) would have to fall between there somehow so if I run it today the OCT-DEC section shows only those who have a date >= to 10-1-2010 AND <=12-31-2010 and the other two section will have 0 records showing since Jan-September is for next year.

{tblTraining_Strategy_Training_Options.StatusChangeDate} in Date (2009, 10, 01) to Date (2009, 12, 31)

{tblTraining_Strategy_Training_Options.StatusChangeDate} in Date (2010, 01, 01) to Date (2010, 05, 31)

{tblTraining_Strategy_Training_Options.StatusChangeDate} in Date (2010, 06, 01) to Date (2010, 09, 30)

Thank you for any help

 

[MCuthill]

mfernadez,

Just an idea, perhaps adding a Parameter field for the report (ie: {?FiscalYear}) would a possible solution. If not, a formula field to update once on the report could acheive the same result.

If using Report Parameter, your groups formula's would then appear as:

{tblTraining_Strategy_Training_Options.StatusChangeDate} in Date ({?Parameter}, 10, 01) to Date ({?Parameter}, 12, 31)

{tblTraining_Strategy_Training_Options.StatusChangeDate} in Date ({?Parameter}+1, 01, 01) to Date ({?Parameter}+1, 05, 31)

{tblTraining_Strategy_Training_Options.StatusChangeDate} in Date ({?Parameter}+1, 06, 01) to Date ({?Parameter}+1, 09, 30)

For your example, 2009 would be keyed into the parameter prompt when running the report.

If using a formula field, you would just type in the year once per report (in your example, this formula would contain: 2009) and just need to be updated each fiscal year.

Formula Field

2009

There are a few other ideas that come to mind, where the year could be calculated based on another date field (either CurrentDate or a database field) should the above not work for your application.

Hope this helps,


Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[mfernandez8]

Perhaps I don't understand your suggestions but if I put 2009 for the parameter or formula then what about when it is January 2010 and I enter '2010' won't the numbers change for October - December 2009?

If we did the parameter field will it prompt me for each sub report?

The above three sections are individual sub reports since I wasn't sure how to provide a count for each period otherwise. Each year I update these three section along with 7 others. I suppose it is not a big deal to do this once a year but was hoping to build it where I don't have to update it and it knows when our fiscal year begins and ends or at least update it in one place versus 10.

I've attached my report that this refers to so you can see what it looks like in hopes to be helpful. If the link doesn't work just let me know. I've never used MediaFire.

Thank you for your time.
Michelle

 

[MCuthill]

Michelle,

I would have to do some testing to look into how the parameter would work in regards to subreports - I know there are ways it can work, would just need to confirm the details of the approach. I am thinking you only need one parameter field, and pass that criteria to each subreport.

One answer I do have right off the top is that you would always key in the year of your earliest period into the paramter field. In in January 2010, you still wish to see Oct 2009 forward, you would key in 2009 - with a broad assumption, perhaps you would key in the "Fiscal Year Start" value.

There may be other solutions, but I think the following would be a place to start. Unfortunately, this will work better with a formula field (updated once per year) as opposed to a parameter, as the parameter needs to be passed to a formula field to share between Main & Subreports.

TEST:
On Main Report:
1) Create a formula field as follows:

WhilePrintingRecords;
Shared NumberVar FiscalYearStart:=2009;

This is the field which would be updated once a year.

2) In first sub report, create this formula field:

WhilePrintingRecords;
Shared NumberVar FiscalYearStart;

This creates a placeholder for your value from the main report, this formula field can then be referenced in the subreport selection criteria (replacing {?Parameter} in my previous post).

Note: I am never 100% sure when to include "WhilePrintingRecords" in the code for these, so I always include it, hasn't burned me yet, but may not be needed.

If you wish to use a parameter field, the code in the first step above would be:

WhilePrintingRecords;
Shared NumberVar FiscalYearStart:={?ParameterField};

My apologies for not having tested this first myself, but I *think* it should work. Please test with one subreport before rolling out to all of them, but if it works on the first one, you just need to place a formula field in each sub to hold the Shared Variable and update your selection criteria.

I hope this helps, please advise should you encounter any issues.

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."