| Values in dataset to be used as input for another dataset [message #261986] |
Fri, 23 November 2007 04:09  |
Eclipse User |
|
|
|
Originally posted by: alpc.geo.yahoo.com
Is there a way to use some values from 1 dataset (which returns only 1 row
of data) to be used as input for another dataset? The required values from
the 1st dataset does not appear on the report, therefore, I could not
follow what was shown in subreport example.
E.g. 1st dataset returns DocNo and Date. I want to be able to use DocNo as
an input for 2nd dataset.
The report builder is confined to 2.1.0 version.
|
|
|
|
|
|
|
| Re: Values in dataset to be used as input for another dataset [message #262576 is a reply to message #262532] |
Mon, 03 December 2007 17:02  |
| Eclipse User |
|
|
|
Originally posted by: johnw.innoventsolutions.com
This is a multi-part message in MIME format.
--------------090504010902020909000404
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
Yes, you can use a "value" from a query to drive another query. Results
aren't actually retrieved until a bound element is called int he report
generation. If you create a table with the outer query, and in one of
the detail rows, embed the inner query, you can use the the onRender of
the outer table to set the parameter. I have attached a report design
that demonstrated this. Look at the onRender method for the table.
A. L. wrote:
> I am trying to see if I could use global variable - set the variable
> after the retrieval of data of 1st dataset, and then before opening the
> 2nd dataset, set the parameter of 2nd dataset to the global variable.
>
> However, I am having some issues with my scripting and well, being
> rather new to BIRT, I am struggling to figure out how to do this (or
> resolve the scripting issue).
>
--------------090504010902020909000404
Content-Type: text/xml;
name="Image on Last Page.rptdesign"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="Image on Last Page.rptdesign"
<?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.1.r22a_v20071101 Build <2.2.1.v20071101-1439></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>
<parameters>
<scalar-parameter name="param_1" id="47">
<property name="valueType">static</property>
<property name="dataType">integer</property>
<property name="paramType">simple</property>
<property name="isRequired">true</property>
<property name="controlType">text-box</property>
<property name="distinct">true</property>
<structure name="format">
<property name="category">Unformatted</property>
</structure>
</scalar-parameter>
</parameters>
<data-sources>
<oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Data Source" id="44">
<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="CustomerTable" id="45">
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">CUSTOMERNUMBER</property>
<property name="dataType">integer</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">CUSTOMERNAME</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">CUSTOMERNUMBER</property>
<property name="nativeName">CUSTOMERNUMBER</property>
<property name="dataType">integer</property>
<property name="nativeDataType">4</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">CUSTOMERNAME</property>
<property name="nativeName">CUSTOMERNAME</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
</list-property>
<property name="queryText">select CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER,
CLASSICMODELS.CUSTOMERS.CUSTOMERNAME
from CLASSICMODELS.CUSTOMERS</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>CUSTOMERNUMBER</design:name>
<design:position>1</design:position>
<design:nativeDataTypeCode>4</design:nativeDataTypeCode>
<design:precision>10</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>CUSTOMERNUMBER</design:label>
<design:formattingHints>
<design:displaySize>11</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>CUSTOMERNAME</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>CUSTOMERNAME</design:label>
<design:formattingHints>
<design:displaySize>50</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
</design:resultSetColumns>
</design:resultSetDefinitions>
</design:ResultSets>
</model:DesignValues>]]></xml-property>
</oda-data-set>
<oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet " name="OrderTable" id="46">
<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="parameters">
<structure>
<property name="name">param_1</property>
<property name="paramName">param_1</property>
<property name="nativeName"></property>
<property name="dataType">integer</property>
<property name="nativeDataType">4</property>
<property name="position">1</property>
<property name="isOptional">false</property>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
</list-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.ORDERS.ORDERNUMBER
from CLASSICMODELS.ORDERS
where
CLASSICMODELS.ORDERS.CUSTOMERNUMBER = ?</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:DataSetParameters>
<design:parameterDefinitions>
<design:inOutMode>In</design:inOutMode>
<design:attributes>
<design:name></design:name>
<design:position>1</design:position>
<design:nativeDataTypeCode>4</design:nativeDataTypeCode>
<design:nullability>Unknown</design:nullability>
<design:uiHints/>
</design:attributes>
<design:inputAttributes>
<design:elementAttributes>
<design:optional>false</design:optional>
<design:masksValue>false</design:masksValue>
<design:uiHints>
<design:promptStyle>TextField</design:promptStyle>
</design:uiHints>
</design:elementAttributes>
</design:inputAttributes>
</design:parameterDefinitions>
</design:DataSetParameters>
<design:ResultSets derivedMetaData="true">
<design:resultSetDefinitions>
<design:resultSetColumns>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>ORDERNUMBER</design:name>
<design:position>1</design:position>
<design:nativeDataTypeCode>4</design:nativeDataTypeCode>
<design:precision>10</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:usageHints>
<design:label>ORDERNUMBER</design:label>
<design:formattingHints>
<design:displaySize>11</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
</design:resultSetColumns>
</design:resultSetDefinitions>
</design:ResultSets>
</model:DesignValues>]]></xml-property>
</oda-data-set>
</data-sets>
<page-setup>
<simple-master-page name="Simple MasterPage" id="2"/>
</page-setup>
<body>
<table id="48">
<property name="height">1.5416666667in</property>
<property name="width">5.4166666667in</property>
<property name="dataSet">CustomerTable</property>
<list-property name="boundDataColumns">
<structure>
<property name="name">CUSTOMERNUMBER</property>
<expression name="expression">dataSetRow["CUSTOMERNUMBER"]</expression >
<property name="dataType">integer</property>
</structure>
<structure>
<property name="name">CUSTOMERNAME</property>
<expression name="expression">dataSetRow["CUSTOMERNAME"]</expression >
<property name="dataType">string</property>
</structure>
</list-property>
<column id="62">
<property name="width">2.4166666667in</property>
</column>
<column id="63">
<property name="width">3in</property>
</column>
<header>
<row id="49">
<cell id="50"/>
<cell id="52"/>
</row>
</header>
<detail>
<row id="54">
<method name="onRender"><![CDATA[params["param_1"] = row["CUSTOMERNUMBER"]]]></method>
<cell id="55">
<data id="58">
<property name="resultSetColumn">CUSTOMERNAME</property>
</data>
</cell>
<cell id="57"/>
</row>
<row id="64">
<cell id="65"/>
<cell id="66">
<table id="67">
<property name="width">100%</property>
<property name="dataSet">OrderTable</property>
<list-property name="boundDataColumns">
<structure>
<property name="name">ORDERNUMBER</property>
<expression name="expression">dataSetRow["ORDERNUMBER"]</expression >
<property name="dataType">integer</property>
</structure>
</list-property>
<column id="76"/>
<header>
<row id="68">
<cell id="69"/>
</row>
</header>
<detail>
<row id="71">
<cell id="72">
<data id="73">
<property name="resultSetColumn">ORDERNUMBER</property>
</data>
</cell>
</row>
</detail>
<footer>
<row id="74">
<cell id="75"/>
</row>
</footer>
</table>
</cell>
</row>
</detail>
<footer>
<row id="59">
<cell id="60"/>
<cell id="61"/>
</row>
</footer>
</table>
</body>
</report>
--------------090504010902020909000404--
|
|
|
|
| Re: Values in dataset to be used as input for another dataset [message #262600 is a reply to message #262585] |
Tue, 04 December 2007 01:47 |
| Eclipse User |
|
|
|
Originally posted by: johnw.innoventsolutions.com
A. L. wrote:
> John Ward wrote:
>
>> Yes, you can use a "value" from a query to drive another query.
>> Results aren't actually retrieved until a bound element is called int
>> he report generation. If you create a table with the outer query, and
>> in one of the detail rows, embed the inner query, you can use the the
>> onRender of the outer table to set the parameter. I have attached a
>> report design that demonstrated this. Look at the onRender method for
>> the table.
>
> Hi John,
> Thanks. I am trying to view the report design but I am getting some
> error on the xml source - unhandled event loop exception and
> java.lang.nullpointerexception error - so it will take awhile to figure
> out how you do it.
>
To help out, what I did in that report is I embedded the table
outputting one data set into a parent table for the driving data set. In
the onRender method (the onCreate method could be used also), I set a
global parameter which is set to hidden and not required, using the
following script:
params["param_1"] = row["CUSTOMERNUMBER"]
where param_1 is a report parameter that is bound to my child dataset,
and CUSTOMERNUMBER is a foreign key in the child table. Now, the outer
table, which displays a list of customers, will populate the current
customer number into the child table.
There are several different ways to do this similar task, but this is
probably the easiest.
John
|
|
|
Powered by
FUDForum. Page generated in 0.02868 seconds
Home