Suppress data

[busi1]

Families Programs county
110012719 88102 214
110012719 88102 214
110012719 88304 214
110012719 88304 214
110012720 88304 214
110012720 88304 214



I need the distinct counts of families for programs for every county. But I want distinct count of families for the highest program ( in the above data family 110012719 exist for programs
88102 & 88304,but I want the distinct count of family for program 88304 and the other program 88102 should not be display in the report)

I did grouping on the county and created a running total for distinct count (In the running total, I am doing distinct
count of Families and evaluating on change of field program and resetting it on change of group county) and placed programs and running total in the group footer.

How do I suppress the program 88102 (I don't want it to be displayed in the report at all).

I am using Crystal reports XI and Oracle 10g

Help is appreciated....

 

[lbass]

This simplest approach would be to use group selection instead of suppression, as your running totals would then automatically pick up only the displayed values. First insert a group #2 on family, and then go to report->selection formula->GROUP and enter:

{table.program} = maximum({table.program},{table.family})

-LB

 

[busi1]

Thanks for the reply lbass, it worked fine. But what if the families are associated with more than 2-3 programs and I need the count of families in this program order 1) 88304 2) 87801 3) 88302
4) 88102 (The report has to determine the priority order, say if the family is associated with the above 4 programs then it has to look at the above order and bring the counts for the program 88304 or if the families is associated with programs- 87801,88102 and the report should display counts for program 87801 based on the above order)

I might have framed the question wrongly in my earlier post, that's my bad.

Thanks

 

[lbass]

Are you saying you still want the highest priority program only but that the program numbers for the the highest priority aren't necessarily the highest program number? If so, create a formula {@priority} like this:

select {table.program}
case 88304 : 1
case 87801 : 2
case 88302 : 3
case 88102 : 4
default : 999999

Then change your group selection formula to:

{@priority} = minimum({@priority}, {table.family})

-LB

 

[busi1]

Hi lbass,

Thanks for the above solution, priority worked except it's not pulling the right count of families for the programs. Below is the running total I created for the family counts and placed it in the program group footer.

Running total for count of families:
1) distinct count of families
2) evaluate on change of program
3) reset on change of county group

I tried all possible combinations but couldn't figure out the solution.

Below is the sample data I am using for the report.

Families Programs county
110012719 88102 214
110012719 88102 214
110012719 88304 214
110012719 88304 214
110012720 88304 214
110012720 88304 214
110012721 88102 214
110012718 88304 214
110012718 88302 214

I want the report to display the below.

Program Count of Families
88304 3
88102 1

but the report is displaying the below:

Program Count of Families
88304 3
88102 0
88302 1

similar to count of families, I need count of children (IND_ID), sum of expenditure (exp_amt) in the same priority order as per my earlier post. I created running totals for all these and not getting the expected results, not sure if i need to add any of these in the group selection.

Thanks again for you help....

 

[lbass]

Did you insert a group on families? I think the rt should be set up to do a distinct count of program on change of group: family, reset on change of county.

Also, what does your data look like when you include the children and expenditures? Are you still looking for the results by highest priority program PER FAMILY?

-LB