Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Help - sum field of only visible records Post Reply Post New Topic
Author Message
ravenous
Newbie
Newbie


Joined: 24 Sep 2014
Location: United States
Online Status: Offline
Posts: 7
Quote ravenous Replybullet Topic: Help - sum field of only visible records
    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

Edited by ravenous - 23 Feb 2015 at 3:50am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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
IP IP Logged
ravenous
Newbie
Newbie


Joined: 24 Sep 2014
Location: United States
Online Status: Offline
Posts: 7
Quote ravenous Replybullet 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!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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"
IP IP Logged
csmith.lpi
Newbie
Newbie


Joined: 16 Jul 2018
Location: United States
Online Status: Offline
Posts: 3
Quote csmith.lpi Replybullet 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?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.

Edited by DBlank - 07 Dec 2018 at 2:38am
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.