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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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
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
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
ASKER
That worked. Now how would I pull the other quarters?
Thanks,
Beth
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?
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?
ASKER
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
Beth
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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(para m_year,10, 01))))
When I try this I get nothing, no data for those prior quarters.
Much Appreciation,
Beth
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_
Can DateAdd be nested?
DateAdd ('yyyy',-1,(DateAdd("q",-3
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
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
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
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
ASKER
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