The ability to show text in a crosstab is highly requested feature. Various work-arounds have been found, such as using layout expressions like: case when [Flag] = 1 then ‘Yes’ else ‘No’ end. This works, but doesn’t allow for truly dynamic crosstabs.
Consider the following requirement. The user wants a crosstab that shows Region, Retailer Type, and a list of Company Names in the intersections. He wants to be able to filter by Product Line to limit the companies that are shown.
In the above example, I’m using DB2. DB2 has the function listagg, which aggregates text with a specified delimitter. Oracle has something similar, group_concat. SQL Server has some UDFs that can do the same thing, such as found here.
This can now be accomplished as of 10.2.1 Fix Pack 3. One of the new features of this fix pack is a way of using non-standard aggregation functions. By prepending “aggregate:” before the function, we are instructing Cognos to trust us that this is an aggragative function. It won’t try to put it in the group by. This makes it incredibly easy to get this effect, the expression in it’s entirety is aggregate:listagg([Sales (query)].[Retailers].[Company name],’, ‘)
aggregating-text.txt (2079 downloads)
Hi Paul,
I have a report where i need to display user comments as crosstab measure. i tried using aggregate([Comments] but, my report just displays a blank crosstab intersection instead of comments.
First, which version of Cognos (including fixpacks) are you using? This feature only exists as of 10.2.1.3. What’s your database? It needs to have some sort of function to aggregate the text. Oracle has group_concat, Server has several UDFs to do it. Finally, it should be aggregate:([Comments])
The aggregate flag is the new feature that allows us to do this.
hi, i am using 10.2.1 FP3 with DB2 10.5. i am accessing a straight relational table and am not using dqm. i believe i am following your syntax except substituting the item from my package / database. i get the following error – Parsing text: aggregate:listagg([CrossTabText].[Survey Comment Table].[Survey Comment],’, ‘)QE-DEF-0260 Parsing error before or near position: 11 of: “aggregate:”. i can run a sql query outside of the application using the listagg function without error. am i missing a prerequisite step or have i botched the syntax?
Hi Is the report spec attached for 10.2.2?
I installed 10.2.2 FP6 and it still give me an error.
And will this only work for Relational data sources. What about TM1 10.1
Unfortunately this will only work in a relational data source that has a text grouping function. What error are you getting? I tested it in an unpatched 10.2.2 and it works perfectly.
Can we use the Listagg Funciton In FM & how ? Any suggestion???
I’m not sure if it’s possible to put it in. Try using a standalone calculation with the function in place. No promises on it’s working.
I tried this using the samples (and sample report spec) – with 10.2.2 (unpatched) and Oracle and I get this error: QE-DEF-0260 Parsing error before or near position: 11 of: “aggregate:”
QE-DEF-0261 QFWP – Parsing text: aggregate:listagg([Sales (query)].[Retailers].[Company name],’, ‘). Ideas? Would love to get this to work..
Unfortunately this is only available after you install a specific fix pack, and even then I think it’s only available in DQM.
Have you by any chance had the chance to find out if this works in non-DQM based crosstabs? I’ve been handed a requirement to do almost this exact thing, and although I don’t need the dynamic data pull, I do need the aggregation…we’re not on DQM yet (heck, we just got to C10 this month…), and I’m not sure we’re ready to make that leap…but if this is the hill I have to make the stand on, I will.
I don’t think it’s possible in a CQM environment. It entirely relies on the “aggregate:” clause. I can’t think of any reason IBM can’t extend this to CQM. Put it in as an enhancement request.
In my case, Oracle is my source db. I couldn’t get aggregate:listagg to work in DQM as it gave an ORA-02000 error.
I didn’t actually need to aggregate text items together; but, i did need to surface the text to the crosstab intersection. This did work for me:
aggregate:max(data item)
Finally!
Hi CognosPaul,thanks for sharing your ideas and experience with these very useful cognos tips .i’m trying to use the ‘aggregate:’ directive with row number windows function on netezza but i have an error during the launch of report.im using cognos 11.08.
I’ve used also brackets around rownumber function but it doesnt work.
My package is relational with dqm enabled.
Could you suggest a way or workaround to use analytical function such row number directly using expression on report ?thx.
I think this might be a limitation of DQM – it tries to intelligently rewrite the query at runtime. You might be able to replace rownum with running-total(1 for report).