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

Tabular Report

kalsy

Hi,

I am new to BIRT and this is my first BIRT report. I am trying to create a table where column value is number calculated based on column and row header.

so my table look like below

component name P1 P2 Total

COMP1 20 5 25
COMP2 1 0 1

I created 1 data set where it lists all component name [ select cmp.name from comp where ..... ]
that populated my component header and first column

After doing this I realize that I'll have to create data set for each colm:row set, there has to be a way in BIRT
to create dataset where I can use it for each column or for each row

can someone help. Thanks

Find more posts tagged with

Comments

Tubal

How are you getting your P1 and P2? Where do they come from?

kalsy

<blockquote class='ipsBlockquote' data-author="'Tubal'" data-cid="112083" data-time="1354733097" data-date="05 December 2012 - 11:44 AM">
How are you getting your P1 and P2? Where do they come from? </blockquote>
 
I created different dataset for each value [ select table1.priority from table1 , table 2 ... where table1.priority = 'P1' same query and different dataset for P2 , P3 and so on ]

Tubal

Could you upload a copy of your report so we can see what you are trying to do?

A table can only be bound to one dataset, so you have a few options:

1. Create a dataset that retrieves all the data you need in your table (either through joins in your initial query, or a joint dataset).
2. Insert a table inside your table, and use a dataset parameter to bind the inner table to the outer table.

kalsy

Tubal

<blockquote class='ipsBlockquote' data-author="'kalsy'" data-cid="112097" data-time="1354749919" data-date="05 December 2012 - 04:25 PM">
I have attached the sample of report that I would like to create. 
 
Thanks for helping me </blockquote>
 
It would be helpful if I could see where your data is coming from, so we can decide the best way to get what you want. Possibly the queries you use to get your bug counts. I'm still not sure where your bug counts are coming from. Are all of your datasets from the same datasource? If so, you should be able to create the joins in your dataset query and only use one dataset. 
 
 
So you'd so something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT components.component_name
,count(high_bugs.bug_id) AS high
,count(medium_bugs.bug_id) AS medium
,count(low_bugs.bug_id) AS low
FROM components
LEFT JOIN bugs AS high_bugs
ON components.component_id = bugs.component_id AND bug_type = 'HIGH'
LEFT JOIN bugs AS medium_bugs
ON components.component_id = bugs.component_id AND bug_type = 'MEDIUM'
LEFT JOIN bugs AS low_bugs
ON components.component_id = bugs.component_id AND bug_type = 'LOW'
GROUP BY components.component_id</pre>
 
And then you can create a Data Element for your Total, adding the high, medium, low together. 
 
Let me know if this puts you in the right direction, or how you're retrieving your bug counts.

kalsy

<blockquote class='ipsBlockquote' data-author="'Tubal'" data-cid="112083" data-time="1354733097" data-date="05 December 2012 - 11:44 AM">
How are you getting your P1 and P2? Where do they come from? </blockquote>
 
Thanks Tubal. 
 
here is my query. It is generating off the chart counts. 
 
my total bugs are 3807 [ select count(*) from bugs ] 
 
 
select cmp.name , 
count(bugs_P0.bug_id) as P0 , 
count(bugs_P1.bug_id) as P1 , 
count(bugs_P2.bug_id) as P2 , 
count(bugs_setPr.bug_id) as SetPriority 
from classifications c 
inner join products p on p.classification_id = c.id 
inner join components cmp on p.id = cmp.product_id 
left join bugs as bugs_P0 on cmp.id = bugs_P0.component_id 
and bugs_P0.priority = 'P0' 
and bugs_P0.bug_status = 'ASSIGNED' 
and bugs_P0.resolution = '' 
left join bugs as bugs_P1 on cmp.id = bugs_P1.component_id 
and bugs_P1.priority = 'P1' 
and bugs_P1.bug_status = 'ASSIGNED' 
and bugs_P1.resolution = '' 
left join bugs as bugs_P2 on cmp.id = bugs_P2.component_id 
and bugs_P2.priority = 'P2' 
and bugs_P2.bug_status = 'ASSIGNED' 
and bugs_P2.resolution = '' 
left join bugs as bugs_setPr on cmp.id = bugs_setPr.component_id 
and bugs_setPr.priority = 'Set Priority' 
and bugs_setPr.bug_status = 'ASSIGNED' 
and bugs_setPr.resolution = '' 
where p.isactive = 1 
and cmp.isactive = 1 
and c.id = 1 
#and p.name in ('Product-A' , 'Product-B') 
group by cmp.name 
order by cmp.name 
 
 
 
