Is there a way to create an array of values for a given field in Cognos Report Studio?
Say one expense has 5 attendees. If I bring in fields expense & attendees, this will create 5 rows since the granularity is forced down to the attendee level rather the expense level.
Can I force this back into one row where the attendee column is a comma separated value or array of some sorts?
Extract:
+---------+----------+-----------+------------+
| Expense | Employee | Entry Key | Attendee |
+---------+----------+-----------+------------+
| Meal | Iron Man | 1 | Thor |
+---------+----------+-----------+------------+
| Meal | Iron Man | 1 | Hulk |
+---------+----------+-----------+------------+
| Meal | Iron Man | 1 | Spider Man |
+---------+----------+-----------+------------+
| Meal | Iron Man | 1 | Star Lord |
+---------+----------+-----------+------------+
| Meal | Iron Man | 1 | Thanos |
+---------+----------+-----------+------------+
Desired Extract:
+---------+----------+-----------+-------------------------------------------+
| Expense | Employee | Entry Key | Attendee |
+---------+----------+-----------+-------------------------------------------+
| Meal | Iron Man | 1 | Thor, Hulk, Spider Man, Star Lord, Thanos |
+---------+----------+-----------+-------------------------------------------+
limited to calculated fields/equations Assuming this will take a subquery and then joining back to a full query using entry key. Not sure if this is possible in calculated fields
Use a repeater table simple query structure. This will create a comma for each value (before or after) and make it impossible to omit the comma either before the first value or after the last value.
Here's a sample report:
You didn't say what version of Cognos. This is for 11.0.13. You may need to update the version number and style version in the first line to match your environment.