terryhdbailey
asked on
how to summarize a summary
I have some summary fields that generate some summaries of various rate_codes.
There is a bunch of rate codes for example maybe like:
SI1 which has a bill amount of $33.00
SI2 which has a bill amount of $44.00
WI1 which has a bill amount of $12
WI2 which has a bill amount of $35
Bod1 which has a bill amount of $44
Bod2 which has a bill amount of $23
Bod1 which has a bill amount of $65
All of these produce a summary for each rate code. So all the SI1's would be summed together and give me a count of SI1's and a $$ of all SI1's. Same for SI2, Bod1, etc. So then I get example of below:
Rate Code Count Bill Amount
SI1 46 $1790.15
SI2 2 $55.41
SI3 39 $721.37
SI4 4 $113.42
WI1 2 $58.55
WI2 37 $322.92
What I need now is something that will give me a count and total of all the rate codes that begin with an S or begin with a W or begin with B's. How do I do that?
thanks terry
There is a bunch of rate codes for example maybe like:
SI1 which has a bill amount of $33.00
SI2 which has a bill amount of $44.00
WI1 which has a bill amount of $12
WI2 which has a bill amount of $35
Bod1 which has a bill amount of $44
Bod2 which has a bill amount of $23
Bod1 which has a bill amount of $65
All of these produce a summary for each rate code. So all the SI1's would be summed together and give me a count of SI1's and a $$ of all SI1's. Same for SI2, Bod1, etc. So then I get example of below:
Rate Code Count Bill Amount
SI1 46 $1790.15
SI2 2 $55.41
SI3 39 $721.37
SI4 4 $113.42
WI1 2 $58.55
WI2 37 $322.92
What I need now is something that will give me a count and total of all the rate codes that begin with an S or begin with a W or begin with B's. How do I do that?
thanks terry
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
By the way, mahabat's suggestion can work, he just didn't give you the whole picture. The idea with that is to create additional columns with formulas.
Create a formula in your details section:
If Left({table.RateCode),1) = 'S' then 1 else 0; <-- Note this is the correct syntax, mahabat was backwards.
Now you'll have a column full of 1's and 0's. You can insert a SUM on this column and that'll give you the count of all S's. For the values instead of "then 1 else 0" you would use "then {table.BillAmount} else 0". You can suppress these columns so they aren't visible on your report.
If it's just S & W that you want then this method will work fine also. When you get into lots of different values to summarize then your report designer gets very, very cluttered so using a formula may be a better bet. Either will give you the same results though.
frodoman
Create a formula in your details section:
If Left({table.RateCode),1) = 'S' then 1 else 0; <-- Note this is the correct syntax, mahabat was backwards.
Now you'll have a column full of 1's and 0's. You can insert a SUM on this column and that'll give you the count of all S's. For the values instead of "then 1 else 0" you would use "then {table.BillAmount} else 0". You can suppress these columns so they aren't visible on your report.
If it's just S & W that you want then this method will work fine also. When you get into lots of different values to summarize then your report designer gets very, very cluttered so using a formula may be a better bet. Either will give you the same results though.
frodoman
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried frodoman answer but get error on formula. I believe it needed a else if instead of if?
Formula was/is now
// @IncrementVars
if Left({finalbilling.rate_co
(
shared numberVar SCount := SCount + 1;
shared numberVar SValue := SValue + {finalbilling.billamount}
)
else if Left({finalbilling.Rate_Co
(
shared numberVar WCount := WCount + 1;
shared numberVar WValue := WValue + {finalbilling.BillAmount}
)
Question is if all above counters are in original report but the display is on the subreport. Should I just count somehow in subreport or does the variables from the main report get passed to subreport.
thanks terry
Formula was/is now
// @IncrementVars
if Left({finalbilling.rate_co
(
shared numberVar SCount := SCount + 1;
shared numberVar SValue := SValue + {finalbilling.billamount}
)
else if Left({finalbilling.Rate_Co
(
shared numberVar WCount := WCount + 1;
shared numberVar WValue := WValue + {finalbilling.BillAmount}
)
Question is if all above counters are in original report but the display is on the subreport. Should I just count somehow in subreport or does the variables from the main report get passed to subreport.
thanks terry
Terry,
Variables with the "shared" keyword are available to subreports automatically. The only catch is that you must use them in your main report before the subreport is executed (which should already be the case). Within the subreport you can create this formula for example, to write the SCount value:
shared numberVar SCount;
Also, the else..if doesn't hurt anything but it doesn't help either. There's no case where the first character can be *both* S and W so whether you use "else if" or "if" in your second clause doesn't matter.
Hope that helps.
frodoman
Variables with the "shared" keyword are available to subreports automatically. The only catch is that you must use them in your main report before the subreport is executed (which should already be the case). Within the subreport you can create this formula for example, to write the SCount value:
shared numberVar SCount;
Also, the else..if doesn't hurt anything but it doesn't help either. There's no case where the first character can be *both* S and W so whether you use "else if" or "if" in your second clause doesn't matter.
Hope that helps.
frodoman
ASKER
without the else and just the second if I get errors. So that is why I added the else if.
let me try ...
ok I am lost. sorry.
How exactly do I display this Scount? I have tried various items and can;'t make work.
thanks terry
let me try ...
ok I am lost. sorry.
How exactly do I display this Scount? I have tried various items and can;'t make work.
thanks terry
Create a formula containing the exact text below:
shared numberVar SCount;
Place this formula anywhere you want to see the value of scount.
I suspect the error was just a missing semicolon but it's no big deal - what you have works fine.
frodoman
shared numberVar SCount;
Place this formula anywhere you want to see the value of scount.
I suspect the error was just a missing semicolon but it's no big deal - what you have works fine.
frodoman
ASKER
Yes was the semicolon.
These totals may or not have anything. If I did not want them to show up at all if they were zero how do I do that? Sometime I may not have one of the waters/lights/sewer/etc and want to just not have them in there. It seems that in formula / common/ suppress you create some kind of formula for your formulas?
right now I have with frodoman's formulas to following
Total Waters (text field) formula of #totalwaters formula of sum of total amount waters
Total Sewers (text field) formula of #totalsewers formula of sum of total sewers
Total lights (text field) formula of #totallights formula of sum of total lights.
These totals may or not have anything. If I did not want them to show up at all if they were zero how do I do that? Sometime I may not have one of the waters/lights/sewer/etc and want to just not have them in there. It seems that in formula / common/ suppress you create some kind of formula for your formulas?
right now I have with frodoman's formulas to following
Total Waters (text field) formula of #totalwaters formula of sum of total amount waters
Total Sewers (text field) formula of #totalsewers formula of sum of total sewers
Total lights (text field) formula of #totallights formula of sum of total lights.
Couple ways.
Formula - Common - Suppress: Click the conditional formula button (X+2) to the right of suppress and enter this formula:
(CurrentFieldValue = 0)
So if the above is 'true', the formula will be suppressed.
Another way is to change the display formula from this:
shared numberVar SCount;
To this:
if shared numberVar SCount <> 0 then
shared numberVar SCount;
This will only show the value of SCount if it's non-zero. Otherwise it won't show anything.
Either method should work - whichever makes more sense to you.
frodoman
Formula - Common - Suppress: Click the conditional formula button (X+2) to the right of suppress and enter this formula:
(CurrentFieldValue = 0)
So if the above is 'true', the formula will be suppressed.
Another way is to change the display formula from this:
shared numberVar SCount;
To this:
if shared numberVar SCount <> 0 then
shared numberVar SCount;
This will only show the value of SCount if it's non-zero. Otherwise it won't show anything.
Either method should work - whichever makes more sense to you.
frodoman
ASKER
I am increasing points here because.
Frodoman done a VERY excellent job of pointing me in right direction. Very consise and helpful and my thanks is in your hands. But as I am new to this forum and think I can split points when I award just because the comment from rhinok made me remember something that was very important.
thanks to both
Frodoman done a VERY excellent job of pointing me in right direction. Very consise and helpful and my thanks is in your hands. But as I am new to this forum and think I can split points when I award just because the comment from rhinok made me remember something that was very important.
thanks to both
The split points is good - often there are different perspectives and the "correct" answer is many times a collaborative effort.
Glad we could help.
frodoman
Glad we could help.
frodoman
Frodoman's absolutely correct. Thank you and I'm glad we could help:-)
Left (1,{SI1} ) function to get first charactor of the sting and then you can use if conditions to summary or any other answer
if Left (1,{SI1} ) = "S" or Left (1,{SI1} ) ="W" then
etc
got it ?