| How do I create this report? (details inside) [message #257249] |
Fri, 05 October 2007 00:39  |
Eclipse User |
|
|
|
Hello all.
Here is a scenario that I am running to and if someone can drop me a
hint as to how this can be done, I would appreciate it. Lets say I have
a table with three columns "State", "City", "id":
Texas Austin 1
Texas Dallas 5
Texas San Antonio 6
California San Francisco 10
California LA 18
......
I can easily group my date based on, say, State:
Texas
Austin 1
Dallas 5
San Antoni 6
California
San Francisco 10
LA 18
......
Now lets say in the group header, in the cell next to the State, I would
like to list all the cities in that State, so the above data will be
presented like:
Texas Austin, Dallas, San Antonio
Austin 1
Dallas 5
San Antoni 6
California San Francisco, LA, ...
San Francisco 10
LA 18
......
My question is: how can I create the concatenated list of cities in a
group (some sort of aggregation) as it is presented above?
Thanks,
Ali.
|
|
|
| Re: How do I create this report? (details inside) [message #257561 is a reply to message #257249] |
Mon, 08 October 2007 13:02  |
| Eclipse User |
|
|
|
Originally posted by: jasonweathersby.alltel.net
Ali,
There may be a better way of doing this, but one way is to drop the
table in twice and use script in the oncreate of the row in the first
instance to create the string. Hide the first instance and display the
string in the second instance. Attached is an example.
Jason
<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.14"
id="1">
<property name="createdBy">Eclipse BIRT Designer Version
2.2.0.v20070620 Build <2.2.0.v20070626-1003></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>
<method name="beforeFactory"><![CDATA[citystr = "";]]></method>
<text-property name="displayName">Blank Report</text-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>
</oda-data-source>
</data-sources>
<data-sets>
<oda-data-set
extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet "
name="Data Set" id="7">
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">OFFICECODE</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">CITY</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">PHONE</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">ADDRESSLINE1</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">5</property>
<property name="name">ADDRESSLINE2</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">6</property>
<property name="name">STATE</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">7</property>
<property name="name">COUNTRY</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">8</property>
<property name="name">POSTALCODE</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">9</property>
<property name="name">TERRITORY</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">OFFICECODE</property>
<property name="nativeName">OFFICECODE</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">CITY</property>
<property name="nativeName">CITY</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">PHONE</property>
<property name="nativeName">PHONE</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">ADDRESSLINE1</property>
<property name="nativeName">ADDRESSLINE1</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">5</property>
<property name="name">ADDRESSLINE2</property>
<property name="nativeName">ADDRESSLINE2</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">6</property>
<property name="name">STATE</property>
<property name="nativeName">STATE</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">7</property>
<property name="name">COUNTRY</property>
<property name="nativeName">COUNTRY</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">8</property>
<property name="name">POSTALCODE</property>
<property name="nativeName">POSTALCODE</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">9</property>
<property name="name">TERRITORY</property>
<property name="nativeName">TERRITORY</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
</list-property>
<property name="queryText">select *
from offices</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>OFFICECODE</design:name>
<design:position>1</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>10</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>OFFICECODE</design:label>
<design:formattingHints>
<design:displaySize>10</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>CITY</design:name>
<design:position>2</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>50</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>CITY</design:label>
<design:formattingHints>
<design:displaySize>50</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>PHONE</design:name>
<design:position>3</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>50</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>PHONE</design:label>
<design:formattingHints>
<design:displaySize>50</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>ADDRESSLINE1</design:name>
<design:position>4</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>50</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>ADDRESSLINE1</design:label>
<design:formattingHints>
<design:displaySize>50</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>ADDRESSLINE2</design:name>
<design:position>5</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>50</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>ADDRESSLINE2</design:label>
<design:formattingHints>
<design:displaySize>50</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>STATE</design:name>
<design:position>6</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>50</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>STATE</design:label>
<design:formattingHints>
<design:displaySize>50</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>COUNTRY</design:name>
<design:position>7</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>50</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>COUNTRY</design:label>
<design:formattingHints>
<design:displaySize>50</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>POSTALCODE</design:name>
<design:position>8</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>15</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>POSTALCODE</design:label>
<design:formattingHints>
<design:displaySize>15</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>TERRITORY</design:name>
<design:position>9</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>10</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>TERRITORY</design:label>
<design:formattingHints>
<design:displaySize>10</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" 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-cell" 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="80">
<property name="marginTop">0pt</property>
<property name="width">100%</property>
<property name="dataSet">Data Set</property>
<list-property name="visibility">
<structure>
<property name="format">all</property>
<expression name="valueExpr">true</expression>
</structure>
</list-property>
<list-property name="boundDataColumns">
<structure>
<property name="name">OFFICECODE</property>
<expression
name="expression">dataSetRow["OFFICECODE"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">CITY</property>
<expression
name="expression">dataSetRow["CITY"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">PHONE</property>
<expression
name="expression">dataSetRow["PHONE"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">ADDRESSLINE1</property>
<expression
name="expression">dataSetRow["ADDRESSLINE1"]</expression >
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">ADDRESSLINE2</property>
<expression
name="expression">dataSetRow["ADDRESSLINE2"]</expression >
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">STATE</property>
<expression
name="expression">dataSetRow["STATE"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">COUNTRY</property>
<expression
name="expression">dataSetRow["COUNTRY"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">POSTALCODE</property>
<expression
name="expression">dataSetRow["POSTALCODE"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">TERRITORY</property>
<expression
name="expression">dataSetRow["TERRITORY"]</expression>
<property name="dataType">string</property>
</structure>
</list-property>
<column id="129"/>
<column id="130"/>
<column id="134"/>
<column id="135"/>
<column id="137"/>
<header>
<row id="81">
<cell id="82">
<label id="83">
<text-property
name="text">OFFICECODE</text-property>
</label>
</cell>
<cell id="84">
<label id="85">
<text-property name="text">CITY</text-property>
</label>
</cell>
<cell id="92">
<label id="93">
<text-property
name="text">STATE</text-property>
</label>
</cell>
<cell id="94">
<label id="95">
<text-property
name="text">COUNTRY</text-property>
</label>
</cell>
<cell id="98">
<label id="99">
<text-property
name="text">TERRITORY</text-property>
</label>
</cell>
</row>
</header>
<group id="138">
<property name="groupName">NewTableGroup1</property>
<property name="interval">none</property>
<property name="sortDirection">asc</property>
<expression name="keyExpr">row["COUNTRY"]</expression>
<structure name="toc">
<expression
name="expressionValue">row["COUNTRY"]</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="139">
<cell id="140">
<data id="151">
<property
name="resultSetColumn">COUNTRY</property>
</data>
</cell>
<cell id="141"/>
<cell id="142"/>
<cell id="143"/>
<cell id="144"/>
</row>
</header>
<footer>
<row id="145">
<cell id="146"/>
<cell id="147"/>
<cell id="148"/>
<cell id="149"/>
<cell id="150"/>
</row>
</footer>
</group>
<detail>
<row id="100">
<method name="onCreate"><![CDATA[var cty =
this.getRowData().getColumnValue("CITY");
var cnty = this.getRowData().getColumnValue("COUNTRY");
var tcnty = reportContext.getPersistentGlobalVariable(cnty);
if( tcnty == null ){
reportContext.setPersistentGlobalVariable(cnty, cty);
}else{
tcnty = tcnty + " " + cty;
reportContext.setPersistentGlobalVariable(cnty, tcnty);
}]]></method>
<cell id="101">
<data id="102">
<property
name="resultSetColumn">OFFICECODE</property>
</data>
</cell>
<cell id="103">
<data id="104">
<property
name="resultSetColumn">CITY</property>
</data>
</cell>
<cell id="111">
<data id="112">
<property
name="resultSetColumn">STATE</property>
</data>
</cell>
<cell id="113">
<data id="114">
<property
name="resultSetColumn">COUNTRY</property>
</data>
</cell>
<cell id="117">
<data id="118">
<property
name="resultSetColumn">TERRITORY</property>
</data>
</cell>
</row>
</detail>
<footer>
<row id="119">
<cell id="120"/>
<cell id="121"/>
<cell id="125"/>
<cell id="126"/>
<cell id="128"/>
</row>
</footer>
</table>
<table id="152">
<property name="width">100%</property>
<property name="dataSet">Data Set</property>
<list-property name="boundDataColumns">
<structure>
<property name="name">OFFICECODE</property>
<expression
name="expression">dataSetRow["OFFICECODE"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">CITY</property>
<expression
name="expression">dataSetRow["CITY"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">PHONE</property>
<expression
name="expression">dataSetRow["PHONE"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">ADDRESSLINE1</property>
<expression
name="expression">dataSetRow["ADDRESSLINE1"]</expression >
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">ADDRESSLINE2</property>
<expression
name="expression">dataSetRow["ADDRESSLINE2"]</expression >
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">STATE</property>
<expression
name="expression">dataSetRow["STATE"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">COUNTRY</property>
<expression
name="expression">dataSetRow["COUNTRY"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">POSTALCODE</property>
<expression
name="expression">dataSetRow["POSTALCODE"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">TERRITORY</property>
<expression
name="expression">dataSetRow["TERRITORY"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">tst</property>
<expression name="expression">tcnty =
reportContext.getPersistentGlobalVariable(row["COUNTRY"]); </expression>
<property name="dataType">string</property>
</structure>
</list-property>
<column id="195"/>
<column id="196"/>
<column id="197"/>
<column id="198"/>
<column id="199"/>
<header>
<row id="153">
<cell id="154">
<label id="155">
<text-property
name="text">OFFICECODE</text-property>
</label>
</cell>
<cell id="156">
<label id="157">
<text-property name="text">CITY</text-property>
</label>
</cell>
<cell id="158">
<label id="159">
<text-property
name="text">STATE</text-property>
</label>
</cell>
<cell id="160">
<label id="161">
<text-property
name="text">COUNTRY</text-property>
</label>
</cell>
<cell id="162">
<label id="163">
<text-property
name="text">TERRITORY</text-property>
</label>
</cell>
</row>
</header>
<group id="164">
<property name="groupName">NewTableGroup1</property>
<property name="interval">none</property>
<property name="sortDirection">asc</property>
<expression name="keyExpr">row["COUNTRY"]</expression>
<structure name="toc">
<expression
name="expressionValue">row["COUNTRY"]</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="165">
<cell id="166">
<data id="167">
<property
name="resultSetColumn">COUNTRY</property>
</data>
</cell>
<cell id="168">
<data id="200">
<property
name="resultSetColumn">tst</property>
</data>
</cell>
<cell id="169"/>
<cell id="170"/>
<cell id="171"/>
</row>
</header>
<footer>
<row id="172">
<cell id="173"/>
<cell id="174"/>
<cell id="175"/>
<cell id="176"/>
<cell id="177"/>
</row>
</footer>
</group>
<detail>
<row id="178">
<cell id="179">
<data id="180">
<property
name="resultSetColumn">OFFICECODE</property>
</data>
</cell>
<cell id="181">
<data id="182">
<property
name="resultSetColumn">CITY</property>
</data>
</cell>
<cell id="183">
<data id="184">
<property
name="resultSetColumn">STATE</property>
</data>
</cell>
<cell id="185">
<data id="186">
<property
name="resultSetColumn">COUNTRY</property>
</data>
</cell>
<cell id="187">
<data id="188">
<property
name="resultSetColumn">TERRITORY</property>
</data>
</cell>
</row>
</detail>
<footer>
<row id="189">
<cell id="190"/>
<cell id="191"/>
<cell id="192"/>
<cell id="193"/>
<cell id="194"/>
</row>
</footer>
</table>
</body>
</report>
Ali Naddaf wrote:
> Hello all.
>
> Here is a scenario that I am running to and if someone can drop me a
> hint as to how this can be done, I would appreciate it. Lets say I have
> a table with three columns "State", "City", "id":
>
>
> Texas Austin 1
> Texas Dallas 5
> Texas San Antonio 6
> California San Francisco 10
> California LA 18
> ......
>
> I can easily group my date based on, say, State:
>
>
> Texas
> Austin 1
> Dallas 5
> San Antoni 6
> California
> San Francisco 10
> LA 18
> ......
>
> Now lets say in the group header, in the cell next to the State, I would
> like to list all the cities in that State, so the above data will be
> presented like:
>
>
> Texas Austin, Dallas, San Antonio
> Austin 1
> Dallas 5
> San Antoni 6
> California San Francisco, LA, ...
> San Francisco 10
> LA 18
> ......
>
> My question is: how can I create the concatenated list of cities in a
> group (some sort of aggregation) as it is presented above?
>
>
> Thanks,
> Ali.
|
|
|
Powered by
FUDForum. Page generated in 0.04018 seconds
Home