Eclipse Community Forums: BIRT » How to concatenate values from the same column

Home » Archived » BIRT » How to concatenate values from the same column

How to concatenate values from the same column [message #365736]

Wed, 29 October 2008 09:22

Eclipse User

I need to be able to concatenate strings from different rows from the same
DataSet. I have noticed that there is a Total.concatenate() method but I
can't find any information about it. I am not sure what information I need
to put in the aggregation wizard.
Where can I find information about this function?

let say I have the following table

customers
---------
Steve
Joe
Denny

I want to display the result as a csv. In this case : Steve, Joe, Denny

--Shimi

Re: How to concatenate values from the same column [message #365738 is a reply to message #365736]

Wed, 29 October 2008 10:26

Eclipse User

Originally posted by: jasonweathersby.alltel.net

Shimi,

Take a look at this example:

<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.17"
id="1">
<property name="createdBy">Eclipse BIRT Designer Version
2.3.1.v20080911 Build <2.3.1.v20080922-1151></property>
<property name="units">in</property>
<property name="iconFile">/templates/blank_report.gif</property>
<data-sources>
<oda-data-source
extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Data Source"
id="6">
<property
name="odaDriverClass">org.eclipse.birt.report.data.oda.sampledb.Driver </property>
<property name="odaURL">jdbc:classicmodels:sampledb</property>
<property name="odaUser">ClassicModels</property>
<property name="OdaConnProfileName"></property>
</oda-data-source>
</data-sources>
<data-sets>
<oda-data-set
extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet "
name="Data Set" id="7">
<list-property name="columnHints">
<structure>
<property name="columnName">PRODUCTLINE</property>
<property name="displayName">PRODUCTLINE</property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">PRODUCTLINE</property>
<property name="dataType">string</property>
</structure>
</list-property>
</structure>
<property name="dataSource">Data Source</property>
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">PRODUCTLINE</property>
<property name="nativeName">PRODUCTLINE</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
</list-property>
<property name="queryText">select
CLASSICMODELS.PRODUCTLINES.PRODUCTLINE
from CLASSICMODELS.PRODUCTLINES</property>
<xml-property name="designerValues"><![CDATA[<?xml
version="1.0" encoding="UTF-8"?>
<model:DesignValues
xmlns:design="http://www.eclipse.org/datatools/connectivity/oda/design"
xmlns:model="http://www.eclipse.org/birt/report/model/adapter/odaModel">
<Version>1.0</Version>
<design:ResultSets derivedMetaData="true">
<design:resultSetDefinitions>
<design:resultSetColumns>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>PRODUCTLINE</design:name>
<design:position>1</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>50</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>PRODUCTLINE</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>PRODUCTLINE</design:label>
<design:formattingHints>
<design:displaySize>50</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
</design:resultSetColumns>
</design:resultSetDefinitions>
</design:ResultSets>
</model:DesignValues>]]></xml-property>
</oda-data-set>
</data-sets>
<styles>
<style name="crosstab-cell" id="4">
<property name="borderBottomColor">#CCCCCC</property>
<property name="borderBottomStyle">solid</property>
<property name="borderBottomWidth">1pt</property>
<property name="borderLeftColor">#CCCCCC</property>
<property name="borderLeftStyle">solid</property>
<property name="borderLeftWidth">1pt</property>
<property name="borderRightColor">#CCCCCC</property>
<property name="borderRightStyle">solid</property>
<property name="borderRightWidth">1pt</property>
<property name="borderTopColor">#CCCCCC</property>
<property name="borderTopStyle">solid</property>
<property name="borderTopWidth">1pt</property>
</style>
<style name="crosstab" id="5">
<property name="borderBottomColor">#CCCCCC</property>
<property name="borderBottomStyle">solid</property>
<property name="borderBottomWidth">1pt</property>
<property name="borderLeftColor">#CCCCCC</property>
<property name="borderLeftStyle">solid</property>
<property name="borderLeftWidth">1pt</property>
<property name="borderRightColor">#CCCCCC</property>
<property name="borderRightStyle">solid</property>
<property name="borderRightWidth">1pt</property>
<property name="borderTopColor">#CCCCCC</property>
<property name="borderTopStyle">solid</property>
<property name="borderTopWidth">1pt</property>
</style>
<style name="table-header" id="31">
<property name="backgroundColor">#6078BF</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">small</property>
<property name="fontWeight">bold</property>
<property name="color">#FFFFFF</property>
<property name="paddingTop">0px</property>
<property name="paddingLeft">0px</property>
<property name="paddingBottom">0px</property>
<property name="paddingRight">0px</property>
</style>
<style name="table-footer" id="32">
<property name="backgroundColor">#6078BF</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">small</property>
<property name="fontWeight">bold</property>
<property name="color">#FFFFFF</property>
<property name="paddingTop">0px</property>
<property name="paddingLeft">0px</property>
<property name="paddingBottom">0px</property>
<property name="paddingRight">0px</property>
</style>
<style name="table-detail" id="33">
<property name="backgroundColor">#E6ECFF</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">x-small</property>
<property name="fontWeight">bold</property>
<property name="color">#000080</property>
<property name="paddingTop">0px</property>
<property name="paddingLeft">0px</property>
<property name="paddingBottom">0px</property>
<property name="paddingRight">0px</property>
</style>
<style name="table-group-header-1" id="34">
<property name="backgroundColor">#809FFF</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">small</property>
<property name="fontWeight">bold</property>
<property name="color">#FFFFFF</property>
<property name="paddingTop">0px</property>
<property name="paddingLeft">0px</property>
<property name="paddingBottom">0px</property>
<property name="paddingRight">0px</property>
</style>
<style name="table-group-footer-1" id="35">
<property name="backgroundColor">#809FFF</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">small</property>
<property name="fontWeight">bold</property>
<property name="color">#FFFFFF</property>
<property name="paddingTop">0px</property>
<property name="paddingLeft">0px</property>
<property name="paddingBottom">0px</property>
<property name="paddingRight">0px</property>
</style>
<style name="special-header" id="36">
<property name="backgroundColor">#6078BF</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">medium</property>
<property name="fontWeight">bold</property>
<property name="color">#FFFFFF</property>
<property name="paddingTop">0px</property>
<property name="paddingLeft">0px</property>
<property name="paddingBottom">0px</property>
<property name="paddingRight">0px</property>
</style>
<style name="crosstab-detail" id="37">
<property name="backgroundColor">#E6ECFF</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">x-small</property>
<property name="fontWeight">bold</property>
<property name="color">#000080</property>
<property name="paddingTop">0px</property>
<property name="paddingLeft">0px</property>
<property name="paddingBottom">0px</property>
<property name="paddingRight">0px</property>
</style>
<style name="crosstab-header" id="38">
<property name="backgroundColor">#6078BF</property>
<property name="fontFamily">"Arial"</property>
<property name="fontSize">small</property>
<property name="fontWeight">bold</property>
<property name="color">#FFFFFF</property>
<property name="paddingTop">0px</property>
<property name="paddingLeft">0px</property>
<property name="paddingBottom">0px</property>
<property name="paddingRight">0px</property>
</style>
</styles>
<page-setup>
<simple-master-page name="Simple MasterPage" id="2">
<page-footer>
<text id="3">
<property name="contentType">html</property>
<text-property
name="content"><![CDATA[<value-of>new Date()</value-of>]]></text-property>
</text>
</page-footer>
</simple-master-page>
</page-setup>
<body>
<table id="19">
<property name="height">0.9270833333333334in</property>
<property name="width">5.989583333333333in</property>
<property name="dataSet">Data Set</property>
<list-property name="boundDataColumns">
<structure>
<property name="name">PRODUCTLINE</property>
<property name="displayName">PRODUCTLINE</property>
<expression
name="expression">dataSetRow["PRODUCTLINE"]</expression >
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">Aggregation</property>
<property name="dataType">string</property>
<property
name="aggregateFunction">CONCATENATE</property>
<list-property name="arguments">
<structure>
<property name="name">Expression</property>
<expression
name="value">row["PRODUCTLINE"]</expression>
</structure>
<structure>
<property name="name">Separator</property>
<expression name="value">", "</expression>
</structure>
<structure>
<property name="name">Max length</property>
<expression name="value">1024</expression>
</structure>
<structure>
<property name="name">Show all
values</property>
<expression name="value">true</expression>
</structure>
</list-property>
<expression
name="filterExpr">row["PRODUCTLINE"]!="Classic Cars"</expression>
</structure>
</list-property>
<property name="summary">test summary</property>
<text-property name="caption">My Caption</text-property>
<column id="28"/>
<header>
<row id="20">
<cell id="21">
<label id="22">
<text-property
name="text">PRODUCTLINE</text-property>
</label>
</cell>
</row>
</header>
<detail>
<row id="23">
<cell id="24">
<data id="25">
<property
name="resultSetColumn">PRODUCTLINE</property>
</data>
</cell>
</row>
</detail>
<footer>
<row id="26">
<property name="height">0.3958333333333333in</property>
<cell id="27">
<data id="30">
<property
name="resultSetColumn">Aggregation</property>
</data>
</cell>
</row>
</footer>
</table>
</body>
</report>

Jason

shimi wrote:
> I need to be able to concatenate strings from different rows from the
> same DataSet. I have noticed that there is a Total.concatenate() method
> but I can't find any information about it. I am not sure what
> information I need to put in the aggregation wizard.
> Where can I find information about this function?
>
> let say I have the following table
>
> customers
> ---------
> Steve
> Joe
> Denny
>
> I want to display the result as a csv. In this case : Steve, Joe, Denny
>
> --Shimi
>

Re: How to concatenate values from the same column [message #365740 is a reply to message #365738]

Wed, 29 October 2008 10:53

Eclipse User

Thanks
What is the purpose of the Max length and the show all values properties?

--Shimi

Re: How to concatenate values from the same column [message #365741 is a reply to message #365740]

Wed, 29 October 2008 11:53

Eclipse User

Originally posted by: jasonweathersby.alltel.net

Shimi,

Max length set the maximum length of the returned result. The show all
parameter will determine whether duplicates are are allowed. For
example if class cars was in the list twice, show all set to true would
show it twice. Show all set to false and it would only show once.

Jason

shimi wrote:
> Thanks
> What is the purpose of the Max length and the show all values properties?
>
> --Shimi
>

Previous Topic:	How can I set a CSS class name for a report item?
Next Topic:	Returning report in non HttpServlet stream

Goto Forum:

-=] Back to Top [=-

Current Time: Tue Nov 04 06:13:44 EST 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter