CODEWITHSUNDEEP
sql-serverbirt
Charles
Charles

Reputation: 5

Birt filter data set

I currently have a sql code like this 

select id1, ' ' as id2, balance1, 0 as balance2   
from table 1  
union  
select ' ' as id1, id2, 0 as balance1, balance2   
from table 2

table 1 and table 2 have no relations, so i cannot use join.

In my report template i have created a table that is binded to the data set with the columns that the sql code outputs.

I have put two detail rows in the table currently like this

head:         ID                       BALANCE    
detail1:     [id1]                 [balance1]   
detail2:     [id2]                            [balance2]

i need to filter out the data from table2 in detail1 and filter out the data from table1 in detail2. How do i do this?

Also currently my the table report results are crossing data like this: 

[id1]     [balance1]   
[id2]               [balance2]  
[id1]     [balance1]   
[id2]               [balance2]

And i need it to be one detail reuslt on top and one on the bottom like this : 

[id1]     [balance1]   
[id1]     [balance1]     
[id2]               [balance2]    
[id2]               [balance2]

how should i set my report template?

Upvotes: 0

Views: 93

Answers (1)

shA.t
shA.t

Reputation: 16968

I think you need a query like this:

SELECT *
FROM (
    SELECT id1, ' ' as id2, balance1, 0 as balance2, 1 As ord   
    FROM table 1  
    UNION ALL  
    SELECT ' ' as id1, id2, 0 as balance1, balance2, 2 As ord
    FROM table 2  ) DT
ORDER BY 
    ord, id1, id2

Upvotes: 0

Related Questions