[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Date Parameter for Fiscal and Calendar Year

Posted on 2008-11-17
13
Medium Priority
?
516 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:maximus44
  • 6
  • 5
  • 2
13 Comments
 
LVL 16

Expert Comment

by:wykabryan
ID: 22977174
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 Comment

by:maximus44
ID: 22977238
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
 
LVL 16

Expert Comment

by:wykabryan
ID: 22977263
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 22977394
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
 

Author Closing Comment

by:maximus44
ID: 31517494
Thanks very much
0
 

Author Comment

by:maximus44
ID: 22978044
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 22978359
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 Comment

by:maximus44
ID: 22979081
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 22979961
It shouldn't.

mlmcc
0
 

Author Comment

by:maximus44
ID: 22987714
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 22988759
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 Comment

by:maximus44
ID: 22994741
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 22996135
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

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…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month19 days, left to enroll

834 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