# Date Parameter for Fiscal and Calendar Year

I need help creating a parameter that will only pull into my report fiscal or calendar year.

FYI my date range is coming from the db as datetime.

The FY is always Oct to Sept  (Oct 1, 2007 to Sept 30, 2008 ) and of course Calendar (Jan 1, 2007 to Dec 31, 2007)

Thanks
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
So you want a calendar to pull your fiscal calendar? for example if I put in a date range of Oct 1-31 In the fical calendar that is really Jan 1-31.
0
Author Commented:
Hi, I am confused.  The user will usually always enter in month ranges.  If Oct 1 is the start of the Date Range then we know they are looking for FY if they enter in the START DATE anything but Oct we can say it will be Calendar Year.  Does that make sense?  So something like if the start date begins with "Month 10" it is considered FY.
0
Commented:
No I do not think that logic is sound because what happens when they truly do want to see what the report is for the month of Oct. Being that it is a date range, the user could enter the 1st through the 31 of Oct and that doesnt mean he want FY.  Now if you want you could do a parameter that say Fiscal or Calendar, then do a date range. In your logic depending on the parameter it would pull fy or calendar. Adds more flexiblity.
0
Commented:
If the options are always Calendar or Fiscal why not have parameter for that then in the Select expertconvert it

Local NumberVar CYear := Year(CurrentDate);
If {?RangeType} = 'Calendar' then
{DateField} In Date(CYear,1,1) to Date(CYear,12,31)
else
{DateField} In Date(CYear-1,10,1) to Date(CYear,9,30)

If you need to use other years then wither setup a true date range and have the users select the range they want or have 2 prompts Calendar/Fiscal and the year.

If you want to use a single date and if the date is in October then use Fiscal
Local NumberVar CYear := Year({?DateParam});
Local NumberVar CMonth := Month({?DateParam});
If CMonth <> 10 then
{DateField} In Date(CYear,1,1) to Date(CYear,12,31)
else
{DateField} In Date(CYear-1,10,1) to Date(CYear,9,30)

mlmcc
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thanks very much
0
Author Commented:
This Formula is working great for my two new parameters FY or CY

Local NumberVar CYear := Year(CurrentDate);
If {?My Parameter} = 'Calendar' then
{CnGf_1.CnGf_1_Date}In Date(CYear,1,1) to Date(CYear,12,31)
else
{CnGf_1.CnGf_1_Date}In Date(CYear-1,10,1) to Date(CYear,9,30)

How can I add onto this a specific year. Example

User picks "Calendar Year" but wants to see last three years of that FY. I would probaby add in another parameter with years.

2000
2001 and so on but how can I use the above but have the user pick a year and have the report only show me the last three calendar yrs or last three fiscal years?

Thanks

0
Commented:
To see the last 3 years for CY and FY use

Local NumberVar CYear := Year(CurrentDate);
If {?My Parameter} = 'Calendar' then
{CnGf_1.CnGf_1_Date}In Date(CYear-2,1,1) to Date(CYear,12,31)
else
{CnGf_1.CnGf_1_Date}In Date(CYear-1,10,1) to Date(CYear,9,30)

If they want to specify a particular year then
Local NumberVar CYear := {?YearParameter};
If {?My Parameter} = 'Calendar' then
{CnGf_1.CnGf_1_Date}In Date(CYear-2,1,1) to Date(CYear,12,31)
else
{CnGf_1.CnGf_1_Date}In Date(CYear-1,10,1) to Date(CYear,9,30)

mlmcc
0
Author Commented:
Hi, I am putting all this into a cross tab and I do not see the three years coming through. Could it be due to the fact this is in a cross tab?  Thanks
0
Commented:
It shouldn't.

mlmcc
0
Author Commented:
Is it possible to add to this formula a choice of Date Range?  Could the user enter in One Month date range?  So it would be like they have a few options to run the report. Fiscal, Calendar, by year and lastly by date range?                 BELOW is the orginal formula:To see the last 3 years for CY and FY use

Local NumberVar CYear := Year(CurrentDate);
If {?My Parameter} = 'Calendar' then
{CnGf_1.CnGf_1_Date}In Date(CYear-2,1,1) to Date(CYear,12,31)
else
{CnGf_1.CnGf_1_Date}In Date(CYear-1,10,1) to Date(CYear,9,30)

If they want to specify a particular year then
Local NumberVar CYear := {?YearParameter};
If {?My Parameter} = 'Calendar' then
{CnGf_1.CnGf_1_Date}In Date(CYear-2,1,1) to Date(CYear,12,31)
else
{CnGf_1.CnGf_1_Date}In Date(CYear-1,10,1) to Date(CYear,9,30)

0
Commented:
Sure.

Problem is they will have to enter a value for all parameters.
It might be easier just to allow them to enter a start date and end date.

mlmcc
0
Author Commented:
But would I still be able to catorgize it as FY or CY.  If the user chose Jan 1 to Jan 31 that would be calendar but if the start date begins with Oct and the end date begins with Sept it would be considered FY.  Could you provide code for this?
0
Commented:
Are you trying to do this with the Crystal prompt page?

It sounds like you want the user to be able to specify one of 3 things.
FY then a year or range of years
CY then a year or range of years
An independent date range

Is that what you want?
If so you probably will have trouble doing it in the Crystal parameter prompts.

Do you still want to go back some calendar years?

mlmcc
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.