Hi Glen,
You can use
aggregate:string_agg([ns].[sales].[sales_order], ', ' )
I checked this in Report Studio using a Data Module that uses the Country table object.
Country expression: substring([C].[C_gosales].[COUNTRY].[COUNTRY],1,1)
IsoThreeLetterCode expression: aggregate:string_agg([C].[C_gosales].[COUNTRY].[ISOTHREELETTERCODE],';;')
The output looks like:
(You notice that S, references CHE, SWE, ESP Switzerland->CHE, Spain->ESP)
You can also create a stand-alone Calculation in a Data Module that has the same expression.
When you create it as a Calculated column of a table object, then the Grid view will not render, because of the way the Grid queries are constructed.
// Henk
------------------------------
HENK CAZEMIER
------------------------------
Original Message:
Sent: Wed August 12, 2020 06:32 AM
From: Glen Miller
Subject: STRINGAGG EQUIVALENT IN COGNOS QUERY
Hi,
I want to create a query which delivers the data for Sales Orders number for a particular Product on one row where the Sales Order numbers are comma separated.
E.g.
Product Sales Order Number
Product1 Ord1, Ord2, Ord3
Product3 Ord5, Ord7, Ord9
Product9 Ord11, Ord12, Ord13
In MS SQL Server I can use STRING_AGG function
How do I achieve this at query level in Cognos Analytics?
(I know I can use Repeaters in the report but these are only valid for displaying the results of the query - I want to perform a join on the query to pull in other data).
Any suggestions?
------------------------------
Glen Miller
------------------------------
#CognosAnalyticswithWatson