nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to dynamicly concat sql to an other dataset

LeinadJan · 2010-08-04T16:13:06+00:00

Hi, I must create a report where a function is generating an SQL query that must be used in the where clause of an other dataset. For some reasons, I can't call directly my function inside a single dataset example : select c1, c2, c3,…

LeinadJan

Hi, 
 
I must create a report where a function is generating an SQL query that must be used in the where clause of an other dataset. 
 
For some reasons, I can't call directly my function inside a single dataset example : 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>select c1, c2, c3, c4 from table1 where c1 in (my_function(param1,param2,param3)</pre>
 
my_function is returning an SQL query and it's highly dynamic. I can't predict the result. I'm using it for my web application and it'S working well. 
 
Now, I tried to create a second dataset which will contain the function call and return the SQL query. Then I want add it in a script beforeOpen. 
 
Something like that : 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>this.queryText = "select c1, c2, c3, c4 from table1 where c1 in (" + dataset2.row["TEXT"] + ")"</pre>
 
Can I do something like that, or do you have a better solution ? 
 
Thank you 
 
Leinad

Find more posts tagged with

Comments

johnw

So a SQL query is returned as the TEXT field in query 1? 
 
What you might want to consider is setting a global variable with the result of the query, and drop in a hidden table. Then you use property binding in the dependent query. So in query 1, you'd do something like: 
 
//either in an onFetch event, or in a DATA report item in your hidden table 
reportContext.setGlobalVariable("queryResult", dataset2.row["TEXT"]); 
/////////////////////////////// 
 
Then, in query 2, you would use the property binding tab and use this expression: 
 
///////////////////////////////// 
var sql = "select c1, c2, c3, c4 from table1 where c1 in (" + reportContext.getGlobalVariable("queryResult") + ")"; 
 
sql; 
//////////////////////////////// 
 
You can also consider using query 1 as a dynamic parameter, with the value and display text return the result values. a little scrpting might be in order to do this, but this will foce query 1 to run before anything else, and you can retrieve the values using a ParameterExtractionTask. 
 
 
 
<blockquote class='ipsBlockquote' data-author="'LeinadJan'" data-cid="67061" data-time="1280952786" data-date="04 August 2010 - 01:13 PM">
Hi, 
 
I must create a report where a function is generating an SQL query that must be used in the where clause of an other dataset. 
 
For some reasons, I can't call directly my function inside a single dataset example : 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>select c1, c2, c3, c4 from table1 where c1 in (my_function(param1,param2,param3)</pre>
 
my_function is returning an SQL query and it's highly dynamic. I can't predict the result. I'm using it for my web application and it'S working well. 
 
Now, I tried to create a second dataset which will contain the function call and return the SQL query. Then I want add it in a script beforeOpen. 
 
Something like that : 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>this.queryText = "select c1, c2, c3, c4 from table1 where c1 in (" + dataset2.row["TEXT"] + ")"</pre>
 
Can I do something like that, or do you have a better solution ? 
 
Thank you 
 
Leinad </blockquote>

LeinadJan

Hi John, 
 
Yes, my function is returning an text SQL query. Originally, this function is used in an Oracle Application Express state region. This function is called and the returning text is executed. 
 
So, if I understand you correctly, I must put the SQL string into a global variable in the onFetch event? Then I can use the global variable inside my first query, and BIRT would execute it like I would have writen it directly in dataset's source. 
 
I'll try that. But I need to know if there is an order to follow. Are datasets runs in a particular order ? 
 
Thank you ! 
 
 
 
<blockquote class='ipsBlockquote' data-author="'johnw'" data-cid="67070" data-time="1280993967" data-date="05 August 2010 - 12:39 AM">
So a SQL query is returned as the TEXT field in query 1? 
 
What you might want to consider is setting a global variable with the result of the query, and drop in a hidden table. Then you use property binding in the dependent query. So in query 1, you'd do something like: 
 
//either in an onFetch event, or in a DATA report item in your hidden table 
reportContext.setGlobalVariable("queryResult", dataset2.row["TEXT"]); 
/////////////////////////////// 
 
Then, in query 2, you would use the property binding tab and use this expression: 
 
