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

Compute SUMs in a GRID

THILL

Hi,

I would like to compute the SUM over all rows of a specific column (and/or over all columns of a specifiv row) in a GRID but can not use SQL though.

Example
column 1 column 2 ==> row sum
row 1 10 1 11
row 2 20 5 25
=> col sum 30 6 36

Can this be achieved? and if so, how?

Thanks

Find more posts tagged with

Comments

mwilliams

Hi THILL,

You're wanting to do this across a grid or a table?

THILL

Hi Michael,

I am using a grid object with a fixed number of rows and colums. Each cell in the grid receives its value by its own 'Select count(*)' statement associated with the cell. In the last row / the last column of my grid I want to display sums, this time not using SQL but by adding up the values found in the respective column / row.

Thanks

mwilliams

THILL,

So, you'll have the same amount of columns and rows each time? If so, you should be able to use variables to compute the values and then display them in a data item or text item in the "sum" column/row. I haven't tested this, but I don't see why it wouldn't work.

THILL

Hi,

yes, same amount of rows and columns all the time. How would I use variables? (Note: I already tried to insert a text item into my grid "sum" cell and to then use expression builder to sum up the report items, but this didn't work)

mwilliams

THILL,

You would define your variables in script and then should be able to do your sum calculations in a data element or text element. Can you attach your report design so I can see how your report is set up? I may be able to offer an simpler solution after seeing it. I won't be able to run it obviously without your data, but I'll be able to understand everything better.

PaulS

Did you ever find an elegant non-SQL solution for this? I have the exact same requirement and can't find how to do it. It's got to be easy. I know how to use the Aggregate tool in a table, but don't know how to write an expression which refers to the values contained in arbitrary grid cells?

mwilliams

Hi PaulS,

Can you create a sample report with the sample database set up similar to your report and attach it here? This way I can test with the report and post it back if/when I have a solution. Be sure to include your BIRT version. Thanks.

PaulS

Thanks for the quick response. The first part of my problem is I'm using BIRT report design for Eclipse, I'm not using BIRT programmatically by coding in Java with its API. I guess this means I'm a bit limited by the UI as to how far I can hand code my reports.

So my first question would be - am I in the right forum? I'm after help associated with the Eclipse tool.

However my requirement is trivial. In fact I'm not sure I need to send a database for example. I have a table with Patients, some male, some female. This would lend itself to a fixed sized grid right? I imagine I need one query to count male patients, and one query to count female patients. Simple right? So I created two datasets, one for each query. I trust this is the optimal approach?

So query 1 tells me I have 500 male patients, and query 2 tells me I have 450 female patients. This is neatly presented in the grid. All I want now is to be able to drop in a text box and set it's expression to something like totalMales + totalFemales.

This last bit is what's confusing me. It seems aggregates won't help me because the figures for Males and Females come from different datasets. Since expressions run as Javascript I can't see why I can't just refer to each value and add them, if it's possible then where do I find good documentation on how to do this?

Thanks very much indeed

mwilliams

PaulS,

You probably already found the solution to this, but since the items are from different dataSets, and both dataSets cannot be bound to the same grid, the aggregations wouldn't be able to access the second dataSet. You'd need to store the values in variables in your case to access them in the expression builder of a text element, dynamic text element, or data element. If your count of male and female patients came from the same dataSet, you could probably use the aggregation, as it would have access to both fields in the aggregation builder.

cirovladim

<blockquote class='ipsBlockquote' data-author="mwilliams">
 
You would define your variables in script and then should be able to do your sum calculations in a data element or text element. Can you attach your report design so I can see how your report is set up?</blockquote>
 
I've found how to do it with variables. You create one on the DataExplorer, then you could access it on script or binding and add up totals as needed. 
The only problem I have is that I need the SUM to be above data elements and I haven't found a solution for this. I hope someone can help me. 
 
I have another question though, when I drag an aggregation into the grid it has an option to Agreggate on Grid instead of a Group, have anyone used this feature? what would be the expression I should use? how should I setup the Grid or Column? 
 
In the meantime here's a report example showing how to SUM counts from different datasets on a grid. Cheers!

mwilliams

Hi cirovladim,

I modified the report design slightly to allow your aboveTotal to display correctly. This is done by setting the variable value in the onFetch script of the dataSet.

As for the aggregation on a grid, if you're getting the data from 2 dataSets, you won't be able to use this because the grid would need to have both dataSets bound to it and this isn't possible.

cirovladim

<blockquote class='ipsBlockquote' data-author="mwilliams">Hi cirovladim, 
 
I modified the report design slightly to allow your aboveTotal to display correctly. This is done by setting the variable value in the onFetch script of the dataSet. 
 </blockquote>
 
Hi Michael, 
 
Thanks for your prompt response. It's working on the sample I provided. Unfortunately it isn't working if I place only one grid. I suppose it's because datasets fetch occurs after variable label has been rendered. It was working before because previous grids caused the dataset to be fetched. 
 
Could you check this report, thanks! 
I removed the OnCreate label's event since it wasn't needed anymore.

mwilliams

Ah, yeah...All you'd need to do is put an empty text box bound to each dataSet to start your report. Make them invisible, then it'll work. Sorry, I didn't think of that cause you already had items in the report.

cirovladim

<blockquote class='ipsBlockquote' data-author="mwilliams">... put an empty text box bound to each dataSet to start your report. Make them invisible, then it'll work.</blockquote>
 
hey Michael, 
 
Thanks for the workaround, it wouldn't be pretty on my report because I have a lot of datasets to sum up. But that will do the trick. 
 
Thank you! 
 
Since I'll have many datasets, I'll put them in a grid with one row so it doesn't mess up the designer view. 
Here's the final example

mwilliams

cirovladim, 
 
Yes, with many dataSets, this could get to be a lot of bound text elements. The only other thing I could suggest would be to request an enhancement to be able to select whether a dataSet is run regardless of being bound to a report item for situations like this. Not sure that many situations would warrant this feature, but it might be worth suggesting if you'd like. 
 
<a class='bbc_url' href='http://www.birt-exchange.org/bug-reporting/'>Report Bugs - BIRT Exchange</a>