Pie Chart - including 'Others' that need to be filtered or hidden

[crogers111]

CR XI
SQL

I'm having difficulty getting my pie chart to work as needed. I need to show a slice for Individuals whose subtotal meets a $500 threshold and then group the rest into a slice of 'others'

I also need to show the only invidviuals that meet the threshold and their subtotals in a Group section below the pie chart


EXAMPLE OF DATA

Name AmtPaid
Joe W. 200
Joe W. 400
Sue G. 300
Ted S. 800
Ben R. 100


- Grp1 is on Name and includes Subtotals of AmtPaid

- Need to show in a GH or GF only those Names where Sum(AmtPaid), Name >= 500


PIE GRAPH NEEDS SLICES FOR NAMES THAT MEET $500 THRESHOLD AND THEN GROUP THE REST AS 'OTHERS':
Thus chart should include these slices:

Joe W. 600
Ted S. 800
Others 400

GROUPED DATA SHOULD SHOW:

Joe W. 600
Ted S. 800


Attempt 1: Using a Group Record Selection for the $500 threshold doesn't appear to include the 'others' chart is in RH

Attempt 2: Suppress GP1 Section if Sum(AmtPaid), Name >= 500. Tried to use TopN within the chart but the formula I use to generate the 'N' in TopN appears to be a PrintTime formula and thus unavailable to use for charting. Chart in RH


Any ideas or input is appreciated

 

[GJParker]

Your problem is that you can't create a group on a summary formula. If you just wanted to display the date then there are ways around this, but these will not allow you to chart the results.

One option is to calculate the Amtpaid totals per person on the db and bring these into the report, this makes the report layout and chart very simple.

Another option may be to use a SQL expression to calculate the AmtPaid totals this will look something like below, this will depend on your db and version of CR as to whether this method is available)

(
select Sum(A.AmtPaid) 
from table A
where A.person_id = table.person_id
)

Gary Parker
MIS Data Analyst
Manchester, England