Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
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 )
icon5.gif  Create a data-set grouped by column values Using Flat file datasource-BIRTv2.2.2 [message #555581] Fri, 27 August 2010 05:18 Go to next message
srimal  is currently offline 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:

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

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 Go to previous messageGo to next message
Jason Weathersby is currently offline 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 &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
>


Jason Weathersby

BIRT Exchange
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 12:45 Go to previous messageGo to next message
srimal  is currently offline 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

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 Go to previous messageGo to next message
Jason Weathersby is currently offline 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 &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
>


Jason Weathersby

BIRT Exchange
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 Go to previous message
srimal  is currently offline srimal
Messages: 3
Registered: August 2010
Junior Member
Hello Jason,

I got it.
Many thanks !!!! Smile

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


Current Time: Thu Apr 24 06:37:19 EDT 2014

Powered by FUDForum. Page generated in 0.02096 seconds