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; } } }

Union data set preview and report viewer results different

rudolfs

Hello,
 
I'm creating a report where I have UNION in query. The results are good for the data set "Preview Results", but when I start the report viewer the report returns only the first query before union, the second one after the union isn't showing or working.
I checked this by creating 2 identical data sets, but switching the queries in union and it returns the other results which are missing from the first data set.
Screenshots in attachements for better understanding.
 
How to get that the results in birt viewer are the same as in data set preview?
 
Best Regards,
Rudolfs

Find more posts tagged with

Comments

JFreeman

What version of BIRT are you using?
What type of data source are you connecting to?
Which driver are you using?

rudolfs

BIRT RCP Designer (version 4.4.2)
I'm connecting to Jama which uses mysql database.
And the driver is com.mysql.jdbc.Driver ( v5.1)

rudolfs

Do you have any idea how to solve this? Or how to make a workaround?

JFreeman

Can you provide a sample schema dump from MySQL and a sample report/query that can replicate the issue?
 
Thus far, I have not been able to replicate the issue.
 
I setup a simple test case of 2 tables with 3 columns and when I perform a union query of the two tables I get the exact same expected result in the data set preview and in the table when running the report.

rudolfs

I couldn't upload the schema dump straight to here so here is the link to it - <a data-ipb='nomediaparse' href='http://www.files.fm/u/cpwsvzc'>http://www.files.fm/u/cpwsvzc</a>
The report sample is in attachment.
 
The queries which should work are "Derigs" and "Derigs1". Both the same but the order of union switched.
Project ID is as report parameter, I was using project with id 20249.
 
Here is the union query alone.
<pre class="_prettyXprint _lang-sql _linenums:1">
select
document.id AS API_ID,
document.name AS NAME,
document.description AS DESCRIPTION,
document.documentKey AS DOCUMENT_KEY,
docs.sequence AS SEQUENCE,
documentcustomfieldvalue.documentId AS DOCUMENT_ID,
docs.globalSortOrder,
MAX(CASE WHEN documenttypefielddefinition.label = 'DarbietilpÄ«ba' THEN textValue ELSE NULL END) AS DARBIETILPIBA,
MAX(CASE WHEN documenttypefielddefinition.label = 'PrasÄ«bas ID' THEN textValue ELSE NULL END) AS PRASIBAS_ID
from document
join (select doc.id, doc.name, dn.sequence, dn.globalSortOrder
from document doc, documentnode dn
where doc.projectId = ? -- report_projectId
and dn.scopeId = 5
and dn.refId = doc.id
and dn.baseLineId is null
and dn.sequence like '2%') docs on docs.id = document.id
join project on document.projectId= project.id

left join documenttype on document.documentTypeId = documenttype.id
left join documenttypefielddefinition on documenttype.id = documenttypefielddefinition.documentTypeId
left join documentfield on documenttypefielddefinition.documentFieldId = documentfield.id
left join documentcustomfieldvalue on document.id = documentcustomfieldvalue.documentId
and documentfield.id = documentcustomfieldvalue.fieldId
left join userbase on documentcustomfieldvalue.textValue = userbase.id

where document.projectId = project.id
and document.name like 'Sprint%' or document.name like 'Backlog'
and document.active = 'T'
group by docs.sequence

UNION

select
document.id AS API_ID,
document.name AS NAME,
document.description AS DESCRIPTION,
document.documentKey AS DOCUMENT_KEY,
docs.sequence AS SEQUENCE,
documentcustomfieldvalue.documentId AS DOCUMENT_ID,
docs.globalSortOrder,
MAX(CASE WHEN documenttypefielddefinition.label = 'DarbietilpÄ«ba' THEN textValue ELSE NULL END) AS DARBIETILPIBA,
MAX(CASE WHEN documenttypefielddefinition.label = 'PrasÄ«bas ID' THEN textValue ELSE NULL END) AS PRASIBAS_ID
from document
join (select doc.id, doc.name, dn.sequence, dn.globalSortOrder
from document doc, documentnode dn
where doc.projectId = ? -- report_projectId
and dn.scopeId = 5
and dn.refId = doc.id
and dn.baseLineId is null
and dn.sequence like '2%') docs on docs.id = document.id
join project on document.projectId= project.id

left join documenttype on document.documentTypeId = documenttype.id
left join documenttypefielddefinition on documenttype.id = documenttypefielddefinition.documentTypeId
left join documentfield on documenttypefielddefinition.documentFieldId = documentfield.id
left join documentcustomfieldvalue on document.id = documentcustomfieldvalue.documentId
and documentfield.id = documentcustomfieldvalue.fieldId
left join userbase on documentcustomfieldvalue.textValue = userbase.id

where document.projectId = project.id
and document.active = 'T' and documenttypefielddefinition.label IN ('DarbietilpÄ«ba','PrasÄ«bas ID')
group by docs.sequence

order by 7
</pre>

JFreeman

Do you have a dump with sample data to populate the tables in the schema for testing?
 
I have the schemas imported for testing but I am going to have to try and manually populate the tables with valid data to move forward without some kind of a dump of sample data I can load.