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

Help, best way to gruop

Frenkys

I use Birt 2.6.2 and I have a dataset like this:

Job1 Job2 Job3 Job4

Mary Mary John Gilbert
Richard Helen John Helen
Mary Helen Cindy Cindy
Mary Mary Mary Mary
Richard John Helen Helen
Mary Cindy Cindy Richard
Mary Cindy Mary Helen
Richard John Helen Mary
Mary Cindy Helen Helen
? ? ? ?

What is the best way to group, count and get a report like this?

User Job1 Job2 Job3 Job4

Mary 6 2 2 2
Richard 3 0 0 1
Helen 0 2 2 4
Cindy 0 3 2 1
Gilbert 0 0 0 1
John 0 2 2 0
.... ... ... ... ...

Some users may only be present in a column.

Thanks

Find more posts tagged with

Comments

Hans_vd

Hi Frenkys, 
 
Write your SQL like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT 'job1' AS job,
job1 AS employee
FROM jobs
UNION ALL
SELECT 'job2' AS job,
job2 AS employee
FROM jobs
UNION ALL
SELECT 'job3' AS job,
job3 AS employee
FROM jobs
UNION ALL
SELECT 'job4' AS job,
job4 AS employee
FROM jobs</pre>
 
Then create a crosstab with two dimensions: job and employee, and 1 measure count(job) (or count(employee), both will work) 
 
Regards 
Hans

Frenkys

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="107665" data-time="1343657778" data-date="30 July 2012 - 07:16 AM">
Hi Frenkys, 
 
Write your SQL like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT 'job1' AS job,
job1 AS employee
FROM jobs
UNION ALL
SELECT 'job2' AS job,
job2 AS employee
FROM jobs
UNION ALL
SELECT 'job3' AS job,
job3 AS employee
FROM jobs
UNION ALL
SELECT 'job4' AS job,
job4 AS employee
FROM jobs</pre>
 
Then create a crosstab with two dimensions: job and employee, and 1 measure count(job) (or count(employee), both will work) 
 
Regards 
Hans </blockquote>
 
Hi Hans, 
Thanks for the answer, but there is a way without changing my sql? 
This dataset is only an example; my dataset is the result of a query with many other data. 
Thanks

Hans_vd

I don't immediately see another solution.
But I'm gonna take another look at it.

Hans_vd

Hi Frenkys,

I've been thinking about it and I'm quite sure that there is no possibility to group the users as long as they are in a two dimensional structure in your dataset.

Is it really that hard to modify your query?
Is the way you have the users in 4 different columns the result of how the query is built, or are they in the database that way?

Regards
Hans

Frenkys

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="107688" data-time="1343717531" data-date="30 July 2012 - 11:52 PM">
... 
Is the way you have the users in 4 different columns the result of how the query is built, or are they in the database that way? 
 
Regards 
Hans </blockquote>
 
They are in the database that way. 
Maybe I can add a new dataset only to do what you suggest. 
 
Regards