Running Total Help
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=9233
Printed Date: 17 May 2024 at 8:42pm
Topic: Running Total Help
Posted By: hammondrob
Subject: Running Total Help
Date Posted: 26 Feb 2010 at 9:57am
|
Using CR 11
I have a report which has a 'date range' parameter prompt for the user. After the user selects the appropriate range (Feb 1, 2009 to June 1, 2009), the records are filtered and the crosstab will display the months in the date range (Feb, Mar, Apr, May and June) with the amount of records for each month, in a running total (Feb's total will be added to March's total, March's total added to May's total etc) Here is the issue: There are records PRIOR to February that i need added to teh February total. For example, if there were 10 Records in January, and 7 records in February, i want February to display the total amount of previous records + the February running total (which would equal 17). Is this possible?? |
Replies:
Posted By: DBlank
Date Posted: 26 Feb 2010 at 10:18am
|
What is your exact logic around this with user defined params?
Since the user can change the the dates what happens if they enter march1-june1?
|
Posted By: hammondrob
Date Posted: 26 Feb 2010 at 10:26am
|
Thanks for the reply.
Basically, our database has records dating back to June 2008. No matter which ?start_date the user defines, i want all the records PREVIOUS to the ?start_date added INTO the ?start_date month. ******************************* For example: June 08 has 20 records July 08 has 10 records August 08 has 5 records The user selects August 08 as the ?start_date. Then the August running total should start at 35 records (20+10+5) ******************************* Or, I also thought about creating a subreport to sum up and display all of the records prior to the ?start_date, which is not ideal, but could work. |
Posted By: DBlank
Date Posted: 26 Feb 2010 at 10:30am
|
In the select expert table.datefield<?enddate
In a formula field
if table.datefield<?startparam then ?startparam else table.datefield
Use this formula field for your RT calculations
|
Posted By: hammondrob
Date Posted: 26 Feb 2010 at 10:42am
|
Okay,
1. In my Record Selection i already have teh following: {table.Date}>={?date_start} and {table.Date}<= {?date_end} 2.I created a formula named 'calc' and added to the 'Field To Summarize' within the Running Total, set to Count. Forumla: if {table.Date} < {?date_start} then {?date_start} else {table.Date} Is this correct? |
Posted By: DBlank
Date Posted: 26 Feb 2010 at 10:50am
|
Nope.
Your select statment can NOT exclude the records < {?date_start}.
If you exclude them they cannot be counted. It should just be the second half of your statement...
{table.Date}<= {?date_end}
For part 2 I am assuming your CrossTab is set up with a ROW or Column on the Table.date field and that is set to group per month.
Change that from using the table.date field to using the @calc formula field (grouped on the month still).
Basically all I did was move all your dates that are < then the start param to be= start param thereby moving them to all be counted in the first month of the report.
Make sense?
|
Posted By: hammondrob
Date Posted: 26 Feb 2010 at 11:01am
lol, i think that worked man!!! 
I will have to do some more testing, but it looks like everything is working! Thank you very much man, you are awesome!!! |
Posted By: hammondrob
Date Posted: 26 Feb 2010 at 11:52am
|
Hey DBlank,
I have another issue, but i have resolved some of it myself. I actually have 2 CT's on this report. The one we worked on has the RT, which works. However, the other CT was pulling records all the way back to June 08, no matter what the date range was. It works better now that i created a forumla called 'apps', which looks like this: ************************************ if {table.date} <= {?date_start} then {?date_end} else {table.Date} ************************************* I put the 'apps' formula in the 2nd CT as the 'Column' and changed the Group Options to print for each month. The issue is that the date selected in my ?date_end parameter always has a crazy high amount. Do you see what the problem could be? |
Posted By: DBlank
Date Posted: 26 Feb 2010 at 11:54am
| What do you want the second CT to be doing? |
Posted By: hammondrob
Date Posted: 26 Feb 2010 at 11:57am
|
The 2nd CT is displaying the individual month totals.
So, there is no running total. June will have 18 records July will have 12 records August will have 23 records At the end of the CT there is a total, which just adds up all the amounts. |
Posted By: DBlank
Date Posted: 26 Feb 2010 at 12:07pm
|
Drop your second formula field. It is throwing it all into the last month hence the huge numbers. So you want it to only show the months between the param dates and show a count of records per month?
If you are counting records and you have no dupe records make a formul field as MOnthCount:
if {table.date} <= {?date_start} then 0 else 1
Continue to group on the altered MOnth field formula (same as your first CT) but now instead for your Summarized Field use the @MonthCOunt formula as a SUM
|
Posted By: hammondrob
Date Posted: 26 Feb 2010 at 12:20pm
|
I probably should have mentioned that there are 4 summarized fields in this CT, and there is a grand total at the very end of the CT.
It is only the ?date_end month that is getting the huge number. I did add your formula to my summarized fields, but the numbers did not add up correctly.. Sorry for all the posts! |
Posted By: DBlank
Date Posted: 26 Feb 2010 at 12:35pm
|
No prob.
can't quite visulaize your CT so having trouble giving you an answer.
The reason your last month has a huge # is because that is where you 'moved' all the early records to in your formula.... if {table.date} <= {?date_start} then {?date_end} else {table.Date} ....
What are the 4 things you are doing in the CT?
|
Posted By: hammondrob
Date Posted: 26 Feb 2010 at 12:50pm
|
Hey dBlank,
Take a look at the screen photo below: This will give you an idea of what the CT looks like. http://i595.photobucket.com/albums/tt37/hammondrob/cr.jpg |
Posted By: DBlank
Date Posted: 26 Feb 2010 at 6:18pm
|
Sorry, had to run out.
What is the field that gives John 217 for Jan 09 and how is it calculated? Meaning is it a count of sale records or Sum of a sales amount, or something else?
My guess is a SUM of a sales amount (per worker). If so create a formula field to zero out the amount field if the row has a date < ?param start called 'SalesUpdated':
if {table.date} <= {?date_start} then 0 else table.salesamount
Now you make the field to summarize as a SUM of this new formula @SalesUpdated
Does this make sense? |
Posted By: hammondrob
Date Posted: 01 Mar 2010 at 6:48am
|
hey dblank,
I was away for the weekend, and just saw your response now. thank you so much for your help again. your formula worked! and yes, the 217 for john was a sum indeed. There is just one more small issue. Check out the link: http://i595.photobucket.com/albums/tt37/hammondrob/ct2.jpg If you look at '08 Jun in CT1 and '08 Jul in CT2, you will notice that the RT does not pick up the 2 records in June (the equation is CT1 Jun 08 + CT1 Jul 08 = CT2 Jul 08). That problem also applies to the CT2 '08 Sept RT. Any idea? |
Posted By: hammondrob
Date Posted: 01 Mar 2010 at 8:15am
|
I just figured out where the missing records were hiding.
Thank you so much for your help, i really appreciate it. |
Posted By: DBlank
Date Posted: 01 Mar 2010 at 8:17am
|
hard to tell but it is most likely how you have it resetting. That is in the SUmmary set up or in the last section of a Running Total. |