Summary Filter for a group of rows in a List

[bbassler]

I need to implement a filter in a report for displaying groups (as in probably based on a List grid's field grouping capability) of list rows only where all the rows in that group have a non null value for a EvaluationDate field.

So I for a prompt I have radio button with two items in a prompt page and and a parameter, say X to denote which option is selected hook into the radio button.

RadioItem1 - include/display only groups where all the rows in that list group have a non null value for a EvaluationDate field.

RadioItem2 - include/display all groups (regardless of a non null or null value for a EvaluationDate field).

So I need a filter of some kind. Basically, I need the equivalent of a conditional SQL group by/having clause. From what I can tell from what I can dig up I might be able to use a summay filter with the appropriate expression to do this. I find close zero practical info on this subject.

Can someone provide generally some ideas, expressions etc as to how this might be done; either using a Summary Filter or some other means.

[eliza_jane]

You could probably create a data item that has a case statement similar to case when Evaluation field is not null then 1 else 0 end and have aggregate function set to total.

Create a filter on this data item say data item 1 > 0 and set it to after auto-aggregation.

[MFGF]

You could probably create a data item that has a case statement similar to case when Evaluation field is not null then 1 else 0 end and have aggregate function set to total.

Create a filter on this data item say data item 1 > 0 and set it to after auto-aggregation.

Very neat solution! :)  Definitely worth an Applaud!

[eliza_jane]

Thanks  :)

[bbassler]

I could not get this to work. It appeared to have something to do with the timing of evaluations and the filtering of the rows per group or ... I was missing something. One would think post aggregation would make this technique work but ... anyway a summary filter does work for this purpose. The problem with the summary filter is that you can't reference a prompt parameter as it will cause a error. The is a terse cryptic reference to this problem in the IBM manual online. There are two work-arounds listed. Also pretty cryptic. I just placed the filter switch parameter with the query's row counter to get around this. http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.ug_cr_rptstd.8.4.0.doc/ug_cr_rptstd_id8542cr_rptstd_wrkdat_filter_data_rel.html Using Prompt Expressions in Filters