///////////////////////////////// 
var sql = "select c1, c2, c3, c4 from table1 where c1 in (" + reportContext.getGlobalVariable("queryResult") + ")"; 
 
sql; 
//////////////////////////////// 
 
You can also consider using query 1 as a dynamic parameter, with the value and display text return the result values. a little scrpting might be in order to do this, but this will foce query 1 to run before anything else, and you can retrieve the values using a ParameterExtractionTask. </blockquote>
 
 
Ok, I was able to obtain the SQLquery inside the global variable, but it seems that I still can't run it. 
 
Why that in the property binding ? Why not in queryText ? 
<pre class='_prettyXprint _lang-auto _linenums:0'>/////////////////////////////////
var sql = "select c1, c2, c3, c4 from table1 where c1 in (" + reportContext.getGlobalVariable("queryResult") + ")";

sql;
////////////////////////////////</pre>
 
Thanks again !

LeinadJan

Hello again, 
 
I do not understand why my sub-query can't be executed correctly ? My first test was to see if the text was really return inside my main dataset. 
 
I used : 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
this.queryText = this.queryText + " where '" + reportContext.getGlobalVariable("queryResult") + "' is not null";
</pre>
 
this works fine. 
 
When I'm doing : 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
this.queryText = this.queryText + " where c1 in ( select a.c1 from (" + reportContext.getGlobalVariable("queryResult") + ") a )";
</pre>
 
I get an error message in the preview 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Table (id = 72):
+ Cannot get the result set metadata.
SQL statement does not return a ResultSet object.
SQL error #1: ORA-00903: invalid table name
</pre>
 
So, I checked if there was something wrong in the table name that BIRT could not understand in the generated query. I created a new dataset and put the result query in the source. Everything is working. 
 
Why it can't simply runs it as a single sql statement ? 
 
Do you have an idea on what I should do ?

if I put it in a single query, it works fine. I need a way to make BIRT understand my query as a single entity, and not a query in two parts. 
 
