cognosreport-studio

Create array of values in Cognos


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


Solution

  • 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=&apos;Samples&apos;]/folder[@name=&apos;Models&apos;]/package[@name=&apos;GO sales (query)&apos;]/model[@name=&apos;model&apos;]</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>