Home » Archived » BIRT » Manually add rows to dataset
| Manually add rows to dataset [message #242709] |
Thu, 14 June 2007 13:50  |
Eclipse User |
|
|
|
Originally posted by: rg.openadvice.de
Hi!
I have the following dataset:
date | severity | count
02/2007 | 0 | 45
02/2007 | 1 | 56
03/2007 | 0 | 42
03/2007 | 1 | 12
03/2007 | 2 | 32
I would like to complete the dataset in the following way:
date | severity | count
02/2007 | 0 | 45
02/2007 | 1 | 56
02/2007 | 2 | 0
02/2007 | 3 | 0
03/2007 | 0 | 42
03/2007 | 1 | 12
03/2007 | 2 | 32
03/2007 | 3 | 0
Is it possible to add these rows to the dataset after getting the data
from the database?
I've tried using joins on the database, but it seems that you cannot join
group-wise.
Thanks!
|
|
|
| Re: Manually add rows to dataset [message #242860 is a reply to message #242709] |
Fri, 15 June 2007 04:26  |
| Eclipse User |
|
|
|
Originally posted by: jasonweathersby.alltel.net
Ralf,
There may be an easier way to do this or this may not be what you are
looking for, but you could write a scripted datasource to created the
manual rows, then create a joint data set that combines the two
datasets. The final step is to create a computed column in the joint
data set that is the merged column you want to use. So if I use select
ordernumber from my db and I wanted to add 3 ordersnumbers I would
create a scripted data set that returns the 3 orders. Then create a
joint data set (full outer) which would return something like
ds1 ds2
1
2
3
4
5
6
Where 4, 5 and 6 are the scripted ones. I would then add a computed
column to the joint data set that would merge these. This would have an
expression like:
if( row["Data Set::ORDERNUMBER"] == null ){
row["Data Set1::on"];
}else{
row["Data Set::ORDERNUMBER"];
}
Attached is a simplistic 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.v20070601 Build <2.2.0.v20070601-1347></property>
<property name="units">in</property>
<property name="comments">Copyright (c) 2006 <<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.birt.report.data.oda.jdbc" name="Data Source"
id="6">
<text-property name="displayName"></text-property>
<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>
<script-data-source name="Data Source1" id="8"/>
</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">ORDERNUMBER</property>
<property name="dataType">integer</property>
</structure>
</list-property>
</structure>
<property name="dataSource">Data Source</property>
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">ORDERNUMBER</property>
<property name="nativeName">ORDERNUMBER</property>
<property name="dataType">integer</property>
<property name="nativeDataType">4</property>
</structure>
</list-property>
<property name="queryText">select
CLASSICMODELS.ORDERDETAILS.ORDERNUMBER
from CLASSICMODELS.ORDERDETAILS
where CLASSICMODELS.ORDERDETAILS.ORDERNUMBER = 10101</property>
</oda-data-set>
<script-data-set name="Data Set1" id="9">
<list-property name="resultSetHints">
<structure>
<property name="position">1</property>
<property name="name">on</property>
<property name="dataType">integer</property>
</structure>
</list-property>
<list-property name="columnHints">
<structure>
<property name="columnName">on</property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">on</property>
<property name="dataType">integer</property>
</structure>
</list-property>
</structure>
<property name="dataSource">Data Source1</property>
<method name="open"><![CDATA[icnt = 0;]]></method>
<method name="fetch"><![CDATA[if( icnt < 5 ){
var tst = 222;
tst = icnt;
row["on"] = tst;
icnt++;
return true;
}else{
return false;
}]]></method>
</script-data-set>
<joint-data-set name="Data Set2" id="20">
<list-property name="computedColumns">
<structure>
<property name="name">combined values</property>
<expression name="expression">if( row["Data
Set::ORDERNUMBER"] == null ){
row["Data Set1::on"];
}else{
row["Data Set::ORDERNUMBER"];
}</expression>
<property name="dataType">any</property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">Data
Set::ORDERNUMBER</property>
<property name="dataType">integer</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">Data Set1::on</property>
<property name="dataType">integer</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">combined values</property>
<property name="dataType">any</property>
</structure>
</list-property>
</structure>
<simple-property-list name="dataSets">
<value>Data Set</value>
<value>Data Set1</value>
</simple-property-list>
<list-property name="joinConditions">
<structure>
<property name="joinType">full-out</property>
<property name="joinOperator">eq</property>
<property name="leftDataSet">Data Set</property>
<property name="rightDataSet">Data Set1</property>
<expression
name="leftExpression">dataSetRow["ORDERNUMBER"]</expression >
<expression
name="rightExpression">dataSetRow["on"]</expression>
</structure>
</list-property>
</joint-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>
</report>
Ralf Grewe wrote:
> Hi!
>
> I have the following dataset:
>
> date | severity | count
> 02/2007 | 0 | 45
> 02/2007 | 1 | 56
> 03/2007 | 0 | 42
> 03/2007 | 1 | 12
> 03/2007 | 2 | 32
>
>
> I would like to complete the dataset in the following way:
>
> date | severity | count
> 02/2007 | 0 | 45
> 02/2007 | 1 | 56
> 02/2007 | 2 | 0
> 02/2007 | 3 | 0
> 03/2007 | 0 | 42
> 03/2007 | 1 | 12
> 03/2007 | 2 | 32
> 03/2007 | 3 | 0
>
>
> Is it possible to add these rows to the dataset after getting the data
> from the database?
> I've tried using joins on the database, but it seems that you cannot
> join group-wise.
>
>
> Thanks!
>
|
|
|
| Re: Manually add rows to dataset [message #242870 is a reply to message #242860] |
Fri, 15 June 2007 06:41 |
| Eclipse User |
|
|
|
Originally posted by: rg.openadvice.de
Hi!
Thanks for your help. But I think the suggested solution only works if you
want to complete a table without another group. e.g this table:
Date | Severity | Count
02/2007 | 0 | 34
02/2007 | 1 | 34
02/2007 | 2 | 34
can be completed to the following table with the suggested solution:
Date | Severity | Count
02/2007 | 0 | 34
02/2007 | 1 | 34
02/2007 | 2 | 34
02/2007 | 3 | 34 <-- New
02/2007 | 4 | 34 <-- New
BUT my table is also grouped by the date, e.g:
Date | Severity | Count
02/2007 | 0 | 34
02/2007 | 1 | 34
02/2007 | 2 | 34
03/2007 | 0 | 34
03/2007 | 1 | 34
AND I want to complete both groups: '02/2007' AND '03/2007', e.g.
Date | Severity | Count
02/2007 | 0 | 34
02/2007 | 1 | 34
02/2007 | 2 | 34
02/2007 | 3 | 34 <-- New
02/2007 | 4 | 34 <-- New
03/2007 | 0 | 34
03/2007 | 1 | 34
03/2007 | 2 | 34 <-- New
03/2007 | 3 | 34 <-- New
03/2007 | 4 | 34 <-- New
It seems I've to put a lot of logic into the scripted datasource. Any other
suggestions?
Greetings, Ralf!
|
|
|
| Re: Manually add rows to dataset [message #242989 is a reply to message #242870] |
Sat, 16 June 2007 03:09 |
| Eclipse User |
|
|
|
Originally posted by: jasonweathersby.alltel.net
Ralf,
Why can you take the example I sent you and add two computed columns in
the last step. One for the date and the other for the severity?
This solution is not optimal, but I can not think of another way to do this.
Jason
Ralf Grewe wrote:
> Hi!
>
> Thanks for your help. But I think the suggested solution only works if you
> want to complete a table without another group. e.g this table:
>
> Date | Severity | Count
> 02/2007 | 0 | 34
> 02/2007 | 1 | 34
> 02/2007 | 2 | 34
>
> can be completed to the following table with the suggested solution:
>
> Date | Severity | Count
> 02/2007 | 0 | 34
> 02/2007 | 1 | 34
> 02/2007 | 2 | 34
> 02/2007 | 3 | 34 <-- New
> 02/2007 | 4 | 34 <-- New
>
> BUT my table is also grouped by the date, e.g:
>
> Date | Severity | Count
> 02/2007 | 0 | 34
> 02/2007 | 1 | 34
> 02/2007 | 2 | 34
> 03/2007 | 0 | 34
> 03/2007 | 1 | 34
>
> AND I want to complete both groups: '02/2007' AND '03/2007', e.g.
>
> Date | Severity | Count
> 02/2007 | 0 | 34
> 02/2007 | 1 | 34
> 02/2007 | 2 | 34
> 02/2007 | 3 | 34 <-- New
> 02/2007 | 4 | 34 <-- New
>
> 03/2007 | 0 | 34
> 03/2007 | 1 | 34
> 03/2007 | 2 | 34 <-- New
> 03/2007 | 3 | 34 <-- New
> 03/2007 | 4 | 34 <-- New
>
> It seems I've to put a lot of logic into the scripted datasource. Any other
> suggestions?
>
>
> Greetings, Ralf!
|
|
| | |
| Re: Manually add rows to dataset [message #243240 is a reply to message #243107] |
Tue, 19 June 2007 10:02 |
| Eclipse User |
|
|
|
Originally posted by: rg.openadvice.de
Hi!
First I've created a scripted datasource with three columns ('date',
'severity' and 'countSeverity')and the following script functions:
OPEN:
count = 0;
ggs = new
Packages.org.openadvice.report.data.GetGroupedSeverity("oracle.jdbc.driver.OracleDriver ","jdbc:oracle:thin:@//localhost:1521/Test","Test","Test ");
sg = ggs.getSeverityGroups();
ONFETCH:
if (count < sg.size()){
var gsr = sg.get(count);
row["date"] = gsr.getDate();
row["severity"] = gsr.getSeverity();
row["countSeverity"] = gsr.getCountSeverity();
count ++;
return true;
} else {
return false;
}
CLOSE:
sg = null;
Please read next message for the further steps. It seems like the
newsgroup server does not accept "long" messages.
Greetings,
Ralf
|
|
|
| Re: Manually add rows to dataset [message #243245 is a reply to message #243240] |
Tue, 19 June 2007 10:04 |
| Eclipse User |
|
|
|
Originally posted by: rg.openadvice.de
Then I've created the following java classes and moved the corresponding
JAR to the folder
'\BIRT\plugins\org.eclipse.birt.report.viewer_2.1.2.v2007020 5-1728\birt\scriptlib'
JAVA-class JDBCConnector (responsible for DB connection handling):
package org.openadvice.report.data;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class JDBCConnector {
private String driver;
private String url;
private String username;
private String password;
private Connection cn;
public JDBCConnector(String driver, String url, String username, String
password){
this.driver = driver;
this.url = url;
this.username =username;
this.password = password;
cn = null;
}
public void openConnection() throws Exception {
Class.forName(driver);
cn = DriverManager.getConnection(url, username, password);
}
public void closeConnection() throws Exception {
if (cn != null) cn.close();
}
public ResultSet executeSQL(String sqlString) throws Exception{
Statement st = cn.createStatement();
ResultSet rs = st.executeQuery(sqlString);
return rs;
}
public void showResultSet(ResultSet rs) throws Exception {
ResultSetMetaData rsmd = rs.getMetaData();
int i, n = rsmd.getColumnCount();
for(i=0; i<n; i++)
System.out.print( "+-----------------------------------" );
System.out.println( "+" );
for( i=1; i<=n; i++ ) // Attention: first column with 1
instead
of 0
System.out.print( "| " + extendString( rsmd.getColumnName( i )
)
);
System.out.println( "|" );
for( i=0; i<n; i++ )
System.out.print( "+-----------------------------------" );
System.out.println( "+" );
while( rs.next() ) {
for( i=1; i<=n; i++ ) // Attention: first column with 1
instead
of 0
System.out.print( "| " + extendString( rs.getString( i ) ) );
System.out.println( "|" );
}
for( i=0; i<n; i++ )
System.out.print( "+-----------------------------------" );
System.out.println( "+" );
}
private static final String extendString(String s)
{
if(s == null) s = "";
String sFillStrWithWantLen = " ";
int iWantLen = sFillStrWithWantLen.length();
int iActLen = s.length();
if( iActLen < iWantLen )
return (s + sFillStrWithWantLen).substring( 0, iWantLen );
if( iActLen > 2 * iWantLen )
return s.substring( 0, 2 * iWantLen );
return s;
}
}
|
|
|
| Re: Manually add rows to dataset [message #243250 is a reply to message #243245] |
Tue, 19 June 2007 10:05 |
| Eclipse User |
|
|
|
Originally posted by: rg.openadvice.de
JAVA-class GroupedSeverityRow (represents a single row in my final table):
package org.openadvice.report.data;
public class GroupedSeverityRow {
String date;
int severity;
int countSeverity;
public GroupedSeverityRow(String date, int severity, int countSeverity){
this.date = date;
this.severity = severity;
this.countSeverity = countSeverity;
}
public String getDate() {
return this.date;
}
public int getSeverity() {
return this.severity;
}
public int getCountSeverity() {
return this.countSeverity;
}
public void setDate(String date) {
this.date = date;
}
public void setSeverity(int severity) {
this.severity = severity;
}
public void setCountSeverity(int countSeverity) {
this.countSeverity = countSeverity;
}
}
JAVA-class GetGroupedSeverity (does the desired group-wise extension of my
data table):
package org.openadvice.report.data;
import java.sql.ResultSet;
import java.util.Vector;
public class GetGroupedSeverity {
private JDBCConnector jdbcConnector;
public GetGroupedSeverity(String driver, String url, String username,
String password) {
jdbcConnector = new JDBCConnector(driver, url, username, password);
}
public Vector<GroupedSeverityRow> getSeverityGroups() throws Exception{
jdbcConnector.openConnection();
ResultSet rs = jdbcConnector.executeSQL("select to_char(lastoccurrence,
'MM/YYYY'), severity, count(severity) " +
"from Reporter.Reporter_status " +
"group by to_char(lastoccurrence, 'MM/YYYY'), severity " +
"order by to_char(lastoccurrence, 'MM/YYYY'), severity
asc");
String row_currentDate = "";
String remDate = "";
int sevCounter = 0;
int row_severity;
int row_countSeverity;
GroupedSeverityRow gsr;
Vector<GroupedSeverityRow> resultDataset = new
Vector<GroupedSeverityRow>();
while (rs.next()){
row_currentDate = rs.getString(1);
if (rs.isFirst()) remDate = row_currentDate;
if (!remDate.equals(row_currentDate)) {
while (sevCounter < 6) {
gsr = new GroupedSeverityRow(remDate, sevCounter, 0);
resultDataset.add(gsr);
sevCounter++;
}
remDate = row_currentDate;
sevCounter = 0;
}
row_severity = Integer.parseInt(rs.getString(2));
row_countSeverity = Integer.parseInt(rs.getString(3));
while (row_severity > sevCounter) {
gsr = new GroupedSeverityRow(row_currentDate, sevCounter, 0);
resultDataset.add(gsr);
sevCounter++;
}
gsr = new GroupedSeverityRow(row_currentDate, row_severity,
row_countSeverity);
resultDataset.add(gsr);
sevCounter++;
}
while (sevCounter < 6) {
gsr = new GroupedSeverityRow(remDate, sevCounter, 0);
resultDataset.add(gsr);
sevCounter++;
}
//showResultset(resultDataset);
jdbcConnector.closeConnection();
return(resultDataset);
}
private String extendString(String s) {
if(s == null) s = "";
String sFillStrWithWantLen = " ";
int iWantLen = sFillStrWithWantLen.length();
int iActLen = s.length();
if( iActLen < iWantLen )
return (s + sFillStrWithWantLen).substring( 0, iWantLen );
if( iActLen > 2 * iWantLen )
return s.substring( 0, 2 * iWantLen );
return s;
}
private String extendInt(int i) {
String s = String.valueOf(i);
String sFillStrWithWantLen = " ";
int iWantLen = sFillStrWithWantLen.length();
int iActLen = s.length();
if( iActLen < iWantLen )
return (s + sFillStrWithWantLen).substring( 0, iWantLen );
if( iActLen > 2 * iWantLen )
return s.substring( 0, 2 * iWantLen );
return s;
}
private void showResultset(Vector<GroupedSeverityRow> resultDataset) {
GroupedSeverityRow gsr;
for (int i = 0; i < resultDataset.size(); i++) {
gsr = resultDataset.get(i);
System.out.print(extendString(gsr.getDate()));
System.out.print(extendInt(gsr.getSeverity()));
System.out.println(extendInt(gsr.getCountSeverity()));
}
}
}
Seems to work great *g*
Greetings,
Ralf
|
|
|
| Re: Manually add rows to dataset [message #243371 is a reply to message #243250] |
Tue, 19 June 2007 17:33 |
| Eclipse User |
|
|
|
Originally posted by: jasonweathersby.alltel.net
Thanks Ralf
Ralf Grewe wrote:
> JAVA-class GroupedSeverityRow (represents a single row in my final table):
>
> package org.openadvice.report.data;
>
> public class GroupedSeverityRow {
> String date;
> int severity;
> int countSeverity;
>
> public GroupedSeverityRow(String date, int severity, int
> countSeverity){
> this.date = date;
> this.severity = severity;
> this.countSeverity = countSeverity;
> }
>
> public String getDate() {
> return this.date;
> }
>
>
> public int getSeverity() {
> return this.severity;
> }
>
>
> public int getCountSeverity() {
> return this.countSeverity;
> }
>
>
> public void setDate(String date) {
> this.date = date;
> }
>
>
> public void setSeverity(int severity) {
> this.severity = severity;
> }
>
>
> public void setCountSeverity(int countSeverity) {
> this.countSeverity = countSeverity;
> }
> }
>
>
> JAVA-class GetGroupedSeverity (does the desired group-wise extension of
> my data table):
>
> package org.openadvice.report.data;
>
> import java.sql.ResultSet;
> import java.util.Vector;
>
> public class GetGroupedSeverity {
> private JDBCConnector jdbcConnector;
>
> public GetGroupedSeverity(String driver, String url, String
> username, String password) {
> jdbcConnector = new JDBCConnector(driver, url, username, password);
> }
>
> public Vector<GroupedSeverityRow> getSeverityGroups() throws Exception{
> jdbcConnector.openConnection();
> ResultSet rs = jdbcConnector.executeSQL("select
> to_char(lastoccurrence, 'MM/YYYY'), severity, count(severity) " +
> "from
> Reporter.Reporter_status " +
> "group by
> to_char(lastoccurrence, 'MM/YYYY'), severity " +
> "order by
> to_char(lastoccurrence, 'MM/YYYY'), severity asc");
>
> String row_currentDate = "";
> String remDate = "";
> int sevCounter = 0;
> int row_severity;
> int row_countSeverity;
>
> GroupedSeverityRow gsr;
> Vector<GroupedSeverityRow> resultDataset = new
> Vector<GroupedSeverityRow>();
> while (rs.next()){
> row_currentDate = rs.getString(1);
>
> if (rs.isFirst()) remDate = row_currentDate;
>
> if (!remDate.equals(row_currentDate)) {
> while (sevCounter < 6) {
> gsr = new GroupedSeverityRow(remDate, sevCounter, 0);
> resultDataset.add(gsr);
> sevCounter++;
> }
>
> remDate = row_currentDate;
> sevCounter = 0;
> }
>
> row_severity = Integer.parseInt(rs.getString(2));
> row_countSeverity = Integer.parseInt(rs.getString(3));
>
> while (row_severity > sevCounter) {
> gsr = new GroupedSeverityRow(row_currentDate,
> sevCounter, 0);
> resultDataset.add(gsr);
> sevCounter++;
> }
> gsr = new GroupedSeverityRow(row_currentDate, row_severity,
> row_countSeverity);
> resultDataset.add(gsr);
> sevCounter++;
> }
>
> while (sevCounter < 6) {
> gsr = new GroupedSeverityRow(remDate, sevCounter, 0);
> resultDataset.add(gsr);
> sevCounter++;
> }
>
> //showResultset(resultDataset);
>
> jdbcConnector.closeConnection();
>
> return(resultDataset);
> }
>
>
>
> private String extendString(String s) {
> if(s == null) s = "";
> String sFillStrWithWantLen =
> " ";
> int iWantLen = sFillStrWithWantLen.length();
> int iActLen = s.length();
> if( iActLen < iWantLen )
> return (s + sFillStrWithWantLen).substring( 0, iWantLen );
> if( iActLen > 2 * iWantLen )
> return s.substring( 0, 2 * iWantLen );
> return s;
> }
>
> private String extendInt(int i) {
> String s = String.valueOf(i);
>
> String sFillStrWithWantLen =
> " ";
> int iWantLen = sFillStrWithWantLen.length();
> int iActLen = s.length();
> if( iActLen < iWantLen )
> return (s + sFillStrWithWantLen).substring( 0, iWantLen );
> if( iActLen > 2 * iWantLen )
> return s.substring( 0, 2 * iWantLen );
> return s;
> }
>
>
> private void showResultset(Vector<GroupedSeverityRow> resultDataset) {
>
> GroupedSeverityRow gsr;
> for (int i = 0; i < resultDataset.size(); i++) {
> gsr = resultDataset.get(i);
>
> System.out.print(extendString(gsr.getDate()));
> System.out.print(extendInt(gsr.getSeverity()));
> System.out.println(extendInt(gsr.getCountSeverity()));
> }
> }
> }
>
>
> Seems to work great *g*
>
> Greetings,
>
> Ralf
>
|
|
|
Goto Forum:
Current Time: Wed Sep 18 16:52:23 GMT 2024
Powered by FUDForum. Page generated in 0.04223 seconds |
] 
Search
Help
Register
Login
Home
