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

Aggregation filter variable

Johny Key

Hi,

I have dataSet of Employees, where every employee has it's own unique ID. Second column in this dataSet is employee's department of work (fkDepartment).

In my report I've got grid with two columns (first column cells are filled with labels, second column cells with aggregations):

Department | Number of employees

1 | 5
2 | 3

In current state I achieved this using two different aggregations:
aggregation1
Function: countdistinct
Expression: dataSetRow["EmployeeID"]
Filter: dataSetRow["fkDepartment"] == 1

aggregation2
Function: countdistinct
Expression: dataSetRow["EmployeeID"]
Filter: dataSetRow["fkDepartment"] == 2

The problem of this approach is that for every row in grid I have to create separate aggregation. So the question is, how to achieve this result using only one aggregation combined with variable? So new aggregation would be something like:

aggregation
Function: countdistinct
Expression: dataSetRow["EmployeeID"]
Filter: dataSetRow["fkDepartment"] == variable

I tried using page variables, report variables, global variables, but it never worked the way I need. I always changed the variable value in onCreate method of label preceding the given aggregation in the grid row. So how should the aggregation's filter condition look like? And how or where to change variable value?

Can anyone please help me with this (probably easy) issue? I spent many hours tryin' to find solution but no result at all.

Find more posts tagged with

Comments

mcremer

<blockquote class='ipsBlockquote' data-author="'Johny Key'" data-cid="82062" data-time="1314691509" data-date="30 August 2011 - 01:05 AM">
Hi, 
 
I have dataSet of Employees, where every employee has it's own unique ID. Second column in this dataSet is employee's department of work (fkDepartment). 
 
In my report I've got grid with two columns (first column cells are filled with labels, second column cells with aggregations): 
 
Department | Number of employees

1 | 5 
2 | 3 
 
In current state I achieved this using two different aggregations: 
aggregation1 
Function: countdistinct 
Expression: dataSetRow["EmployeeID"] 
Filter: dataSetRow["fkDepartment"] == 1 
 
aggregation2 
Function: countdistinct 
Expression: dataSetRow["EmployeeID"] 
Filter: dataSetRow["fkDepartment"] == 2 
 
The problem of this approach is that for every row in grid I have to create separate aggregation. So the question is, how to achieve this result using only one aggregation combined with variable? So new aggregation would be something like: 
 
aggregation 
Function: countdistinct 
Expression: dataSetRow["EmployeeID"] 
Filter: dataSetRow["fkDepartment"] == variable 
 
I tried using page variables, report variables, global variables, but it never worked the way I need. I always changed the variable value in onCreate method of label preceding the given aggregation in the grid row. So how should the aggregation's filter condition look like? And how or where to change variable value? 
 
Can anyone please help me with this (probably easy) issue? I spent many hours tryin' to find solution but no result at all. </blockquote>
 
Why dont you use a a table group by what you wanna show and put the Aggrigation on there? You can nest tables in tables so you could use _outer.row['columnname'] for example.

Johny Key

I am pretty new to BIRT and don't have much experiences. My solution isn't using any tables, I've got only grids and dataSets. Reports I am working on are statistical reports and grids are all I need to use. I only get data from DB and work with them.

mcremer

<blockquote class='ipsBlockquote' data-author="'Johny Key'" data-cid="82078" data-time="1314703682" data-date="30 August 2011 - 04:28 AM">
I am pretty new to BIRT and don't have much experiences. My solution isn't using any tables, I've got only grids and dataSets. Reports I am working on are statistical reports and grids are all I need to use. I only get data from DB and work with them. </blockquote>
 
John, 
 
The benefit for a table is that it gets a binding to a DataSet (eg you could nest data). 
 
I suggest using Grids only for outlining etc. 
 
An

mwilliams

John,

