Conditional Portlet Aggregation Row

[Legacy User]

Hey fora,
Long time reader, first time poster :D
I've made a portlet that grabs some timesheet details to support reporting for a new initiative we're rolling out, and I'm having problems getting an aggregate row to work. I'm trying to find a way to have the aggregate row only count actual hrs if the row has a specific input type code. The options for aggregate rows look pretty sparse, and a quick search of threads revealed some similar problems, but not exactly what I'm looking for.
So, is there a way to configure a portlet's aggregate rows to sum only certain rows, based on other values? Sort of like a countif excel formula?
Thanks!

[Anon Anon]

Can't think of a simple way in Clarity to do that (at the portlet level).

1. I think I'd just code it in the NSQL that drove my portlet though, SQL "Group By" statements should deliver various "subtotals", or at worst you could code the aggregation as a UNION to you main data selection.

The drawback of coding "formatted" logic in the SQL is that you then have to be somewhat careful about the ordering of your data (since you don't really want users to re-order the portlet such that the "total" row appears in the middle of the main data :sad


OR

2. You could always code TWO portlets on the page and have a page-filter portlet (i.e. a single set of filter criteria that gets passed to both portlets) - one portlet delivers the main data, one portlet delivers the aggregate(s) - that would solve the re-ordering dilemma I indicate above. cool)

[Legacy User]

Good call on the two portlets - people here tend to get snippy if they have to run a portlet twice. Thanks for the advice!

[Deactivated User]

The NSQL in portlets brings in the basic SQL functionality and there is not conditional aggregation in SQL.
If you want actual hrs if the row has a specific input type code you could put that in a separate column and then there is no problem in aggregation.
Then the challenge is to hide that column and just to display the agregate value.

Martti K.