Concatenate Columns values into a string

[stancliffer]

Hi

I want to concatenate the results from a a query subject's column into a string, any suggestions?


eg
COL1 COL2
A      STRING1
A      STRING2

I want to see
COL1 COL2
A      STRING1, STRING2


Stancliffer

[MFGF]

Hi Stan,

You could try adding a couple of Query calculations for the two values - one using the maxumum() summary and the other using the minimum() summary

eg:

minimum([COL2] for [COL1])

and

maximum([COL2] for [COL1])

You could then drop these into the same list column (having unlocked the structure) with a text item (space) between them.

Worth a try...

MF.

[stancliffer]

Thanks

I can make this work for my example but I've found a situation with 3 values

Eg
COL1 COL2
B      STRING1
B      STRING2
B      STRING3

I want to see
COL1 COL2
B      STRING1, STRING2, STRING3

For the perfect solution I want to cater for any number of values in COL2 for a given value in COL1

Regards

Stancliffer

[CognosPaul]

Remove COL2 from the list. In it's place drag a repeater, and set the repeater to the same query. Place the field from COL2 into the repeater, and set the master/detail to COL1 <--> COL1.

It's a bit complicated, so I've created a quick example XML. Unfortunately I don't have any of the samples installed at this client. The report is on a metric studio package, so you should be able to just switch the package without fixing anything. If you don't have Metric Studio then just replace the data items with whatever you want to test.

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="en">
<modelPath>/content/package[@name='מערכת מדדים ויעדים']/model[@name='great_outdoors_metrics']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<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" rowsPerPage="999">



<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="Year"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Year"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><staticValue>Repeater</staticValue></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><repeater name="Repeater1" refQuery="Query1">
<contents><textItem><dataSource><staticValue>, </staticValue></dataSource><conditionalRender refVariable="notFirstRow"><renderFor refVariableValue="1"/></conditionalRender></textItem><textItem><dataSource><dataItemValue refDataItem="Month"/></dataSource></textItem></contents>
<masterDetailLinks><masterDetailLink><masterContext><dataItemContext refDataItem="Year"/></masterContext><detailContext><dataItemContext refDataItem="Year"/></detailContext></masterDetailLink></masterDetailLinks><sortList><sortItem refDataItem="Time Level4 ID"/></sortList></repeater></contents></listColumnBody></listColumn></listColumns></list></contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes><queries><query name="Query1"><source><model/></source><selection><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[Metrics].[Time].[Year]</expression></dataItem><dataItem name="Month" aggregate="none" rollupAggregate="none"><expression>[Metrics].[Time].[Month]</expression></dataItem><dataItem name="Time Level4 ID" aggregate="none" rollupAggregate="none"><expression>[Metrics].[Time].[Time Level4 ID]</expression></dataItem></selection><detailFilters><detailFilter><filterExpression>[Month] is not null</filterExpression></detailFilter></detailFilters></query></queries><reportVariables><reportVariable type="boolean" name="notFirstRow">
<reportExpression>RowNumber ()&gt;1</reportExpression>
<variableValues>
<variableValue value="1"/>
</variableValues>
</reportVariable></reportVariables></report>

[stancliffer]

Hi

I need to concatenate data from a query in Report studio for one column into a string, grouping on another column's value

Eg
COL1 COL2
B      STRING1
B      STRING2
B      STRING3

I want to see
COL1 COL2
B      STRING1, STRING2, STRING3

For the perfect solution I want to cater for any number of values in COL2 for a given value in COL1

I can think of a solution using user defined functions and views with SQL server but I need a pure Report Studio solution

Has anyone any suggestions?

Regrdas

Stancliffer

[Lynn]

One method is to use a repeater with a master-detail relationship. The link below has some information on this. Performance may not be great, but you it is easy to do so quick to try and see if it will work for you.

https://www.ibm.com/developerworks/forums/thread.jspa?threadID=312205&tstart=75

[CognosPaul]

Stancliffer, please do not make crossposts.

[stancliffer]

Thanks

The repeater works a treat, simple when you know how!!

Sorry about the crosspost.

Regards

Stancliffer

[stancliffer]

I would just like to add an err of caution. I have noticed when running the report that the executed SQL (when viewed from SQL Server Profiler) that returns the data for the repeater is executed for each row in the report which has given a poor performance.

[Lynn]

Yup. That's what repeaters do.

[stancliffer]

Yep Could work better though

[Lynn]

Actually I believe that behavior is the result of the master-detail relationship so we can't unfairly blame just the repeater container.

I did have a client who had developed a database function to do this concatenation. They used it in the FM model to present a single query item containing the concatenated string. Of course database functions have their own performance implications. This approach is another possibility to consider, but I found it rather inflexible since the function did not sort and had the delimiter pre-defined. In their case it was a list of people with first initial and last name, so there was further inflexibility when someone wanted just the last names or the full first and last name or "last name, first name", etc.