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
maximus44Asked:
Who is Participating?
 
mlmccConnect With a Mentor 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
 
wykabryanCommented:
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
 
maximus44Author 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
wykabryanCommented:
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
 
maximus44Author Commented:
Thanks very much
0
 
maximus44Author 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
 
mlmccCommented:
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
 
maximus44Author 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
 
mlmccCommented:
It shouldn't.

mlmcc
0
 
maximus44Author 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
 
mlmccCommented:
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
 
maximus44Author 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
 
mlmccCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.