Suppressing a non zero values

[Dirtless]

Good afternoon;

Assume we have three distinct records from a single individual - each record indicates a numeric estimate for a specific datum.

My report has a column for each of those three pieces of info. So I have the client identifier, followed by the three estimates, using a formula to display the estimates.

Client is one group, and there is another group (county) above the client. At the county level the estimate is the average for all the clients in that county. Easy so far? Almost.

My problem is that I do not want to average ALL the estimates. (Specifically, number 99 is used to indicate "no opinion", which I want to exclude from the calculation.)

First thing I would like to do would be to conditionally suppress the data in the detail line, if that data indicates 99. I know where to go to suppress a zero value. Can anyone help me suppress the datum if it is a different value?

Second thing I want to do is calculate the county average for all values that are not 99. That would go in the group header.

Suggestions? Thanks very much for your time.

D

 

[briangriffin]

Suppression - if you want to suppress the entire detail line if the field value is 99, go to the Section Expert and place this line into the X2 formula next to "Suppress (No Drilldown":

datafield = 99

If you want to display the detail record but just suppress that field, you would enter the same code in the X2 formula for the field itself: Right click on the field --> Format Field --> Common tab --> Suppress.


For the average issue, you would create a running total for the field, make the summary "Average", and in the middle section of the running total editor select "Use a Formula".

The code here is the reverse: datafield <> 99 - in other words, it will average all values that are not 99. Reset it at the appropriate group level, and you'll probably want to create a running total for each group.

 

[Dirtless]

That's great. Just one problem - The running total works just fine when placed in the group footer, but shows as zero (or null) when in the header.

Am I to assume this is the way running totals work? (Intuitively that makes sense.) Any way I can shift it to the header?

Thanks
D

 

[kray4660]

If you can write your running total as a formula, then you can put 'WhilePrintingRecords;' before the calcuation. I just did a quick test, and it worked for me (I did a distinct count by group).

 

[briangriffin]

I'm not sure that would work with a conditional running total, even using variables. But maybe...

The simplest way would be to exclude the records with a 99 value via the select expert. Then you could just insert an average summary field into the header.

If that's not an option for you, it may end up with a subreport placed in the group header and passing the average to the main report via a shared variable.

 

[IanWaterman]

AS Kray says you can do it using conditional formula, you will need two to do an average

@Total
If datafield <> 99 then valuefield else 0

@count
If datafield <> 99 then 1 else 0


you can sum both these in footer or or header

@avg
If sum(@count) = 0 then 0 else
sum(@total)/sum(@count)

Ian