Custom Rollup Function?

Cognos 8 Business Intelligence Metrics Designer
1

The hierarchy rollup requirement for one of our metrics is non-standard and I can’t figure out how to create a custom aggregate function. In our business, there are retail stores that roll up to a district. Some stores have a blank metric value; some stores have a 0 metric value; and some stores have a normal metric value (always something less than 100). The rollup metric at the district level is defined as the average of all scores greater than 0 - so I can’t use the normal AVG function. In Oracle I could code it as:

SUM(metricvalue) / SUM(CASE WHEN metricvalue>0 THEN 1 ELSE 0 END)

but in metric designer, it appears that you must select from a hard-coded list of rollup functions (AVG, LAST, MAX, MIN, etc).

Any ideas?!?!?
Thanks,

Daniel

2

If everything is bigger then 0 why dont u put a detailfilter on the whole query where value > 0
Then u can use avg again

Besides u can create your own total if u fill in the members in the total row as well