Solved

how to summarize a summary

Posted on 2004-09-02
13
340 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 26

Assisted Solution

by:Kurt Reinhardt
Kurt Reinhardt earned 50 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:terryhdbailey
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Frodoman's absolutely correct.  Thank you and I'm glad we could help:-)
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now