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

make a function in SQL server and call it in order to return per key only one record with the , between.

------------------------------
Stoyka Bojkova
------------------------------

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
------------------------------