Eclipse Community Forums: BIRT » How to display data filtered by Datetime

Help

Home

Home » Archived » BIRT » How to display data filtered by Datetime

Show: Today's Messages :: Show Polls :: Message Navigator

How to display data filtered by Datetime [message #480558]

Mon, 17 August 2009 15:44

Evgeny

Messages: 19
Registered: July 2009

Junior Member

Hi and thank you for reading,

I have a list of orders by different customers. With the help of a Cross
Tab I have counted the number of orders per customer and created a list
with the top customers in descending order.

Now I would like to let the user filter this data by date. This means I
would like the user to choose DateTimes in between of which this top
customer list should be calculated. I tried to do this with the "Report
Parameter"and then a Filter for this Report Parameter, but the problem is
that there is no option to select the OrderDateTime in the Filtering
dialog.

My OrderDateTime is in the same table as the Orders and Customers of this
Orders. How can I choose this column for my filter in order to filter the
top customers?

Thanks a lot for all help

Evgeny

Report message to a moderator

Re: How to display data filtered by Datetime [message #480811 is a reply to message #480558]

Tue, 18 August 2009 14:57

Jason Weathersby

Messages: 9167
Registered: July 2009

Senior Member

Evgeny,

You should be able to use a report parameter to modify the dataset that
feeds the cube. Take a look at the attached example.

Jason

<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.20"
id="1">
<property name="createdBy">Eclipse BIRT Designer Version
2.5.0.v20090603 Build <2.5.0.v20090617-0630></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>
<property name="layoutPreference">auto layout</property>
<parameters>
<scalar-parameter name="beginDate" id="556">
<property name="valueType">static</property>
<property name="dataType">date</property>
<simple-property-list name="defaultValue">
<value type="constant">2003-01-01</value>
</simple-property-list>
<property name="paramType">simple</property>
<property name="controlType">text-box</property>
<property name="distinct">true</property>
<structure name="format">
<property name="category">Unformatted</property>
</structure>
</scalar-parameter>
<scalar-parameter name="endDate" id="557">
<property name="valueType">static</property>
<property name="dataType">date</property>
<simple-property-list name="defaultValue">
<value type="constant">2004-12-12</value>
</simple-property-list>
<property name="paramType">simple</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="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="customers" id="8">
<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>
<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>
<xml-property name="queryText"><![CDATA[select *
from customers]]></xml-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>
<oda-data-set
extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet "
name="dsOrders" id="9">
<list-property name="columnHints">
<structure>
<property name="columnName">ORDERNUMBER</property>
<property name="displayName">ORDERNUMBER</property>
</structure>
<structure>
<property name="columnName">PRODUCTCODE</property>
<property name="displayName">PRODUCTCODE</property>
</structure>
<structure>
<property name="columnName">QUANTITYORDERED</property>
<property name="displayName">QUANTITYORDERED</property>
</structure>
<structure>
<property name="columnName">PRICEEACH</property>
<property name="displayName">PRICEEACH</property>
</structure>
<structure>
<property name="columnName">ORDERLINENUMBER</property>
<property name="displayName">ORDERLINENUMBER</property>
</structure>
<structure>
<property name="columnName">ORDERNUMBER_6</property>
<property name="displayName">ORDERNUMBER</property>
</structure>
<structure>
<property name="columnName">ORDERDATE</property>
<property name="displayName">ORDERDATE</property>
</structure>
<structure>
<property name="columnName">REQUIREDDATE</property>
<property name="displayName">REQUIREDDATE</property>
</structure>
<structure>
<property name="columnName">SHIPPEDDATE</property>
<property name="displayName">SHIPPEDDATE</property>
</structure>
<structure>
<property name="columnName">STATUS</property>
<property name="displayName">STATUS</property>
</structure>
<structure>
<property name="columnName">COMMENTS</property>
<property name="displayName">COMMENTS</property>
</structure>
<structure>
<property name="columnName">CUSTOMERNUMBER</property>
<property name="displayName">CUSTOMERNUMBER</property>
</structure>
</list-property>
<list-property name="parameters">
<structure>
<property name="name">param_1</property>
<property name="paramName">beginDate</property>
<property name="nativeName"></property>
<property name="dataType">date</property>
<property name="nativeDataType">91</property>
<property name="position">1</property>
<property name="isOptional">true</property>
<property name="allowNull">true</property>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
<structure>
<property name="name">param_2</property>
<property name="paramName">endDate</property>
<property name="nativeName"></property>
<property name="dataType">date</property>
<property name="nativeDataType">91</property>
<property name="position">2</property>
<property name="isOptional">true</property>
<property name="allowNull">true</property>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">ORDERNUMBER</property>
<property name="dataType">integer</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">PRODUCTCODE</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">QUANTITYORDERED</property>
<property name="dataType">integer</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">PRICEEACH</property>
<property name="dataType">float</property>
</structure>
<structure>
<property name="position">5</property>
<property name="name">ORDERLINENUMBER</property>
<property name="dataType">integer</property>
</structure>
<structure>
<property name="position">6</property>
<property name="name">ORDERNUMBER_6</property>
<property name="dataType">integer</property>
</structure>
<structure>
<property name="position">7</property>
<property name="name">ORDERDATE</property>
<property name="dataType">date</property>
</structure>
<structure>
<property name="position">8</property>
<property name="name">REQUIREDDATE</property>
<property name="dataType">date</property>
</structure>
<structure>
<property name="position">9</property>
<property name="name">SHIPPEDDATE</property>
<property name="dataType">date</property>
</structure>
<structure>
<property name="position">10</property>
<property name="name">STATUS</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">11</property>
<property name="name">COMMENTS</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">12</property>
<property name="name">CUSTOMERNUMBER</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>
<structure>
<property name="position">2</property>
<property name="name">PRODUCTCODE</property>
<property name="nativeName">PRODUCTCODE</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">QUANTITYORDERED</property>
<property name="nativeName">QUANTITYORDERED</property>
<property name="dataType">integer</property>
<property name="nativeDataType">4</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">PRICEEACH</property>
<property name="nativeName">PRICEEACH</property>
<property name="dataType">float</property>
<property name="nativeDataType">8</property>
</structure>
<structure>
<property name="position">5</property>
<property name="name">ORDERLINENUMBER</property>
<property name="nativeName">ORDERLINENUMBER</property>
<property name="dataType">integer</property>
<property name="nativeDataType">5</property>
</structure>
<structure>
<property name="position">6</property>
<property name="name">ORDERNUMBER_6</property>
<property name="nativeName">ORDERNUMBER</property>
<property name="dataType">integer</property>
<property name="nativeDataType">4</property>
</structure>
<structure>
<property name="position">7</property>
<property name="name">ORDERDATE</property>
<property name="nativeName">ORDERDATE</property>
<property name="dataType">date</property>
<property name="nativeDataType">91</property>
</structure>
<structure>
<property name="position">8</property>
<property name="name">REQUIREDDATE</property>
<property name="nativeName">REQUIREDDATE</property>
<property name="dataType">date</property>
<property name="nativeDataType">91</property>
</structure>
<structure>
<property name="position">9</property>
<property name="name">SHIPPEDDATE</property>
<property name="nativeName">SHIPPEDDATE</property>
<property name="dataType">date</property>
<property name="nativeDataType">91</property>
</structure>
<structure>
<property name="position">10</property>
<property name="name">STATUS</property>
<property name="nativeName">STATUS</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">11</property>
<property name="name">COMMENTS</property>
<property name="nativeName">COMMENTS</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">12</property>
<property name="name">CUSTOMERNUMBER</property>
<property name="nativeName">CUSTOMERNUMBER</property>
<property name="dataType">integer</property>
<property name="nativeDataType">4</property>
</structure>
</list-property>
<xml-property name="queryText"><![CDATA[select *
from CLASSICMODELS.ORDERDETAILS, CLASSICMODELS.ORDERS
where CLASSICMODELS.ORDERS.ORDERNUMBER =
CLASSICMODELS.ORDERDETAILS.ORDERNUMBER
and
CLASSICMODELS.ORDERS.STATUS = 'Shipped'
and orderdate between ? and ?]]></xml-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>91</design:nativeDataTypeCode>
<design:precision>10</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:inputAttributes>
<design:elementAttributes>
<design:optional>true</design:optional>
</design:elementAttributes>
</design:inputAttributes>
</design:parameterDefinitions>
<design:parameterDefinitions>
<design:inOutMode>In</design:inOutMode>
<design:attributes>
<design:name></design:name>
<design:position>2</design:position>
<design:nativeDataTypeCode>91</design:nativeDataTypeCode>
<design:precision>10</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:inputAttributes>
<design:elementAttributes>
<design:optional>true</design:optional>
</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:uiHints>
<design:displayName>ORDERNUMBER</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>ORDERNUMBER</design:label>
<design:formattingHints>
<design:displaySize>11</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>PRODUCTCODE</design:name>
<design:position>2</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>PRODUCTCODE</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>PRODUCTCODE</design:label>
<design:formattingHints>
<design:displaySize>15</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>QUANTITYORDERED</design:name>
<design:position>3</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>QUANTITYORDERED</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>QUANTITYORDERED</design:label>
<design:formattingHints>
<design:displaySize>11</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>PRICEEACH</design:name>
<design:position>4</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>PRICEEACH</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>PRICEEACH</design:label>
<design:formattingHints>
<design:displaySize>22</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>ORDERLINENUMBER</design:name>
<design:position>5</design:position>
<design:nativeDataTypeCode>5</design:nativeDataTypeCode>
<design:precision>5</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>ORDERLINENUMBER</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>ORDERLINENUMBER</design:label>
<design:formattingHints>
<design:displaySize>6</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>ORDERNUMBER</design:name>
<design:position>6</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>ORDERNUMBER</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>ORDERNUMBER</design:label>
<design:formattingHints>
<design:displaySize>11</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>ORDERDATE</design:name>
<design:position>7</design:position>
<design:nativeDataTypeCode>91</design:nativeDataTypeCode>
<design:precision>10</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>ORDERDATE</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>ORDERDATE</design:label>

Report message to a moderator

Re: How to display data filtered by Datetime [message #481020 is a reply to message #480811]

Wed, 19 August 2009 11:23

Evgeny

Messages: 19
Registered: July 2009

Junior Member

Hello Jason,

thanks a lot for this example. It will be very useful, but I can't figure
out one thing. I see you have created the parameters beginDate and endDate
which help to filter the report. What I don't understand is how have you
binded this parameters to the report, so that it is filtered according to
them. To my knowledge this is done in the "Filters" tab of the "Cross
Tab", but it is empty...

Thanks a lot

Evgeny

Report message to a moderator

Re: How to display data filtered by Datetime [message #481052 is a reply to message #481020]

Wed, 19 August 2009 12:53

Evgeny

Messages: 19
Registered: July 2009

Junior Member

Hi Jason,

I just read something on Data Set Parameters and thus I understood what
you have been doing, just for the record, here the sql query for the
retrieval of data:

select *
from CLASSICMODELS.ORDERDETAILS, CLASSICMODELS.ORDERS
where CLASSICMODELS.ORDERS.ORDERNUMBER =
CLASSICMODELS.ORDERDETAILS.ORDERNUMBER
and
CLASSICMODELS.ORDERS.STATUS = 'Shipped'
and orderdate between ? and ?

The question marks set the data set parameters...

However I am having a problem adapt it to my needs. I am getting a "sql
error #1 missing in or out parameter".

Thanks again

Evgeny

Report message to a moderator

Re: How to display data filtered by Datetime [message #481055 is a reply to message #481020]

Wed, 19 August 2009 12:58

Evgeny

Messages: 19
Registered: July 2009

Junior Member

Report message to a moderator

Re: How to display data filtered by Datetime [message #481058 is a reply to message #481052]

Wed, 19 August 2009 13:10

Evgeny

Messages: 19
Registered: July 2009

Junior Member

ok, the missing parameter error is solved (needed to add the parameter ins
the edit parameter set window)

best

evgeny

Report message to a moderator

Re: How to display data filtered by Datetime [message #481106 is a reply to message #481058]

Wed, 19 August 2009 16:03

Jason Weathersby

Messages: 9167
Registered: July 2009

Senior Member

It is working now?

Evgeny wrote:
> ok, the missing parameter error is solved (needed to add the parameter
> ins the edit parameter set window)
>
> best
>
> evgeny
>

Report message to a moderator