Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Archived » BIRT » Create a data-set grouped by column values Using Flat file datasource-BIRTv2.2.2 (Create a dataset grouped by coloumn values Using Flat file datasource-BIRTv2.2.2 )
icon5.gif  Create a data-set grouped by column values Using Flat file datasource-BIRTv2.2.2 [message #555581] Fri, 27 August 2010 09:18 Go to next message
srimal is currently offline srimal Friend
Messages: 3
Registered: August 2010
Junior Member
Hi All,

I want to create a dataset grouped by coloumn values using BIRT v2.2.2.(This can do it in SQL query if I 'm using a db2 or sql database)But don't know how to do when datasource pointed to a flatfile.(.csv file)
Appreciate your help on the same.
Thanks in Advanced !

srimal

Quote:

example:
dataset1:
service | category
lotus | A
lotus | B
lotus | A
lotus | B
lotus | C
lotus | D
lotus | B
lotus | A
lotus | C
lotus | A

Result would be:
dataset2:
category | Lotus_count
A | 4
B | 3
C | 2
D | 1

Report message to a moderator

Re: Create a data-set grouped by column values Using Flat file datasource-BIRTv2.2.2 [message #555672 is a reply to message #555581] Fri, 27 August 2010 14:24 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member
I used your data to make an example. It is attached.
Basically you want to add a table with both your columns then add a
group to the table on category. Next add an aggregation report item to
the group header that has its function set to count and set to aggregate
on the group. Delete the items in the detail row as they are not needed.

Jason

<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.15"
id="1">
<property name="createdBy">Eclipse BIRT Designer Version
2.2.2.r222_v20071226 Build &lt;2.2.2.v20080227-1131></property>
<property name="units">in</property>
<property name="comments">Copyright (c) 2007 &lt;&lt;Your Company
Name here>></property>
<html-property name="description">Creates a blank report with no
predefined content.</html-property>
<text-property name="displayName">Blank Report</text-property>
<property name="iconFile">/templates/blank_report.gif</property>
<data-sources>
<oda-data-source
extensionID="org.eclipse.datatools.connectivity.oda.flatfile " name="Data
Source" id="6">
<text-property name="displayName"></text-property>
<property name="HOME">C:\test9</property>
<property name="DELIMTYPE">PIPE</property>
<property name="CHARSET">UTF-8</property>
<property name="INCLCOLUMNNAME">YES</property>
<property name="INCLTYPELINE">NO</property>
</oda-data-source>
</data-sources>
<data-sets>
<oda-data-set
extensionID="org.eclipse.datatools.connectivity.oda.flatfile.dataSet "
name="Data Set" id="7">
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">service</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">category</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">service</property>
<property name="nativeName">service</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">category</property>
<property name="nativeName">category</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
</list-property>
<property name="queryText">select "service", "category"
from mydata.psv :
{"service","service",STRING;"category","category",STRING} </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>service</design:name>
<design:position>1</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>-1</design:precision>
<design:scale>-1</design:scale>
<design:nullability>Unknown</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>service</design:label>
<design:formattingHints/>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>category</design:name>
<design:position>2</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>-1</design:precision>
<design:scale>-1</design:scale>
<design:nullability>Unknown</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>category</design:label>
<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>
</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="8">
<property name="width">100%</property>
<property name="dataSet">Data Set</property>
<list-property name="boundDataColumns">
<structure>
<property name="name">service</property>
<expression
name="expression">dataSetRow["service"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">category</property>
<expression
name="expression">dataSetRow["category"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">Aggregation</property>
<expression
name="expression">dataSetRow["service"]</expression>
<property name="dataType">string</property>
<property name="aggregateFunction">SUM</property>
</structure>
<structure>
<property name="name">Aggregation_1</property>
<property name="dataType">integer</property>
<simple-property-list name="aggregateOn">
<value>NewTableGroup1</value>
</simple-property-list>
<property name="aggregateFunction">COUNT</property>
</structure>
</list-property>
<column id="22"/>
<column id="23"/>
<header>
<row id="9">
<cell id="10">
<label id="11">
<text-property
name="text">service</text-property>
</label>
</cell>
<cell id="12">
<label id="13">
<text-property
name="text">category</text-property>
</label>
</cell>
</row>
</header>
<group id="24">
<property name="groupName">NewTableGroup1</property>
<property name="interval">none</property>
<property name="sortDirection">asc</property>
<expression name="keyExpr">row["category"]</expression>
<structure name="toc">
<expression
name="expressionValue">row["category"]</expression>
</structure>
<property name="repeatHeader">true</property>
<property name="hideDetail">false</property>
<property name="pageBreakAfter">auto</property>
<property name="pageBreakBefore">auto</property>
<property name="pageBreakInside">auto</property>
<header>
<row id="25">
<cell id="26">
<data id="31">
<property
name="resultSetColumn">category</property>
</data>
</cell>
<cell id="27">
<data id="33">
<property
name="resultSetColumn">Aggregation_1</property>
</data>
</cell>
</row>
</header>
<footer>
<row id="28">
<cell id="29"/>
<cell id="30"/>
</row>
</footer>
</group>
<detail>
<row id="14">
<cell id="15"/>
<cell id="17"/>
</row>
</detail>
<footer>
<row id="19">
<cell id="20"/>
<cell id="21"/>
</row>
</footer>
</table>
</body>
</report>




On 8/27/2010 5:18 AM, srimal wrote:
> Hi All,
>
> I want to create a dataset grouped by coloumn values using BIRT
> v2.2.2.(This can do it in SQL query if I 'm using a db2 or sql
> database)But don't know how to do when datasource pointed to a
> flatfile.(.csv file)
> Appreciate your help on the same.
> Thanks in Advanced !
>
> srimal
>
> Quote:
>> example:
>> dataset1:
>> service | category
>> lotus | A
>> lotus | B
>> lotus | A
>> lotus | B
>> lotus | C
>> lotus | D
>> lotus | B
>> lotus | A
>> lotus | C
>> lotus | A
>>
>> Result would be:
>> dataset2:
>> category | Lotus_count
>> A | 4
>> B | 3
>> C | 2
>> D | 1
>

Report message to a moderator

icon14.gif  Re: Create a data-set grouped by column values Using Flat file datasource-BIRTv2.2.2 [message #555693 is a reply to message #555672] Fri, 27 August 2010 16:45 Go to previous messageGo to next message
srimal is currently offline srimal Friend
Messages: 3
Registered: August 2010
Junior Member
Hello Jason,

Great.... It worked !!!!! Smile
Many thanks.

I changed only :
"HOME">C:\tcr\csvdb

and
"queryText">select "service", "category" from mydata.csv


Also I just have few questions to be asked from you.Since I'm new to XML coding.

1). How do I improve my XML coding skills as a beginner.
2). How do I create the same using "Layout" design.
3). I want same table to show the "Total" like this:
Quote:

category | Lotus_count
A | 4
B | 3
C | 2
D | 1
Total | 10

Report message to a moderator

Re: Create a data-set grouped by column values Using Flat file datasource-BIRTv2.2.2 [message #556057 is a reply to message #555693] Mon, 30 August 2010 16:58 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member
1 and 2 - you should not need to build the reports in xml. I built the
example I posted in the report designer. I just posted the xml because
sometimes its easier to pass around this way.

On the third question:
Just add an aggregation element to the table footer row. See attached.


Jason

<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.15"
id="1">
<property name="createdBy">Eclipse BIRT Designer Version
2.2.2.r222_v20071226 Build &lt;2.2.2.v20080227-1131></property>
<property name="units">in</property>
<property name="comments">Copyright (c) 2007 &lt;&lt;Your Company
Name here>></property>
<html-property name="description">Creates a blank report with no
predefined content.</html-property>
<text-property name="displayName">Blank Report</text-property>
<property name="iconFile">/templates/blank_report.gif</property>
<data-sources>
<oda-data-source
extensionID="org.eclipse.datatools.connectivity.oda.flatfile " name="Data
Source" id="6">
<property name="HOME">C:\test9</property>
<property name="DELIMTYPE">PIPE</property>
<property name="CHARSET">UTF-8</property>
<property name="INCLCOLUMNNAME">YES</property>
<property name="INCLTYPELINE">NO</property>
</oda-data-source>
</data-sources>
<data-sets>
<oda-data-set
extensionID="org.eclipse.datatools.connectivity.oda.flatfile.dataSet "
name="Data Set" id="7">
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">service</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">category</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">service</property>
<property name="nativeName">service</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">category</property>
<property name="nativeName">category</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
</list-property>
<property name="queryText">select "service", "category"
from mydata.psv :
{"service","service",STRING;"category","category",STRING} </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>service</design:name>
<design:position>1</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>-1</design:precision>
<design:scale>-1</design:scale>
<design:nullability>Unknown</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>service</design:label>
<design:formattingHints/>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>category</design:name>
<design:position>2</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>-1</design:precision>
<design:scale>-1</design:scale>
<design:nullability>Unknown</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>category</design:label>
<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>
</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="8">
<property name="width">100%</property>
<property name="dataSet">Data Set</property>
<list-property name="boundDataColumns">
<structure>
<property name="name">service</property>
<expression
name="expression">dataSetRow["service"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">category</property>
<expression
name="expression">dataSetRow["category"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">Aggregation</property>
<expression
name="expression">dataSetRow["service"]</expression>
<property name="dataType">string</property>
<property name="aggregateFunction">SUM</property>
</structure>
<structure>
<property name="name">Aggregation_1</property>
<property name="dataType">integer</property>
<simple-property-list name="aggregateOn">
<value>NewTableGroup1</value>
</simple-property-list>
<property name="aggregateFunction">COUNT</property>
</structure>
<structure>
<property name="name">Aggregation_2</property>
<property name="dataType">any</property>
<property name="aggregateFunction">COUNT</property>
</structure>
</list-property>
<column id="22"/>
<column id="23"/>
<header>
<row id="9">
<cell id="10">
<label id="11">
<text-property
name="text">service</text-property>
</label>
</cell>
<cell id="12">
<label id="13">
<text-property
name="text">category</text-property>
</label>
</cell>
</row>
</header>
<group id="24">
<property name="groupName">NewTableGroup1</property>
<property name="interval">none</property>
<property name="sortDirection">asc</property>
<expression name="keyExpr">row["category"]</expression>
<structure name="toc">
<expression
name="expressionValue">row["category"]</expression>
</structure>
<property name="repeatHeader">true</property>
<property name="hideDetail">false</property>
<property name="pageBreakAfter">auto</property>
<property name="pageBreakBefore">auto</property>
<property name="pageBreakInside">auto</property>
<header>
<row id="25">
<cell id="26">
<data id="31">
<property
name="resultSetColumn">category</property>
</data>
</cell>
<cell id="27">
<data id="33">
<property
name="resultSetColumn">Aggregation_1</property>
</data>
</cell>
</row>
</header>
<footer>
<row id="28">
<cell id="29"/>
<cell id="30"/>
</row>
</footer>
</group>
<detail>
<row id="14">
<cell id="15"/>
<cell id="17"/>
</row>
</detail>
<footer>
<row id="19">
<cell id="20"/>
<cell id="21">
<data id="34">
<property
name="resultSetColumn">Aggregation_2</property>
</data>
</cell>
</row>
</footer>
</table>
</body>
</report>


On 8/27/2010 12:45 PM, srimal wrote:
> Hello Jason,
>
> Great.... It worked !!!!! :) Many thanks.
>
> I changed only :
> "HOME">C:\tcr\csvdb
> and
> "queryText">select "service", "category" from mydata.csv
>
> Also I just have few questions to be asked from you.Since I'm new to XML
> coding.
>
> 1). How do I improve my XML coding skills as a beginner.
> 2). How do I create the same using "Layout" design.
> 3). I want same table to show the "Total" like this:
> Quote:
>> category | Lotus_count
>> A | 4
>> B | 3
>> C | 2
>> D | 1
>> Total | 10
>

Report message to a moderator

Re: Create a data-set grouped by column values Using Flat file datasource-BIRTv2.2.2 [message #556404 is a reply to message #556057] Wed, 01 September 2010 08:06 Go to previous message
srimal is currently offline srimal Friend
Messages: 3
Registered: August 2010
Junior Member
Hello Jason,

I got it.
Many thanks !!!! Smile

regards,
srimal

Report message to a moderator

Previous Topic:java.lang.IllegalStateException: zip file closed
Next Topic:change size of the grid's case
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 22:29:51 GMT 2024

Powered by FUDForum. Page generated in 0.03778 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top

Sign up to our Newsletter

A fresh new issue delivered monthly