Print Page | Close Window

Help - sum field of only visible records

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21345
Printed Date: 28 Apr 2024 at 11:47am


Topic: Help - sum field of only visible records
Posted By: ravenous
Subject: Help - sum field of only visible records
Date Posted: 23 Feb 2015 at 3:48am
Hey all,
I have a report with a formula field called ({Made it}) and I am having difficult time summing only the visible records of that field.

The report is setup to show only distinct records by using {table.record_ID <> previous({table.record_ID}) to suppress the duplicates. The report is grouped by state.

The formula field ({Made it}) looks like:

If {table.SLA} > 2880 then '1' else '0'

My hope is that I can get the average of how many are > than 2880 for each group. Everything seems to work okay math wise but when I double check the results it appears the field ({Made it}) is summing all records not just the unsuppressed records. How can I make a field that sums only the visible records and ignore the suppressed?

Is there something similar to this available?
DistinctSum({Made it},{@assigned_state})

Please forgive me if this sounds convoluted but it's only my second time posting.

Thanks


Replies:
Posted By: DBlank
Date Posted: 23 Feb 2015 at 4:35am
Use shared variable formula or running totals to achieve this.
try this
in the Field Explorer
right click on Running Total Fields
select New
Name it (e.g. "Avg 2880 at group")
field to summarize=table.SLA
type of summary = average
evaluate=use a formula
{table.record_ID <> previous({table.record_ID}) and {table.SLA} > 2880
reset= on change of group-select @assigend state
place this RT field in group footer
RTs do not work in headers

Posted By: ravenous
Date Posted: 23 Feb 2015 at 5:49am
Hey, that worked great. Thanks. Now I gotta figure out how to get the running total into a bar graph!

Posted By: DBlank
Date Posted: 23 Feb 2015 at 6:17am
1. make sure you have your evaluate formula set to use defualt values for nulls or it will exclude your first row. Note you can place the RT on the detail section to watch how it is evaluating each row of data.
2. for you bar chart make your chart mimic the repoprt set up with tghe same grouping
insert the bar chart in the report header (or footer)
select the @assigned state as your group field
use the RT as your show value field - it will automatically be set to "don't summarize"

Posted By: csmith.lpi
Date Posted: 06 Dec 2018 at 11:24am
Forgive me for resurrecting an old thread. I'm having a very similar issue. My results are filtered through variables set by a front-end app, though. I can't get my totals to reflect only the printed records. the running total field appears to be summing every record that passes through the record selection formula, but it's not affected by the front-end application filters, it seems. Where do I look first to narrow this down?

Posted By: DBlank
Date Posted: 07 Dec 2018 at 2:38am
If you are using a running total you need to add a formula in the select criteria portion that matches your 'show' criteria.


Print Page | Close Window