?
Solved

Summing Some

Posted on 2011-05-13
19
Medium Priority
?
272 Views
Last Modified: 2012-05-11
How do I Sum some records?

I have a report which shows data somthing like this:
Customer  Job_No  Vendor  Vendor_Cost  Sale_Amt
12345678  123456  ABCDEF  10.50        123.45
12345678  123456  UVWXYZ  20.92        123.45
87654321  123456  LMNOPQ  12.45        645.23

Open in new window

This happens because of a join in the query.  There was no problem, until we started adding more than one vendor.  

The issue is that there is a summary of Sale_Amt.  When we had only a single vendor, the sum was correct.  Now Sum is adding the Sale_Amt even if it's a duplicate (same customer and job_no).

At one point I also had an issue wioth counting jobs.  I was doing "Count({view1.Job_No})", but was getting three (from the example above).  I learned that I could do DistinctCount() which solved that problem.  Now I need something akin to a DistinctSum().

Currently I suppress the Sale_Amt if it's a duplicate.  Is there anyway to Sum only on non-suppressed fields?

Finally, our reports have the GT values in the report headers so that the customer can see right up front the important values.  I tried a global variable, but it does not allow me to put the result in the report header (well, it allows me to put it there, but it shows 0.00).

How do I fix this?

TIA
0
Comment
Question by:Clif
  • 7
  • 5
  • 4
  • +1
19 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 35754962
You can create the correct grand total by creating a formual field that holds a zero or a value depending on a match/ simatch with the previous record.

