| Upgrading from BIRT 2.6 to BIRT 4.4 [message #1470290] |
Wed, 12 November 2014 08:13  |
 Deepak Purohit
Messages: 1
Registered: November 2014 |
Junior Member |
|
|
We have BIRT 4.4.0 report with a report parameter of decimal data type.
The report parameter gets rounded up/down to the nearest 10 in Data Set Preview Results (and when the report is run, but we've narrowed the problem to the Data Set).
E.g. if the paramter is
0-4, rounds down to 0
5-9, rounds up to 10
10-14 rounds down to 10
15-20 rounds up to 20
:
:
90-94 rounds down to 90
95-100 rounds up to 100
The database is SQLServer 2012. The problem has not been tested against other databases.
The .rptdesign is not being included, since the various tests below change it and you can use a blank report.
The problem is in the DataSet and you won't need to run a report, but to preview results in the DataSet.
Steps to reproduce
1) Use BIRT 4.4.0 designer, Java 1.7 u25 (jdk-7u25-windows-i586.exe), SQLServer 2012
2) Create a database (AlbumsDB) with a table (Albums) with the following columns
Title varchar(50) not null
Cost decimal(18,0) not null
Type char(10) not null
3) Populate with the following 3 rows
Title Cost Type
APPLE 0 D
ABBEY ROAD 1 D
BEATLES 2 D
4) Create a blank report
5) Create a Datasource to the SQLServer
6) Create a Dataset
The Data Set query in BIRT is created using column selection from the table (and not typed in).
Please keep the format exactly as shown, using only cursor positioning and double-clicking the column/table names to select them.
select dbo.Albums.Title,
dbo.Albums.Cost,
dbo.Albums.type
from dbo.Albums
where dbo.Albums.Cost = ?
The query parameter:
Name: param_1
Native Name: greyed out and not enterable
Data Type: Decimal
Direction: Input
Default Value: 2
Linked to Report Parameter: None
The default parameter was set to 2, so that tests will either produce the correct Cost=2 row, or round down and produce the incorrect Cost=0 row.
Tests
=====
1) When the SQL statement was setup in this format,
- the "Preview Results' in the Data Set produced the wrong result (the rounded down Cost=0 row)
- the .rptdesign file did not have the nativeDataType attribute
2) When sql was edited and the table name was brought up to the same line as the last column selected as:
select dbo.Albums.Title,
dbo.Albums.Cost,
dbo.Albums.type from dbo.Albums
where dbo.Albums.Cost = ?
- the "Peview Results " in the Data Set produced the correct result (the Cost=2 row)
- the .rptdesign file did not have the nativeDataType attribute
3) When the format of the sql was
select dbo.Albums.Title, dbo.Albums.Cost,
dbo.Albums.type
from dbo.Albums
where dbo.Albums.Cost = ?
or
select dbo.Albums.Title,.Albums.Cost,.Albums.type
from dbo.Albums
where dbo.Albums.Cost = ?
that is, some or all columns selected put on same line as "select", keeping "from <table-name>" on a separate line
- the "Preview Results' in the Data Set produced the wrong result (the rounded down Cost=0 row)
- the .rptdesign file did not have the nativeDataType attribute
4) When the entire SQL was put on one line
select dbo.Albums.Title,.Albums.Cost,.Albums.type from dbo.Albums where dbo.Albums.Cost = ?
- the "Peview Results " in the Data Set produced the correct result (the Cost=2 row)
- the .rptdesign file now had the correct nativeDataType attribute <propert name="nativeDataType">3</property>
5) When any whitespace (spaces) was introduced in the statement:
select dbo.Albums.Title,
dbo.Albums.Cost,
dbo.Albums.type
from dbo.Albums
where dbo.Albums.Cost = ?
- the "Preview Results' in the Data Set produced the wrong result (the rounded down Cost=0 row)
- the .rptdesign file had an incorrect nativeDataType attribute <property name="nativeDataType">0</property>
In all tests above, if the SQL format was reverted, the nativeDataType attribute stayed in the .rptdesign file.
It got changed from "0" to "3" when the "from dbo.Albums" was brought upto the same line as "select".
6) Your suggestion to add the nativeDataType line to the .rptdesign had no effect, until step 6d)
a) Edited the initial .rptdesign (without the nativeDataType) and with the sql format as created in #1, and added <property name="nativeDataType">3</property>
- the "Preview Results' in the Data Set produced the wrong result (the rounded down Cost=0 row)
b) Then added <property name="nativeName"></property>
- the "Preview Results' in the Data Set produced the wrong result (the rounded down Cost=0 row)
c) Then added <property name="isOptional">false</property>
- the "Preview Results' in the Data Set produced the wrong result (the rounded down Cost=0 row)
d) When a slight change to the format of the SQL statement is made (bring the "from db.Albums" up to the line of the last column selected), CDReport.rptdesign produces the correct result.
|
|
|
|
Powered by
FUDForum. Page generated in 0.03013 seconds
] 
Search
Help
Register
Login
Home
