Sum of Running total(s)
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8318
Printed Date: 05 May 2024 at 7:19am
Topic: Sum of Running total(s)
Posted By: nabilino
Subject: Sum of Running total(s)
Date Posted: 12 Nov 2009 at 1:14pm
|
Hi everyone,
I have running totals summing deduction codes by month. I want to sum up all the months for a yearly total and display it on the same detail line.
for ex.
Jan. Feb. ... YTD
$10 $5 ... $15
I tried doing that using another running total but it doesnt sum on other running totals. The only way I think is to do a formula and display it, but I don't know how to write the formula.
I tried Sum({#January}{#February}...) but it didn't work
I also tried {#Jan} + {#Feb} +... but it displays blanks
Any ideas?
Thank you
|
Replies:
Posted By: Grifter
Date Posted: 13 Nov 2009 at 6:15am
|
I think you need to go into formula editor in the field explorer window. Right click formula editor and create new calculation. Once your in the formula editor you can select Formul, Selection and Formatting from the left window. From their I think you can make up some sort of calculation or sum, haven't ever done one myself though. G |
Posted By: nabilino
Date Posted: 13 Nov 2009 at 7:29am
| I tried that but it won't let me select my running totals fields which I want to sum and display for YTD total. |
Posted By: DBlank
Date Posted: 13 Nov 2009 at 8:44am
|
Originally posted by nabilino
Hi everyone, I also tried {#Jan} + {#Feb} +... but it displays blanks
Did you palce this anywhere but the report footer?
If so it likely would by all blank. Running Totals only work when placed below the data that it is calculating, in this case all rows therefore on the report footer.
|
Posted By: nabilino
Date Posted: 13 Nov 2009 at 8:51am
| No, I didn't place the running totals below the data it's calculating. I have running totals calculating Ded Code amounts by month and displaying it on the same detail row. .... it seems to be working ok I wanted to add the YTD total to sum all running totals and display it along with the month totals. |
Posted By: DBlank
Date Posted: 13 Nov 2009 at 9:07am
|
Sorry, I should have been clearer.
For a RT you can place it on or below the level of the calcualtion so your other ones work becasue they are displaying the data as you wish. However if you are now trying to show the data as an aggregate it won't work.
If you do not have any duplicte data then you really just want a SUM of all the rows correct?
SUM(field) and place it wherever you want on the report.
If you use INsert Summary, these are calculated before displaying and can be palced anywhere on the report (unlike a RT).
|
Posted By: nabilino
Date Posted: 13 Nov 2009 at 9:32am
|
So you're saying that I can't Sum the running totals already in the row calculating the amount of ded codes and display it in the same row?
If I want to display the total of the row it has to be below it?
I'm just concerned about the output in csv form and that it would be ugly for users to understand.
|
Posted By: DBlank
Date Posted: 13 Nov 2009 at 9:45am
|
For clarity sake you cannot SUM a RT at all (e.g. SUM(#RT) )
and you cannot add the values of a RT and display anywhere prior to all of the fields that are included in the calcualtion haev all ready been displayed in the report.
Example
Row 1=January
Row 2 = Feb
row 3 = March
etc.
A RT field or formula fieldin adding all of the months RTs togther cannot be displayed on each row because data it needs to include has not been read into the RTs yet.
Make sense?
|
Posted By: nabilino
Date Posted: 13 Nov 2009 at 9:53am
|
Ok, so there is no way of having the following in the report: Row1: {#Jan} {#Feb} {#March}...{#YTD}
Row2: {#Jan} {#Feb} {#March}...{#YTD}
What I have to do is:
Row1: {#Jan} {#Feb} {#March}...
GrpFooter: {#YTD}
Row2: {#Jan} {#Feb} {#March}...
GrpFooter: {#YTD}
Is that what you're saying? Thanks
|
Posted By: DBlank
Date Posted: 13 Nov 2009 at 10:04am
|
Lets get back to basics here. What data do you have and how do you want it to appear?
|
Posted By: nabilino
Date Posted: 19 Nov 2009 at 9:50am
| I have running totals summing up Deduction codes per employee for every month. Now, I want to display the YTD total somwhere to capture the ded code total YTD. |
Posted By: DBlank
Date Posted: 19 Nov 2009 at 11:15am
|
So instead of using RTs can you group on Employee then group on Datefield set to per month then use SUM(dedcode field,group2field) to get your month totals per worker
SUM(dedcode field,worker) to get your worker totals.
|
Posted By: nabilino
Date Posted: 19 Nov 2009 at 12:43pm
|
Good suggestion
I tried this formula:
sum({Command_1.DED-AMT},{Command_1.CHECK-DATE})
but I get there must be a group that matchs this field. Notice I created a group for both.
|
Posted By: DBlank
Date Posted: 19 Nov 2009 at 12:59pm
|
In your report design you will need to have your group1 set up on the employee then group 2 set up on Date set to per month.
Your SUM has to take into acount the per month portion :
sum({Command_1.DED-AMT},{Command_1.CHECK-DATE}, "monthly")
|
Posted By: nabilino
Date Posted: 19 Nov 2009 at 1:26pm
|
Well, I have to distinguish the Monthly total by DedCode as well. So that's why I think the running total would work better, that way I can set conditions to Sum the DED-AMT only when Month ({Command.CHECK-DATE})=4 for April, and Reset the total once the EMP group changes. The
sum({Command_1.DED-AMT},{Command_1.CHECK-DATE}, "monthly") formula will give me the monthly total for ALL DED-CODES
so I'm back to the drawing board. |
Posted By: DBlank
Date Posted: 19 Nov 2009 at 1:28pm
| just add another group (3rd one) on DEDCODE and do a sum there. |
Posted By: nabilino
Date Posted: 19 Nov 2009 at 1:48pm
|
Ok that right there: Sum ({Command.DED-AMT},{Command.DED-CODE}) gave me the YTD Totals for my Ded Codes Amounts.
|
Posted By: nabilino
Date Posted: 19 Nov 2009 at 2:53pm
|
Originally posted by nabilino
Ok that right there: Sum ({Command.DED-AMT},{Command.DED-CODE}) gave me the YTD Totals for my Ded Codes Amounts. Sorry I was wrong! This forumla calcuates one month's amounts only, not YTD.
|
Posted By: DBlank
Date Posted: 19 Nov 2009 at 3:01pm
|
Perhaps you grab and move group 3 up to group level 1 and this would addres ti but i am not sure... Leaving your existing report aside, can you post a little sample row level data from your source and then an example of what you want the report to look like with all of the calculations?
I am having trouble visualizing the overall process you want to get to...
|
Posted By: nabilino
Date Posted: 19 Nov 2009 at 3:33pm
|
DBlank- I found out somthing interesting..If I'm trying to sum 2 running totals that contain data using {RT1}+{RT2} it works, but if one or more are null, the Sum becomes null as well...wierd
What I would like to do is something like this:
DedCode Amt Jan Feb Mar ... YTD
PPO 50 100 150 50 ... 300
PPO1 10 10 20 10 ... 40
I have running totals calculating the monthly totals. This is how:
January Total
Field to Sum: Ded-Amt
Evaluate: Formula: Month ({Command.CHECK-DATE})=1
Reset: Group:DedCode
The part where I'm stuck is the YTD one.
|
Posted By: DBlank
Date Posted: 20 Nov 2009 at 7:04am
|
You are correct that any RT that is NULL will NULL out any formula that adds them together. A work around is to use multiple if thens.
(if isnull({#RT1}) then 0 else {#RT1}) + (if isnull({#RT2}) then 0 else {#RT2}) + (if isnull({#RT3}) then 0 else {#RT3}) + ...
Here are a couple of soultions for your issue (hopefully).
Group on DED Code, make your RTs as you have them and place them on DEDCODE group footer then do a insert sumamry as SUM(DED-AMOUNT,DEDCODE) and place it on the DEDCODE-GRoup Footer for your YTD amount. This is the amounts per Dedcode. (If you want all the Dedcodes at once then make a formual field as SUM(DEDcode).)
You could also make a Crosstab (this assumes you do not have more than 1 year of data, if you do you have to adjust for that).
Create a formula to get your Month names:
Monthname({Command.CHECK-DATE},abbreviate)
Add a Crosstab in the RH or RF
In the CT Expert Columns is the new Month Formula you just made
Rows is the DedCode field
Summarized field = Ded Amount
|
Posted By: nabilino
Date Posted: 20 Nov 2009 at 9:08am
|
Great, that solved the YTD problem.
Thank you.
Can't believe how such easy calculation in Excel could be tedious in Crystal.
|
Posted By: DBlank
Date Posted: 20 Nov 2009 at 9:12am
|
It is just a different way of looking at it and takes a little time to learn how crystal reads and interprets the data.
Once you get it it is not as difficult as it appears and worth the effort.
Glad you got it worked out
 |