Whileprintingrecords;
If onfirstrecord or ({table.Customer}=previous({table.customer})  and  ({table.Job_no}=previous({table.Job_no})  then
{table.sale_amt}
else
0

Then use Insert Summary to get the total.
However I don't see how you can get a summary of this value into the report header because the calculation can only be done at print time.

To solve that I think you would have to use a subreport which calculated the same total  (and displayed only that total) and place that  where you wanted the total to appear.
0
 
LVL 19

Expert Comment

by:GJParker
ID: 35755030
If you must have the Grand totals in the report header then one way to do this would be to create a subreport in the reportheader section to calculate and display the summaries.

Simply duplicate your main report and use running totals to calculate the summaries by setting the following

Field to summarise -> {view1.Sale_Amt}
Evaluate -> On change of field {view1.Customer}
Reset -> On change of field {view1.Customer}
N.B. If you have a group on customer then use on change of group 'Customer' for evaluate and reset instead

Place the running total in the Report footer section in the subreport

If you have any parameters on the report then these can be linekd to the main report parameters using 'Change Subreport links'

You could also use the running total on the main report instead of a subreport but this would only allow the totals to be displayed in the report footer

HTH
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35755056
Peter's formula can't be summarized since it is a printtime formula.

It would be better to figure why you are getting the duplicate records and try to solve that issue.
I had a similar issue and found I needed to join the tables on 2 fields thus eliminating the duplicate records.

How are the tables joined?

mlmcc
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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 10

Author Comment

by:Clif
ID: 35755136
It's not so much the problem of the duyplicate records as it is the duplicate values.  The records are correct, taken individually.

There is a job number 123456 for customer 12345678 that had a sale amount of $123.45 where we used a vendor ABCDEF who charged us 10.50 for his services.
There is a job number 123456 for customer 12345678 that had a sale amount of $123.45 where we used a vendor UVWXYZ who charged us 20.92 for his services.
Etc.

It becomes an issue when these are put on the report and, since there is a seperate line for the same job for each vendor, but the same sale price for the job itself, it's summing both.
0
 
LVL 19

Expert Comment

by:GJParker
ID: 35755211
If you only want the customer totals then you can create the subreport without the vendor information and use Sum(Sales_Amt), no need for running totals
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35755596
They really aren't duplicate records but duplicate total cost for that sale.  Can the total cost be calculated from the individual vendor amounts?

mlmcc
0
 
LVL 10

Author Comment

by:Clif
ID: 35755688
No, the individual vendor's and their costs need to be displayed.

I understand that I could use a subreport.  However, the subreport will still have the duplicate sales amounts.  Yes, I could rewrite a seperate query just for the subreport that leaves out the vendor, but I'd rather not have the added resources that I have to track and grant/restrict permissions.  This would be the very last option.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35755796
I explained how you have to calculate the total.

That is the way you would have to calculate it in the subreport as well.

I take it you understand clearly that the need for the subreport is entirely due to you wanting the total in the header rather than the footer. There would be no need for a subreport if the total was in the footer.

But it has to done in a subreport - there is no other solution within CR.
0
 
LVL 10

Author Comment

by:Clif
ID: 35755947
peter57r,
Your explaination won't work as you describe (no matter where I want to put the result).  As mlmcc pointed out, you can't Sum a calculated field.  Crystal won't allow it.  Clicking the Summary (Sigma) button in the toolbar won't list the formula.  Trying to type in Sum({@NonDupAmounts}) in another formula will throw an error.

It was my first thought.  When it failed, I came here.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35756106
Even in the subreport you will have to use running totals or manually sum them

You can have the subreport in the report header of the main report.  DIsplay the totals in the subreport report footer and they will show as you want them.

mlmcc
0
 
LVL 10

Author Comment

by:Clif
ID: 35756225
Well, in the subreport (presuming I create a new query removing the Vendor info) I could at that point just use a Grand Total SUM.  But the issue with a subreport is still the fact that I would need a separate query.
0
 
LVL 19

Expert Comment

by:GJParker
ID: 35756293
if you want the summary in the header then thats the
pay off

one other way might be using sql expression as a subquery.


(select sum( sales_amt)
from view1 a
where a.customer = view1.customer)


this will depend on your db and CR version
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35756346
Sorry - yes - well it's Friday...

But the principle of needing a subreport still stands, if you are to get this total in CR.

You can easily modify the code I posted to create your own totals but it would effectively be the same as using a running total.

Whileprintingrecords;
currencyvar mysum;

If onfirstrecord or ({table.Customer}=previous({table.customer})  and  ({table.Job_no}=previous({table.Job_no})  then
mysum:= mysum+ {table.sale_amt}
else
//do nothing;
""
You would use another formula to print the total.
0
 
LVL 10

Author Comment

by:Clif
ID: 35756499
I've tried
1. Using Sum. - Failed, as explained.
2. Global Variables - Will produce the correct total, but only in the report footer.
3. Running Totals - As above, will produce the correct total, but only in the report footer.
4. Subreport - Currently developing, but a pain.  I'd like a better option.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35756766
The subreport only needs to use a query like

SELECT DISTINCT CustomerNumber, JobNumber, SalesAmt
FROM YourTable

The only thing required in the subreport would be the summary of the field.

mlmcc
0
 
LVL 19

Expert Comment

by:GJParker
ID: 35756830
if you create the subreport using the same view then there shouldn't be any
issues with multiple queries.

did you try the sql expression.
0
 
LVL 10

Author Comment

by:Clif
ID: 35757011
I tried (am trying) using the existing query but leaving off the vendor stuff.  It doesn't seem to be working.  I'm sure it's an issue with the subreport links, but not sure what the deal is.  

Some part of the problem may be that I'm aiming at a moving target.  The data is live and is constantly changing.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35757075
How are you linking the subreport?

mlmcc
0
 
LVL 10

Author Comment

by:Clif
ID: 35757273
Using the exact same parameters that filter the main report.

It is working now.  The problem was, indeed, that the data was in a state of flux.  Most of the accounting department has left now, so the data has pretty much stabilized and I can compare the data.

I suppose I'll have to take the subreport.  Points are forthcoming to whoever suggested subreports fist.

Thanks and y'all have a good weekend.

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month16 days, 8 hours left to enroll

862 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