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.
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.
Quote from: eliza_jane on 08 Apr 2010 04:01:20 AM
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!
Thanks :)
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