Solved

how to summarize a summary

Posted on 2004-09-02
13
355 Views
Last Modified: 2006-11-17
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
0
Comment
Question by:terryhdbailey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 

Expert Comment

by:mahabat
ID: 11970433
hi you can use formula field and the different functions to solove this problem use
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 ?
0
 
LVL 42

Accepted Solution

by:
frodoman earned 250 total points
ID: 11971569
Terry,

I would use variables in this case to keep things from getting too cluttered.  Create a formula in your report header as follows:

// @InitVars
shared numberVar SCount := 0;
shared numberVar WCount := 0;
shared numberVar SValue := 0;
shared numberVar WValue := 0;

Now create this formula and put it in your details section:

// @IncrementVars
if Left({table.RateCode},1) = 'S' then
(
   shared numberVar SCount := SCount + 1;
   shared numberVar SValue := SValue + {table.BillAmount}
)
if Left({table.RateCode},1) = 'W' then
(
   shared numberVar WCount := WCount + 1;
   shared numberVar WValue := WValue + {table.BillAmount}
)

Now whereever you want to see the count of "S" just create this formula:

// @Show S Count
shared numberVar SCount;

Of course you use the same idea to see any of the other variables as well, and you can add as many variables and if..then cases to these formulas as needed (in case you need this on S, W, P, R, Q, ... ).

HTH

frodoman
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11971601
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
0
Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

 
LVL 26

Assisted Solution

by:Kurt Reinhardt
Kurt Reinhardt earned 50 total points
ID: 11978481
Based on your previous thread, you don't know which Rate Codes are going to be present.  This is why you went with a subreport that simply summarized the codes at a group level.  Both of the options above require that you know all the rate codes that might be returned.  They also require you to create a large number of formulas.

If all you want to do is summarize the Rate Codes by the first character, then you have a couple of options.

1)  Create a single formula as follows:

//@Rate Code Letter
Left({table.field},1

2)  Insert a Group based on the new formula field @Rate Code Letter - make this Group 1, Rate Code will be Group 2.  Your grouping will look like this:

3)  Insert summaries counts and totals in the Group Footer for @Rate Code Letter.  It should look something like this:

Ltr
     Rate Code                 Count                      Bill Amount
S
     SI1                                46                          $1790.15
     SI2                                2                            $55.41
     SI3                                 39                          $721.37
     SI4                                4                             $113.42

S Total                                91                         $2680.35

     WI1                              2                               $58.55
     WI2                               37                            $322.92

W Total                                39                           $381.47


The caveat here is that the formulaic group interrupts grouping that may have been passed to the DB.

To get around this, create a SQL Expression instead of a Formula.  The SQL Expression will always be processed on the DB, therefore, it won't interrupt any grouping that could be passed to the DB (Show SQL Query).

If you need more than the first character in some cases, you will need to modify the initial formula/expression, but it can be done.
0
 

Author Comment

by:terryhdbailey
ID: 11998972
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_code},1) = 'S' then
(
  shared numberVar SCount := SCount + 1;
   shared numberVar SValue := SValue + {finalbilling.billamount}
)

else if Left({finalbilling.Rate_Code},1) = 'W' then
(
 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
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11999018
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
0
 

Author Comment

by:terryhdbailey
ID: 11999268
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
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11999304
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
0
 

Author Comment

by:terryhdbailey
ID: 11999671
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.
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11999711
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
0
 

Author Comment

by:terryhdbailey
ID: 12003463
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
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12005677
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
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 12007008
Frodoman's absolutely correct.  Thank you and I'm glad we could help:-)
0

Featured Post

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question