some rows returned count for P0 as 4800 
 
 
Thanks for helping me

Tubal

So if we get your query working, that should give you what you need to make your table show what you want correct? 
 
I think the problem with your counts are that your joins are creating more than one record for each component, so it's doubling up some of your bugs and they are getting counted more than once. So you'd do your component-bug join in a subquery, and then join that subquery to your other tables. 
 
What database are you using? (MySQL, PostgreSQL, etc) 
 
I'm familiar with PostgreSQL, and they have a 'CASE' statement that would make your query simple: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT cmb_bugs.*
FROM (SELECT cmp.product_id
,cmp.name
,sum(case when bugs.priority = 'P0' then 1 else 0 end) P0
,sum(case when bugs.priority = 'P1' then 1 else 0 end) P1
,sum(case when bugs.priority = 'P2' then 1 else 0 end) P2
,sum(case when busg.priority = 'Set Priority' then 1 else 0 end) SetPriority
FROM components cmp
LEFT JOIN bugs on bugs.component_id = cmp.id
WHERE cmp.isactive = 1
AND bugs.bug_status = 'ASSIGNED'
AND bugs.resolution = ''
GROUP BY cmp.product_id, cmp.name
) cmp_bugs
INNER JOIN products p ON p.id = cmp_bugs.product_id
INNER JOIN classifications c on c.id = p.classification_id
WHERE p.isactive = 1
AND c.id = 1
ORDER BY cmp_bugs.name</pre>
 
I haven't tested this query obviously, but in theory it should work. 
 
The goal is to get all the data you need for your table from one dataset.

kalsy

<blockquote class='ipsBlockquote' data-author="'Tubal'" data-cid="112143" data-time="1354810859" data-date="06 December 2012 - 09:20 AM">
So if we get your query working, that should give you what you need to make your table show what you want correct? 
 
I think the problem with your counts are that your joins are creating more than one record for each component, so it's doubling up some of your bugs and they are getting counted more than once. So you'd do your component-bug join in a subquery, and then join that subquery to your other tables. 
 
What database are you using? (MySQL, PostgreSQL, etc) 
 
I'm familiar with PostgreSQL, and they have a 'CASE' statement that would make your query simple: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT cmb_bugs.*
FROM (SELECT cmp.product_id
,cmp.name
,sum(case when bugs.priority = 'P0' then 1 else 0 end) P0
,sum(case when bugs.priority = 'P1' then 1 else 0 end) P1
,sum(case when bugs.priority = 'P2' then 1 else 0 end) P2
,sum(case when busg.priority = 'Set Priority' then 1 else 0 end) SetPriority
FROM components cmp
LEFT JOIN bugs on bugs.component_id = cmp.id
WHERE cmp.isactive = 1
AND bugs.bug_status = 'ASSIGNED'
AND bugs.resolution = ''
GROUP BY cmp.product_id, cmp.name
) cmp_bugs
INNER JOIN products p ON p.id = cmp_bugs.product_id
INNER JOIN classifications c on c.id = p.classification_id
WHERE p.isactive = 1
AND c.id = 1
ORDER BY cmp_bugs.name</pre>
 
I haven't tested this query obviously, but in theory it should work. 
 
The goal is to get all the data you need for your table from one dataset. </blockquote>

kalsy

Hi Tubal,

I ran below last night and it worked. I really want to thank you for putting me into right path.
with my 10 day experience in SQL and BIRT, I would not be able to get to this solution

select cmp.name ,
count(case when b.priority = 'HIGH' then b.bug_id else NULL end) AS HIGH ,
count(case when b.priority = 'MED' then b.bug_id else NULL end) AS MED ,
count(case when b.priority = 'LOW' then b.bug_id else NULL end) AS LOW ,
from components cmp
left join bugs b on cmp.id = b.component_id
where cmp.isactive = 1
group by cmp.name

BTW, I am using MySQL