Eclipse Community Forums: BIRT » Create a data-set grouped by column values Using Flat file datasource-BIRTv2.2.2

Home » Eclipse Projects » 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 )

Show: Today's Messages :: Show Polls :: Message Navigator

Create a data-set grouped by column values Using Flat file datasource-BIRTv2.2.2 [message #555581]

Fri, 27 August 2010 05:18

srimal
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:

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 10:24

Jason Weathersby
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 <2.2.2.v20080227-1131></property>
<property name="units">in</property>
<property name="comments">Copyright (c) 2007 <<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
>

Jason Weathersby

BIRT Exchange

Report message to a moderator

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 12:45

srimal
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 12:58

Jason Weathersby
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 <2.2.2.v20080227-1131></property>
<property name="units">in</property>
<property name="comments">Copyright (c) 2007 <<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
>

Jason Weathersby

BIRT Exchange

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 04:06

srimal
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 23 06:26:05 EDT 2013