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 in BIRT Data Cubes

peraka

Hi All,

In one of our reports, Data cubes are used to display the data of whole month in a single report.

Labour hours are calculated in this report. Aggregation used in the report sums up all the type of labours and their hours.

I would like to exclude "premium pay hours" from the summation and display the hours.

Is there anyway that I can have expressions in the Aggregation of Data cubes, if yes, how can I build a expression something like below example:

measure[labordataset::laborhours] - measure[labordataset::premiumpayhours]

Thanks,
Suresh.

Find more posts tagged with

Comments

Hans_vd

Hi Suresh, 
 
you can add a computed column to the dataset the cube is based on like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>if (row["LABOURTYPE"]=="PREMIUM") {
0;
}
else {
row["LABOURHOURS"];
}</pre>
 
and then use that field in the cube. 
 
Hope this helps 
Hans

peraka

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="94521" data-time="1327329383" data-date="23 January 2012 - 07:36 AM">
Hi Suresh, 
 
you can add a computed column to the dataset the cube is based on like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>if (row["LABOURTYPE"]=="PREMIUM") {
0;
}
else {
row["LABOURHOURS"];
}</pre>
 
and then use that field in the cube. 
 
Hope this helps 
Hans </blockquote>
 
Hi Hans, 
 
Thanks for your prompt reply. We're using BIRT 2.3.2, I don't find any "Computed Columns" option in Edit Data Set. 
For displaying purpose this number should be displayed, but for summation this field should not be considered. Hope my requirement is clear. 
 
Could you please give me any other option. 
 
Thanks, 
Suresh.

Hans_vd

If you don't have computed columns available, you might want to try something like this in your query: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT CASE WHEN labourtype = 'PREMIUM' THEN hours ELSE 0 END AS hours_premium,
CASE WHEN labourtype = 'PREMIUM' THEN 0 ELSE hours END AS hours_without_premium
FROM labour</pre>
 
and use both fields separately in your calculations 
 
Regards 
Hans

peraka

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="94577" data-time="1327415291" data-date="24 January 2012 - 07:28 AM">
If you don't have computed columns available, you might want to try something like this in your query: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT CASE WHEN labourtype = 'PREMIUM' THEN hours ELSE 0 END AS hours_premium,
CASE WHEN labourtype = 'PREMIUM' THEN 0 ELSE hours END AS hours_without_premium
FROM labour</pre>
 
and use both fields separately in your calculations 
 
Regards 
Hans </blockquote>
Hans, 
 
Exactly, I'm using the same way, in the query and able to populate it. But I don't want that to be a part of aggregation. 
 
Here we're using Data Cubes. 
 
measure[labordataset::laborhours] is the measure, which is calculating the summation of all the hours, in which I want to exclude laborhours of few labtrans types. 
 
Hope my requirement is clear to you now. 
 
Thanks, 
Suresh.

peraka

How to write an expression for a summary field of a datacube.

Attached the screenshot for clarity. I have to exclude few fields from the SUM. i.e. if labortranstype in ('a','b') then it should not include laborhours against these labortranstype in the SUM, rest all other labortranstypes can be added.

mwilliams

You're using a SQL dataSet and computed columns are not available? Or are you using a scripted dataSet to write your query?

As for your most recent post, is "labortranstype" a dimension of the cube that you don't want to include in your grand total calculations?