I try to put the whole query in the Data Binding option for the dataset, but it says the following error : 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>A BIRT exception occurred.
Plug-in Provider:Eclipse.org
Plug-in Name:BIRT Data Engine
Plug-in ID:org.eclipse.birt.data
Version:2.1.1.v20060922-1058
Error Code:data.engine.BirtException
Error Message:A BIRT exception occurred: Error evaluating Javascript expression. Script engine error: TypeError: getGlobalVariable is not a function (DataSet[DataSet2].__bm_beforeOpen#8)
Script source: DataSet[DataSet2].__bm_beforeOpen, line: 1, text:
__bm_beforeOpen(). See next exception for more information.
Error evaluating Javascript expression. Script engine error: TypeError: getGlobalVariable is not a function (DataSet[DataSet2].__bm_beforeOpen#8)
Script source: DataSet[DataSet2].__bm_beforeOpen, line: 1, text:
__bm_beforeOpen()
</pre>
 
Look at my screenshot, is it the right place to put the binding ? 
 
I try to send a query with a parameter, but the result is the same. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>Table (id = 72):
+ Cannot get the result set metadata.
SQL statement does not return a ResultSet object.
SQL error #1: ORA-01722: invalid number</pre>
 
 
Leinad

johnw

1: be sure to drop the data set onto your report, and set the visibility option to true to hide it. Data sets are not retrieved until they are used in a report.

2: Once you have your table in the report, drop a random Data element after that table, and set the expression to 'reportContext.getGlobalVariable("queryResult")'. This will show you the value of what is going to be used in your child data set.

3: We need to move that to the onBeforeOpen event, and eliminate the Property Binding. It works fine with report parameters, but I guess it doesn't quite work with global variables.

I have attached an example that does what your looking for. In it I use a scripted data source to return a select statement, then in my child table, I am appending it in the onBeforeOpen event.

John

johnw

Not sure why, but the upload attachment is failing. Below is the XML source.

<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.17" id="1">
<property name="createdBy">Eclipse BIRT Designer Version 2.3.2.r232_v20090521 Build <2.3.2.v20090601-0700></property>
<property name="units">in</property>
<property name="iconFile">/templates/blank_report.gif</property>
<property name="bidiLayoutOrientation">ltr</property>
<data-sources>
<script-data-source name="srcSomeQuery" id="7"/>
<oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Data Source" id="20">
<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>
<property name="OdaConnProfileName"></property>
</oda-data-source>
</data-sources>
<data-sets>
<script-data-set name="setSomeQuery" id="8">
<list-property name="resultSetHints">
<structure>
<property name="position">0</property>
<property name="name">queryResult</property>
<property name="dataType">string</property>
</structure>
</list-property>
<list-property name="columnHints">
<structure>
<property name="columnName">queryResult</property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">queryResult</property>
<property name="dataType">string</property>
</structure>
</list-property>
</structure>
<property name="dataSource">srcSomeQuery</property>
<method name="onFetch"><![CDATA[reportContext.setGlobalVariable("queryResult", row["queryResult"]);]]></method>
<method name="open"><![CDATA[cnt = 0;]]></method>
<method name="fetch"><![CDATA[if (cnt < 1)
{
row["queryResult"] = "select employeenumber from employees";

cnt++;

return ( true );
}

return ( false );]]></method>
</script-data-set>
<oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" name="Data Set" id="21">
<list-property name="columnHints">
<structure>
<property name="columnName">CUSTOMERNUMBER</property>
<property name="displayName">CUSTOMERNUMBER</property>
</structure>
<structure>
<property name="columnName">CUSTOMERNAME</property>
<property name="displayName">CUSTOMERNAME</property>
</structure>
<structure>
<property name="columnName">CONTACTLASTNAME</property>
<property name="displayName">CONTACTLASTNAME</property>
</structure>
<structure>
<property name="columnName">CONTACTFIRSTNAME</property>
<property name="displayName">CONTACTFIRSTNAME</property>
</structure>
<structure>
<property name="columnName">PHONE</property>
<property name="displayName">PHONE</property>
</structure>
<structure>
<property name="columnName">ADDRESSLINE1</property>
<property name="displayName">ADDRESSLINE1</property>
</structure>
<structure>
<property name="columnName">ADDRESSLINE2</property>
<property name="displayName">ADDRESSLINE2</property>
</structure>
<structure>
<property name="columnName">CITY</property>
<property name="displayName">CITY</property>
</structure>
<structure>
<property name="columnName">STATE</property>
<property name="displayName">STATE</property>
</structure>
<structure>
<property name="columnName">POSTALCODE</property>
<property name="displayName">POSTALCODE</property>
</structure>
<structure>
<property name="columnName">COUNTRY</property>
<property name="displayName">COUNTRY</property>
</structure>
<structure>
<property name="columnName">SALESREPEMPLOYEENUMBER</property>
<property name="displayName">SALESREPEMPLOYEENUMBER</property>
</structure>
<structure>
<property name="columnName">CREDITLIMIT</property>
<property name="displayName">CREDITLIMIT</property>
</structure>
</list-property>
<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>
<structure>
<property name="position">3</property>
<property name="name">CONTACTLASTNAME</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">CONTACTFIRSTNAME</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">5</property>
<property name="name">PHONE</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">6</property>
<property name="name">ADDRESSLINE1</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">7</property>
<property name="name">ADDRESSLINE2</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">8</property>
<property name="name">CITY</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">9</property>
<property name="name">STATE</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">10</property>
<property name="name">POSTALCODE</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">11</property>
<property name="name">COUNTRY</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">12</property>
<property name="name">SALESREPEMPLOYEENUMBER</property>
<property name="dataType">integer</property>
</structure>
<structure>
<property name="position">13</property>
<property name="name">CREDITLIMIT</property>
<property name="dataType">float</property>
</structure>
</list-property>
</structure>
<property name="dataSource">Data Source</property>
<method name="beforeOpen"><![CDATA[this.queryText = this.queryText + " where CUSTOMERS.SALESREPEMPLOYEENUMBER in (" +reportContext.getGlobalVariable("queryResult") + ")";]]></method>
<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>
<structure>
<property name="position">3</property>
<property name="name">CONTACTLASTNAME</property>
<property name="nativeName">CONTACTLASTNAME</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">CONTACTFIRSTNAME</property>
<property name="nativeName">CONTACTFIRSTNAME</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">5</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">6</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">7</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">8</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">9</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">10</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">11</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">12</property>
<property name="name">SALESREPEMPLOYEENUMBER</property>
<property name="nativeName">SALESREPEMPLOYEENUMBER</property>
<property name="dataType">integer</property>
<property name="nativeDataType">4</property>
</structure>
<structure>
<property name="position">13</property>
<property name="name">CREDITLIMIT</property>
<property name="nativeName">CREDITLIMIT</property>
<property name="dataType">float</property>
<property name="nativeDataType">8</property>
</structure>
</list-property>
<property name="queryText">select
*
from
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:uiHints>
<design:displayName>CUSTOMERNUMBER</design:displayName>
</design:uiHints>
</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:uiHints>
<design:displayName>CUSTOMERNAME</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>CUSTOMERNAME</design:label>
<design:formattingHints>
<design:displaySize>50</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>CONTACTLASTNAME</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:uiHints>
<design:displayName>CONTACTLASTNAME</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>CONTACTLASTNAME</design:label>
<design:formattingHints>
<design:displaySize>50</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>CONTACTFIRSTNAME</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:uiHints>
<design:displayName>CONTACTFIRSTNAME</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>CONTACTFIRSTNAME</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>5</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>50</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>PHONE</design:displayName>
</design:uiHints>
</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>6</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>50</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>ADDRESSLINE1</design:displayName>
</design:uiHints>
</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>7</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>50</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>ADDRESSLINE2</design:displayName>
</design:uiHints>
</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>CITY</design:name>
<design:position>8</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>50</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>CITY</design:displayName>
</design:uiHints>
</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>STATE</design:name>
<design:position>9</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>50</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>STATE</design:displayName>
</design:uiHints>
</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>POSTALCODE</design:name>
<design:position>10</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>15</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>POSTALCODE</design:displayName>
</design:uiHints>
</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>COUNTRY</design:name>
<design:position>11</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>50</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>COUNTRY</design:displayName>
</design:uiHints>
</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>SALESREPEMPLOYEENUMBER</design:name>
<design:position>12</design:position>
<design:nativeDataTypeCode>4</design:nativeDataTypeCode>
<design:precision>10</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>SALESREPEMPLOYEENUMBER</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>SALESREPEMPLOYEENUMBER</design:label>
<design:formattingHints>
<design:displaySize>11</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>CREDITLIMIT</design:name>
<design:position>13</design:position>
<design:nativeDataTypeCode>8</design:nativeDataTypeCode>
<design:precision>15</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>CREDITLIMIT</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>CREDITLIMIT</design:label>
<design:formattingHints>
<design:displaySize>22</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="report" id="4">
<property name="fontFamily">"Verdana"</property>
<property name="fontSize">10pt</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>
<style name="crosstab" id="6">
<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">
<property name="topMargin">0.25in</property>
<property name="leftMargin">0.25in</property>
<property name="bottomMargin">0.25in</property>
<property name="rightMargin">0.25in</property>
<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="9">
<property name="width">100%</property>
<property name="dataSet">setSomeQuery</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">queryResult</property>
<property name="displayName">queryResult</property>
<expression name="expression">dataSetRow["queryResult"]</expression>
<property name="dataType">string</property>
</structure>
</list-property>
<column id="18"/>
<header>
<row id="10">
<cell id="11">
<label id="12">
<text-property name="text">queryResult</text-property>
</label>
</cell>
</row>
</header>
<detail>
<row id="13">
<cell id="14">
<data id="15">
<property name="resultSetColumn">queryResult</property>
</data>
</cell>
</row>
</detail>
<footer>
<row id="16">
<cell id="17"/>
</row>
</footer>
</table>
<data id="19">
<list-property name="boundDataColumns">
<structure>
<property name="name">Column Binding</property>
<expression name="expression">reportContext.getGlobalVariable("queryResult");</expression>
<property name="dataType">string</property>
</structure>
</list-property>
<property name="resultSetColumn">Column Binding</property>
</data>
<table id="22">
<property name="width">100%</property>
<property name="dataSet">Data Set</property>
<list-property name="boundDataColumns">
<structure>
<property name="name">CUSTOMERNUMBER</property>
<property name="displayName">CUSTOMERNUMBER</property>
<expression name="expression">dataSetRow["CUSTOMERNUMBER"]</expression>
<property name="dataType">integer</property>
</structure>
<structure>
<property name="name">CUSTOMERNAME</property>
<property name="displayName">CUSTOMERNAME</property>
<expression name="expression">dataSetRow["CUSTOMERNAME"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">CONTACTLASTNAME</property>
<property name="displayName">CONTACTLASTNAME</property>
<expression name="expression">dataSetRow["CONTACTLASTNAME"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">CONTACTFIRSTNAME</property>
<property name="displayName">CONTACTFIRSTNAME</property>
<expression name="expression">dataSetRow["CONTACTFIRSTNAME"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">PHONE</property>
<property name="displayName">PHONE</property>
<expression name="expression">dataSetRow["PHONE"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">ADDRESSLINE1</property>
<property name="displayName">ADDRESSLINE1</property>
<expression name="expression">dataSetRow["ADDRESSLINE1"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">ADDRESSLINE2</property>
<property name="displayName">ADDRESSLINE2</property>
<expression name="expression">dataSetRow["ADDRESSLINE2"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">CITY</property>
<property name="displayName">CITY</property>
<expression name="expression">dataSetRow["CITY"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">STATE</property>
<property name="displayName">STATE</property>
<expression name="expression">dataSetRow["STATE"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">POSTALCODE</property>
<property name="displayName">POSTALCODE</property>
<expression name="expression">dataSetRow["POSTALCODE"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">COUNTRY</property>
<property name="displayName">COUNTRY</property>
<expression name="expression">dataSetRow["COUNTRY"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">SALESREPEMPLOYEENUMBER</property>
<property name="displayName">SALESREPEMPLOYEENUMBER</property>
<expression name="expression">dataSetRow["SALESREPEMPLOYEENUMBER"]</expression>
<property name="dataType">integer</property>
</structure>
<structure>
<property name="name">CREDITLIMIT</property>
<property name="displayName">CREDITLIMIT</property>
<expression name="expression">dataSetRow["CREDITLIMIT"]</expression>
<property name="dataType">float</property>
</structure>
</list-property>
<column id="91"/>
<column id="92"/>
<column id="101"/>
<column id="102"/>
<column id="103"/>
<header>
<row id="23">
<cell id="24">
<label id="25">
<text-property name="text">CUSTOMERNUMBER</text-property>
</label>
</cell>
<cell id="26">
<label id="27">
<text-property name="text">CUSTOMERNAME</text-property>
</label>
</cell>
<cell id="44">
<label id="45">
<text-property name="text">COUNTRY</text-property>
</label>
</cell>
<cell id="46">
<label id="47">
<text-property name="text">SALESREPEMPLOYEENUMBER</text-property>
</label>
</cell>
<cell id="48">
<label id="49">
<text-property name="text">CREDITLIMIT</text-property>
</label>
</cell>
</row>
</header>
<detail>
<row id="50">
<cell id="51">
<data id="52">
<property name="resultSetColumn">CUSTOMERNUMBER</property>
</data>
</cell>
<cell id="53">
<data id="54">
<property name="resultSetColumn">CUSTOMERNAME</property>
</data>
</cell>
<cell id="71">
<data id="72">
<property name="resultSetColumn">COUNTRY</property>
</data>
</cell>
<cell id="73">
<data id="74">
<property name="resultSetColumn">SALESREPEMPLOYEENUMBER</property>
</data>
</cell>
<cell id="75">
<data id="76">
<property name="resultSetColumn">CREDITLIMIT</property>
</data>
</cell>
</row>
</detail>
<footer>
<row id="77">
<cell id="78"/>
<cell id="79"/>
<cell id="88"/>
<cell id="89"/>
<cell id="90"/>
</row>
</footer>
</table>
</body>
</report>

LeinadJan

Thank you John, I'll try that. Thought I still had the two dataset shown in my test report and it wasn't working. I'll try again.