Can I have a running aggregate of a string attribute in IBM Cognos Report Studio?
Hello,
I am creating a query in IBM Cognos Report Studio 10.1.1 and I would like to know if I can somehow do a running string aggregation which, in this case, is based on an RQM test case multiselect category value. Let's say I have data extracted in the following way:
TC NAME | CATEGORY TERM | CATEGORY VALUE
TC001 | COUNTRY | ITALY
TC001 | COUNTRY | SPAIN
TC001 | COUNTRY | GREECE
TC002 | COUNTRY | ITALY
TC003 | COUNTRY | SPAIN
TC003 | COUNTRY | GREECE
What I'd like to know is how to add a calculated data item to my query that aggregates the values together, so:
TC NAME | CATEGORY TERM | CATEGORY VALUE | AGGREGATE
TC001 | COUNTRY | ITALY | ITALY, SPAIN, GREECE
TC001 | COUNTRY | SPAIN | ITALY, SPAIN, GREECE
TC001 | COUNTRY | GREECE | ITALY, SPAIN, GREECE
TC002 | COUNTRY | ITALY | ITALY
TC003 | COUNTRY | SPAIN | SPAIN, GREECE
TC003 | COUNTRY | GREECE | SPAIN, GREECE
This can be done with integers with a running sum. How can I do it with a type of running string aggregate?
Thank you and best regards,
Andrew
One answer
Hi Andrew,
I'm not aware of an aggregation function that would allow you to do that concatenation. The closest thing I can think of is to use grouping, the category values will be listed per TC NAME and CATEGORY TERM.
Why exactly do you want to repeat for each detail the VALUES of the other details within the same category? Depending on the final result you are looking for, there may be a different approach that you can take.
- Clara