Link to home
Start Free TrialLog in
Avatar of geek-goddess
geek-goddess

asked on

Single Date Parameter to run financial Quarterly Report in Crystal

I have 1 date parameter called {?Quarter} and it is defined as a date.  I want the user to enter this date (ie. 2008-05-01) and then want the report to pull all financial data from that entire quarter from a table field {table.invdate}.  Then summarize that data.  I've got the summarizing down, but my record selector does not seem to care about the date entered.  It is giving me all data for all quarters that year.

I do want this info, but I'd like the report to define the quarter and print that quarter's data first and then the 3 previous quarters.

Based on the above date entered example of 2008-05-01:

Quarter          Invoice Date          Invoice #          Sales         Tons
2nd                5-1-08                   123                   $2000        150
2nd                5-2-08                   246                   $1500        125
The above would be my details section (suppressed or not).

Group #1 Footer - Summarized

2nd               Invoice Date & # - suppressed     $3500        275    >>Year (2008)
1st                                                                      $4000        300     >>Year (2008)
4th                                                                      $3000        285     >>Year (2007)
3rd                                                                      $3000        290     >>Year (2007)

Any suggestions?

Thanks so much.
Beth
SOLUTION
Avatar of dougvarga
dougvarga
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of geek-goddess
geek-goddess

ASKER

I was trying to avoid the user having to enter multiple dates, it seems like a waste of time, given that Crystal has Calendar1stQtr and similar functions/commands.  Shouldn't that be something that I can use?

I have the user already selecting Customer and Product criteria, I was trying to eliminate the entry of 2 dates.  I am very comfortable with Date Ranges and how they work, additionally I have written several reports with this type of set-up.  Is there anyway, I can pull data from a quarter with just 1 date entered and if so, how?

Thanks,
Beth
Your customer would only be entering one date (I call it {?date_param} in my example).


The formulas I provided evaluate the date that is entered as a parameter, then return the beginning and ending dates of the quarter.  The user does not see any of this.

The Calendar1stQtr etc. will work if you're always going to be running for the current year, but if you are running for prior years it will not work.

-Doug
Let me try this.  Will you be able to help me with the prior year's part or will I need to post again?
Thx,
B
I'll certainly take a shot!

Please clarify -

Are you always looking for the 3 quarters prior to the quarter of the date that the user inputs?
Or
Are you looking for the other 3 quarters of the fiscal year that the user inputs?

-Doug
That worked.  Now how would I pull the other quarters?
Thanks,
Beth
Please clarify -

Are you always looking for the 3 quarters prior to the quarter of the date that the user inputs?
Or
Are you looking for the other 3 quarters of the fiscal year that the user inputs?
I need the quarter entered and the 3 prior quarters of the date entered to show on the report.  Your help is greatly appreciated.
Beth
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Great, so it works, as long I enter in a date where the prior quarters exist in the same year.  For example, I enter in 2008-07-14 and it gives me all data for 3rd quarter (which my date is in), then 2nd quarter and finally 1st quarter.  ***Here I'd like the last quarter of 2007 also.

However, if I enter in 2008-05-01 then I get only the 1st and second quarters of 2008.  ***Here I'd like the 3rd and 4th quarters of 2007.

Isn't there a little trick with subtracting a year?  'yyyy',-1  Similar to your line of code >>>  DateAdd("q",-3,Date(param_year,10,01))

Can DateAdd be nested?
DateAdd ('yyyy',-1,(DateAdd("q",-3,Date(param_year,10,01))))

When I try this I get nothing, no data for those prior quarters.

Much Appreciation,
Beth
The Year Should not be a problem here, you should not have to explicitly handle it because the DateAdd by Quarter does that for you.

If you're not getting that result, you may have mistyped the formula.

Run the report with 2008-05-01 as the parameter and then drop the formulas onto the report header.   Make sure you're getting the dates listed below.

Example - If date_param = 2008-05-01

qtr_start = 2007-07-01

qtr_end = 2008-06-30

-Doug
The dates are correct, as thought they do show in my header info. correctly.  However, no data is populating in for the 3rd and 4th quarters.  I am pretty sure I know why, I am just not sure how to fix it.  Here is the problem... in the sales and tons formulas I am using the CalendarXXXQtr functions.  3rdQtrSales is defined as
If {table.invdate} in Calendar3rdQtr Then
   {table.price} else
0
Likewise
If {table.invdate} in Calendar4thQtr Then
   {table.price} else
0

So I guess what I am doing - is asking for the prices in the calendar year of 2008-05-01, right?
What would the correct formula be for previous quarters regardless of the criteria year?

Thx,
B
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You are the MAN!!!  The numbers are right.  Thanks a million!  So I will be assigning points to you and I guess I will split them amongst your answers.  

One more quickie, please...
How would I adjust the 2 formulas, // {@qtr_start} and //{@1stQtrSales} to do this exact same thing for 6 month intervals or 1st/2nd half CALENDAR year?  For example, the prompt 2008-05-01...
to get:  1st 6 month/half year totals (2008-01-01 thru 2008-06-30)
then prior 6 month/half year totals (2007-07-01 thru 2007-12-31).

I did try using the same trick but with months; however, my numbers were funky.

Again, much appreciation!!!
Beth
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you immensely.  I signed up for a free trial and then just 1 month of access to this site.  Because of your help, I am going get my boss to buy 6 mos. to a year of service.

I think that I have increased the points.  Now I will assign them all to you with various amounts at each one of your answers with code.  One last question, is there somewhere (a website, perhaps) that I can go to get good explanations and great examples of how the Crystal functions work?  I am even interested in buying a book with the function details in it, if you know of any good books.

Much Appreciation and in case you didn't know... YOU ARE THE MAN w/the CRYSTAL PLAN!!!

Beth

You are the BOMB-diggity!
Thanks again, and I hope to work with you again.
Hope you are happy w/the increase in points.
Please keep in touch.  My site is http://www.bship.com
Ciao,
Beth
Ken Hammady has a series of PDF books that I have learned a great deal from over the years.  This site (experts exchange) has also been a tremendous resource.

Check out Ken's site at http://www.kenhamady.com/index.html

** Note I am not affiliated with Ken in any way shape or form!  I've just bought all his books!

Glad I could help,

Best,
Doug