Solved

how to summarize a summary

Posted on 2004-09-02
13
351 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
  • 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

828 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