Like mcremer said, grids are typically used for layout. You would usually only use only a grid for data if you return a single row in your dataSet. In this case, you wouldn't need a table as you don't need to iterate over your result set. In your case, you could simplify your report by using a table with grouping as mcremer has said. Then, you'd only need to create each aggregation once in your table that aggregates over the group.

Hope this helps.

Johny Key

Thank you mcremer and mwilliams for your replies, it really works and grouping is pretty powerful tool as I can see.

But I've got another question. Now I can group my dataset by department and I am able to get number of employees working in the given department using aggregation. Let's say, my dataSet has one more column and it's employee's salary. So the dataset columns are:

EmployeeID | DepartmentID | Salary

I want my report table to look like this:

Department | Together | Salary < 10000 | Salary >= 10000

1 | 8 | 2 | 6

So how to achieve this? I know how to get data for the column Department (using column binding) and for column Together (using aggregation). I know I am able to do this using two other aggregations with filter expression but that should be the same problem as problem that started this thread (aggregation with variable).

So can you help me please one more time?

mcremer

<blockquote class='ipsBlockquote' data-author="'Johny Key'" data-cid="82128" data-time="1314776271" data-date="31 August 2011 - 12:37 AM">
Thank you mcremer and mwilliams for your replies, it really works and grouping is pretty powerful tool as I can see. 
 
But I've got another question. Now I can group my dataset by department and I am able to get number of employees working in the given department using aggregation. Let's say, my dataSet has one more column and it's employee's salary. So the dataset columns are: 
 
EmployeeID | DepartmentID | Salary 
 
I want my report table to look like this: 
 
Department | Together | Salary < 10000 | Salary >= 10000

1 | 8 | 2 | 6 
 
So how to achieve this? I know how to get data for the column Department (using column binding) and for column Together (using aggregation). I know I am able to do this using two other aggregations with filter expression but that should be the same problem as problem that started this thread (aggregation with variable). 
 
So can you help me please one more time? </blockquote>
 
Ahh here your going to make the power of the data object. 
 
Create 2 columns one with the label Salary < 10000 and one with Salary >= 10000. 
 
Then drag from your palette drag the Data to the Salary < 10000 (detail) row. 
 
Now you get a dialog: With fields like : Column Binding name etc. Fill evry thing in in a logical manner. Were I have to point out make sure that Data Type is Decimal or Interger. 
 
Now on the expresion field fill somting in like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if (dataSetRow["Salary"] < 10000){
1;
} else {
0;
}
</pre>
 
Now you can agagrate with a count on the new Expresion easy peasy

. 
 
Hope this helps.

Johny Key

Thanks mcremer, I really appreciate your help.

I've got one more question but don't know if it could be asked in the current thread or I have to start new one. It relates to report formatting. In my report I am currently able to get this.

Department | Category | Count

1 ........ | 1 ...... | 5

1 ........ | 2 ...... | 8

2 ........ | 1 ...... | 3

2 ........ | 2 ...... | 7

This is result of grouping by Department and then by Category. But I want my report to look like this:

Department | Category | Count

1 ........ | 1 ...... | 5
..........

.......... | 2 ...... | 8

2 ........ | 1 ...... | 3
..........

.......... | 2 ...... | 7

It means I want to merge left column cells this way. So one group should be represented by only one number per group. Is it possible to do please?

//edit: my formatting was destroyed so I had to use dots instead of white places.

mcremer

Oh we can answer here

or you could try searching its been asked a few times.

But the answer is simple.

Select in BIRT designer the column of the data were you want to suppress the duplicates

Johny Key

Thanks mcremer, I am able to suppress duplicates now. But my problem is that, the line separating cells horizontally stays there. I want these lines only on places, where the group portion ends.

So instead of:

1 | 1 | 5

. | 2 | 8

2 | 1 | 3

. | 2 | 7

I want:

1 | 1 | 5
..

. | 2 | 8

2 | 1 | 3
..

. | 2 | 7

P.S. Dots are representing empty places.