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?
+---------+----------+-----------+------------+
| 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 |
+---------+----------+-----------+------------+
+---------+----------+-----------+-------------------------------------------+
| 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.
<report xmlns="http://developer.cognos.com/schemas/report/14.3/" useStyleVersion="11.4" expressionLocale="en-us">
<drillBehavior/>
<layouts>
<layout>
<reportPages>
<page name="Page1">
<style>
<defaultStyles>
<defaultStyle refStyle="pg"/>
</defaultStyles>
</style>
<pageBody>
<style>
<defaultStyles>
<defaultStyle refStyle="pb"/>
</defaultStyles>
</style>
<contents>
<list horizontalPagination="true" name="List1" refQuery="Query1">
<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
<CSS value="padding:10px 18px;"/>
</style>
</textItem>
</contents>
</block>
</contents>
</noDataHandler>
<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
</style>
<listColumns>
<listColumn>
<listColumnTitle>
<style>
<defaultStyles>
<defaultStyle refStyle="lt"/>
</defaultStyles>
</style>
<contents>
<textItem>
<dataSource>
<dataItemLabel refDataItem="Country"/>
</dataSource>
</textItem>
</contents>
</listColumnTitle>
<listColumnBody>
<style>
<defaultStyles>
<defaultStyle refStyle="lc"/>
</defaultStyles>
</style>
<contents>
<textItem>
<dataSource>
<dataItemValue refDataItem="Country"/>
</dataSource>
</textItem>
</contents>
</listColumnBody>
</listColumn>
<listColumn>
<listColumnTitle>
<style>
<defaultStyles>
<defaultStyle refStyle="lt"/>
</defaultStyles>
</style>
<contents>
<textItem>
<dataSource>
<staticValue>Retailer Type</staticValue>
</dataSource>
</textItem>
</contents>
</listColumnTitle>
<listColumnBody>
<style>
<defaultStyles>
<defaultStyle refStyle="lc"/>
</defaultStyles>
</style>
<contents>
<repeaterTable name="Repeater table1" refQuery="Query1" across="20" down="1">
<repeaterTableCell>
<contents>
<textItem>
<dataSource>
<dataItemValue refDataItem="Retailer type"/>
</dataSource>
</textItem>
<textItem>
<dataSource>
<staticValue>, </staticValue>
</dataSource>
</textItem>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="rc"/>
</defaultStyles>
<CSS value="padding:0px;font-size:8pt"/>
</style>
</repeaterTableCell>
<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="rt"/>
</defaultStyles>
</style>
<masterDetailLinks>
<masterDetailLink>
<masterContext>
<dataItemContext refDataItem="Country"/>
</masterContext>
<detailContext>
<dataItemContext refDataItem="Country"/>
</detailContext>
</masterDetailLink>
</masterDetailLinks>
</repeaterTable>
</contents>
</listColumnBody>
</listColumn>
<listColumn>
<listColumnTitle>
<style>
<defaultStyles>
<defaultStyle refStyle="lt"/>
</defaultStyles>
</style>
<contents>
<textItem>
<dataSource>
<dataItemLabel refDataItem="Quantity"/>
</dataSource>
</textItem>
</contents>
</listColumnTitle>
<listColumnBody>
<style>
<defaultStyles>
<defaultStyle refStyle="lm"/>
</defaultStyles>
</style>
<contents>
<textItem>
<dataSource>
<dataItemValue refDataItem="Quantity"/>
</dataSource>
</textItem>
</contents>
</listColumnBody>
</listColumn>
</listColumns>
<listOverallGroup>
<listFooter>
<listRows>
<listRow>
<rowCells>
<rowCell colSpan="2">
<contents>
<textItem>
<dataSource>
<staticValue>Overall</staticValue>
</dataSource>
</textItem>
<textItem>
<dataSource>
<staticValue> - </staticValue>
</dataSource>
</textItem>
<textItem>
<dataSource>
<staticValue>Summary</staticValue>
</dataSource>
</textItem>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="if"/>
</defaultStyles>
</style>
</rowCell>
<rowCell>
<contents>
<textItem>
<dataSource>
<dataItemValue refDataItem="Summary(Quantity)"/>
</dataSource>
</textItem>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="is"/>
</defaultStyles>
</style>
</rowCell>
</rowCells>
</listRow>
</listRows>
</listFooter>
</listOverallGroup>
</list>
</contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes>
<XMLAttribute output="no" name="RS_CreateExtendedDataItems" value="true"/>
<XMLAttribute output="no" name="RS_modelModificationTime" value="2015-11-25T21:38:24.820Z"/>
<XMLAttribute output="no" name="listSeparator" value=","/>
</XMLAttributes>
<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO sales (query)']/model[@name='model']</modelPath>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection>
<dataItem aggregate="none" rollupAggregate="none" name="Country" sort="ascending">
<expression>[Sales (query)].[Branch].[Country]</expression>
<XMLAttributes>
<XMLAttribute output="no" name="RS_dataType" value="3"/>
<XMLAttribute output="no" name="RS_dataUsage" value="attribute"/>
</XMLAttributes>
</dataItem>
<dataItem aggregate="none" rollupAggregate="none" name="Retailer type" sort="ascending">
<expression>[Sales (query)].[Retailer type].[Retailer type]</expression>
<XMLAttributes>
<XMLAttribute output="no" name="RS_dataType" value="3"/>
<XMLAttribute output="no" name="RS_dataUsage" value="attribute"/>
</XMLAttributes>
</dataItem>
<dataItem aggregate="total" name="Quantity">
<expression>[Sales (query)].[Sales].[Quantity]</expression>
<XMLAttributes>
<XMLAttribute output="no" name="RS_dataType" value="1"/>
<XMLAttribute output="no" name="RS_dataUsage" value="fact"/>
</XMLAttributes>
</dataItem>
<dataItemListSummary refDataItem="Quantity" aggregateMethod="aggregate" name="Summary(Quantity)"/>
</selection>
<detailFilters>
<detailFilter>
<filterExpression>[Sales (query)].[Time].[Year] = 2013</filterExpression>
</detailFilter>
</detailFilters>
</query>
</queries>
<reportName>Repeater Table (simple - using Samples data)</reportName>
</report>