| Author |
Message |
hammondrob
Newbie
Joined: 08 Feb 2010
Location: Canada
Online Status: Offline
Posts: 21
|
   Topic: Running Total Help
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??
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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?
|
|
IP Logged |
|
hammondrob
Newbie
Joined: 08 Feb 2010
Location: Canada
Online Status: Offline
Posts: 21
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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
|
|
IP Logged |
|
hammondrob
Newbie
Joined: 08 Feb 2010
Location: Canada
Online Status: Offline
Posts: 21
|
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?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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?
Edited by DBlank - 26 Feb 2010 at 10:51am
|
|
IP Logged |
|
hammondrob
Newbie
Joined: 08 Feb 2010
Location: Canada
Online Status: Offline
Posts: 21
|
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!!!
|
|
IP Logged |
|
hammondrob
Newbie
Joined: 08 Feb 2010
Location: Canada
Online Status: Offline
Posts: 21
|
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?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 26 Feb 2010 at 11:54am |
|
What do you want the second CT to be doing?
|
|
IP Logged |
|
hammondrob
Newbie
Joined: 08 Feb 2010
Location: Canada
Online Status: Offline
Posts: 21
|
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.
|
|
IP Logged |
|
|
|
