Cognos aggregation - listagg

[ry1633]

I could use a little help.   How does one do aggregation in Cognos?   I've never encountered this before so I'm not sure where to start.  Example,   I need to find out all the employees tied to a particular inspection ID.    This is the query I'd like to replicate.    What do I need to do or set in Cognos?   I have the Inspection ID and Employee ID as separate items within the same query subject in my model.

     SELECT insp.id,
            LISTAGG (hist.employee_id, ',') WITHIN GROUP (ORDER BY insp.id)
               AS emp_list
       FROM inspection_table insp, inspection_action_log_table hist
      WHERE insp.id = hist.inspection_id
   GROUP BY insp.id;

[ry1633]

I see this in the knowledge base, but I don't know how to write it in such a way as to reflect what I need.

http://www.ibm.com/support/knowledgecenter/SSWGNW_10.1.0/com.ibm.swg.im.cognos.ug_fm.10.1.0.doc/ug_fm_id22639summaries.html%23summaries

aggregate

Returns a calculated value using the appropriate aggregation function, based on the aggregation type of the expression. This function appears in the Budget vs. Actual sample report in the GO Data Warehouse (analysis) package.
Syntax

aggregate ( expression [ auto ] )
aggregate ( expression for [ all|any ] expression { , expression } )
aggregate ( expression for report )

[bdbits]

That is an interesting Oracle function I was not even aware existed.

I think the confusion is with the term "aggregation". In Cognos, indeed in most tools and database SQL I have worked with, the term means you are summarizing numeric data into a sum, average, etc. or counting it if non-numeric. This is more like concatenating a list of data items.

You can accomplish something like this using repeaters, as mentioned in http://www.cognoise.com/index.php?topic=23693.0.

They are also covered in the Report Studio user guide, I believe there is even an example or two.

[ry1633]

Are repeaters possible within Framework Manager -  can I set this up in a query subject and publish it into the package so the users will automatically see it that way?

[Lynn]

A repeater is a layout container within Report Studio and not applicable to Framework Manager. You could have a view created in the database that uses the listagg function and then try modelling over that view. I've never encountered that function and do not use Oracle at the moment so take that suggestion with more than a few grains of salt.

[ry1633]

Hate to bump an old thread.   But I'd still like to see if this is possible in Framework Manager

For the Oracle users out there.    If it is possible,  I'd like to be able to use the LISTAGG SQL within Cognos on a query subject calculation or similar.  I've been trying it in FM and cannot seem to get the syntax right.  It looks like that statement is possible in Cognos, but there isn't a lot of support or docs that show its usage.  Cognos tends to support Oracle SQL with some minor tweaks, but maybe this one is new enough that Cognos won't handle this?

https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030

[bdbits]

What Lynn suggested with a view should work in any case. That aside, have you tried native or pass-through SQL? Because that should work.

https://www.ibm.com/support/knowledgecenter/en/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_fm.10.2.2.doc/t_change_sql_type.html#change_sql_type

[ry1633]

I thought about trying Native SQL too.  But I can't quite figure out where to enable it.  If I right-click on my datasource to Create -> New Query Item - I don't see any place to enable native SQL.  I'm using Framework Manager 10.2.1

[bdbits]

It's kind of non-obvious. See the instructions at the bottom of this page.
https://www.ibm.com/support/knowledgecenter/SSRL5J_1.0.1/com.ibm.swg.ba.cognos.ug_fm.10.1.1.doc/t_ug_fm_nativesql_prerequisites.html

[ry1633]

wow - I wouldn't have thought to look there.  I did find it but the menu option to turn on native SQL is grayed-out for me.

[AnalyticsWithJay]

To send expressions to the database and skip the Cognos validator, you have to use curly braces {}. LISTAGG might be a challenging one to get the syntax right for.

The following expression works in Oracle SQL Developer:

Code Select Expand

LISTAGG(FISCAL_QUARTER_NUMBER, '; ') WITHIN GROUP(ORDER BY FISCAL_YEAR_NUMBER) OVER (PARTITION BY 1)

... so I tried to surround it in curly braces as such for you:

Code Select Expand


{LISTAGG(FISCAL_QUARTER_NUMBER, '; ') WITHIN GROUP(ORDER BY FISCAL_YEAR_NUMBER) OVER (PARTITION BY 1)}


Only problem is it complains about the column being an invalid identifier. If you have time, you could play around with the first statement and try to include the full path to the column. I suspect if you get that to work, you might be able to have a data item with a similar expression.

Listagg is not something I would recommend you introduce into the BI environment. If the users expect the data a certain way, you should have it as such in a table or at worst a view.

[ry1633]

I may have to leave this one alone - looks like too much headache to implement smoothly.   I was just looking for ways I could help take some of the burden off of my dba.

[CognosPaul]

listagg is definitely possible in Cognos, the problem that you run into is that it's technically aggregating text. So most database implementations disallow listAgg (or similar) from being in the group by.

Fortunately, as of 10.2.1 FP3, IBM introduced the aggregate keyword. This allows you to use non-standard aggregate functions. In your case you should be able to do:

Code Select Expand

aggregate:listagg([Fiscal_Quarter_Number],'; ')

I have an example here:
http://cognospaul.com/2014/10/20/quickie-aggregating-text-cognos-crosstab/

[the6campbells]

Some of the replies are incorrect.

The aggregate: directive does not support the ability to specify within group specification.

The aggregate() expression is for the query engine to attempt to determine an appropriate aggregation as might be required with an OLAP source.

[CognosPaul]

In general you are correct about the "aggregate:" directive, but you CAN cheat by wrapping the expression in braces to make it passthrough SQL.

Code Select Expand

aggregate:{sum(revenue) over (partition by storeid)}
I STRONGLY recommend against doing it, but it does make window functions possible.

The aggregate directive simply prevents Cognos from putting the expression into the group by clause.

[the6campbells]

When a BI-query is transformed into dynamic SQL, the engine has to ensure to generate an appropriate window specification and grouping specification for the query block and expressions.  It does that for the set of summary/windows functions it knows about. The aggregate syntax introduced in 10.2.2 allowed a user to annotate a non-built in function which indicated to the query engine that the expression is not a scalar function. While this causes it to form the appropriate constructs (see first statement) that does not include the WITHIN GROUP clause (per original posters question).

Handing coding an expression using {} or otherwise is not a solid solution. It 'may' work because of other constructs which cause a grouping specification etc. But there is no assurance that this will always occur.

In summary the aggregate keyword that introduces a function (i.e. aggregate:myaggregate) provides some ability but does not claim to provide you with all the ISO-SQL 2016 constructs for windowed aggregates/function syntax.