| Author |
Message |
bwsanders
Senior Member
Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 177
|
  
Topic: SUM Question
Posted: 21 Oct 2013 at 4:38am |
|
I have a 401k export file that I'm working on and have to use variables to build the report and show it in the footer. What I'm running into is when I try to sum a numeric field I get "This field cannot be summarized". I'm not sure why since the formula is pretty basic.
If {checkDate} = {@CheckDate} AND {detCode} IN SHARED StringVar Array Earnings Then
{amount}
Does anyone have any work around or ideas on how to get this to work?
Thank you!
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 21 Oct 2013 at 5:02am |
|
you will have to sum the values yourself using variables.
At the time that the report is formatted, it does not know what is in the Shared Array...the array is only populated at report display time...which is after the aggregate function like SUM have been calculated.
So my work around would be to increment a variable for the amount at the same time you populating the array. If the only thing you are using the string array in the report is for the summarization, I would just increment the sum variable and not worry about the string array...
HTH
|
| IP Logged |
|
bwsanders
Senior Member
Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 177
|
Posted: 21 Oct 2013 at 5:18am |
|
Thank you, I have to use the array as it's the only way to truly access the information since it's stored in an odd way. I guess I'm a bit confused on how to use a variable to sum a variable. For instance the above formula is for the field "DeferredSalary" and I use the variable DefSal to build the amount for each record as the report is running. So I have it like this
Header: shared stringvar DefSal := "";
Details: shared stringvar DefSal := DefSal + {@DeferredSalary}
Footer: shared stringvar DefSal;
when I try to sum the field inside the formula like this:
If {checkDate} = {@CheckDate} AND {detCode} IN SHARED StringVar Array Earnings Then
Sum({amount}, {ssn})
the numbers come out waaaaaay to high. but if I look at what it's actually pulling it's pulling the correct numbers but seems to be summing the field for everyone instead of each employee.
when I try to sum the variable that is when I'm getting this field cannot be summarized. I'm just a bit stuck at the moment I guess.
I appreciate your help!
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 21 Oct 2013 at 5:27am |
|
I am assuming by the code that there is a grouping on ssn.
I've never used an if statement like yours, what I usually do is:
SHARED StringVar Array Earnings;
If {checkDate} = {@CheckDate} AND {detCode} IN Earnings Then
Sum({amount}, {ssn})
Try that...perhaps that will work...
Another idea, and this is just basic debugging for CR.
make a formula like:
SHARED StringVar Array Earnings;
{detCode} IN Earnings
this way you will 'see' if every string is in the array...at least according to CR.
Without knowing the data, it's a bit of sleuthing to figure out what CR is seeing vs how you THINK it is seeing the data.
HTH
|
| IP Logged |
|
bwsanders
Senior Member
Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 177
|
Posted: 21 Oct 2013 at 5:33am |
|
yeah i hear you. my array is actually in a sub report. i can see all the data is in the sub report and it all gets passed over it's just for some reason the sum isn't working. it's weird to me.
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 21 Oct 2013 at 5:39am |
|
obviously to check you could write the formula:
sum(amount,ssn)
if that amount is the same as the initial formula, then it would seem that ssn has an issue. If it working correctly, then just more confusion.
|
| IP Logged |
|
|
|
