How to count sum out of dynamic fields?

Hello.
I'm trying to count a basic "Excel"-like sum in a BIRT report (output in excel).
 
The field I'm using is dynamic aggregate SUM and vary on a given condition, meaning that a value in the field can be retrieved from 2 different fields. The "aggregate" field I'm trying to create AND the field it get's its values from are both presented in two different report group footer rows.
 
The formula I'v used is:
if(row["palvelutuottajaosaid"] != null && row["palvelutuottajaosaid"] != ""){ 
row["palautteet_osa"]} 
else row["palautteet"]
 
Now, normally aggregate is attached to a certain field/column. If I use this field in aggreagate, the sum is something like 402 instead of 25.
 
Is there some easy way of counting just the values in a certain column that can already be seen there?
 
I'll attach a screen shot where I've marked the values I'm trying to achieve in red color.
 
I've searched the forum for an answer, but didn't manage to find a solution I could use. The simplest one I found is shown below, but the binding just broke the report and I ended up having an excel with no data in the main table.
 
:Total.sum(dataSetRow["yourRow"])
 
Thanks in advance, Elja

Find more posts tagged with

Comments

JFreeman

What version of BIRT are you using?
 
Can you attach a sample report based on classic models or a flat file data source that we can to run to replicate the behavior you are seeing?

Elja

Hi again.
 
My Eclipse + Birt version is 4.5.0.
 
I've been trying to collect the necessary information via SQL-query. I've encoutered a lot of problems doing it in the data set code and it would just seem so much easier to make the count in BIRT.
 
The main problem now is that there are 2 main groups of info I've delivering in the report. The feedback and reclaims are the "second set" and inside SQL it is difficult to achieve the "Excel column sum" like goal. There are several report parameters and most of them would need to be considered in SQL for the sum to be count in expected way. Aggregation sum would be easy, if the column wasn't dynamic (value in it is counted from 2 different fields depending on another field value). I've proceeded with the SQL-approach but it really is making this unnecessary difficult.
 
I'll try to create a flat file.
 
Thanks, Elja

Elja

The attachments I've created include the flat file "Column_sum2.csv", report that uses it (tr3_test.rptdesign) and "report_output.xlsx" where I've explained what's what more I'd need from the report.
 
- Elja

JFreeman

You can add an aggregation element set as sum in the footer of the table and then use the column binding you created for that column as the source for the aggregation.
 
Take a look at the attached modified version of your report with this change in place.

Elja

Thanks for the tip.
 
I actually tried at first this aggregate field to sum the above field (by column binding name).
 
The solution I've tried before is counting the sum in the background (in main data sets SQL). In the picture I've attached it shows value 27 which is correct.
 
But when using aggregate sum-field, insted of giving the expected value "27", I got "224". Even though the values above (not seen in the attached output excel) add up to 27...
 
I guess even though the values are shown only in middle layer aggregate fields (I've used other aggregate fields above the one I'm trying to get working) and NOT on every data-row, the aggregate field still sums the multiple instances of the data field value..
 
That's the core problem I've had to fight with aggregate field: how to include only the necessary values from dynamic data-field into the counted aggregate sum.
 
Now, I guess the problem is with my setup:
* "Palautteet" -fields value is counted already as a SUM in SQL from database (according to the values given in parameters)
* This data field chooses the sum it displays according to another fields value.
* "Palautteet" is regular data field, but presented in a groups footer row. Until now everything works fine: it shows a group-spesific value as wanted.
-> Finally: in a master groups footer row, I'm trying to sum all the above child-sums together.
 
I'll attach a screen shot out of the excel output in both cases.
 
- Elja

Elja

So back to the original set up (the way I'd like this to work):
 
1) I've counted a SUM value in SQL to be presented in the reports field: "pal_palv".
This counts the sum of feedback according to the service, which is selected by the person running the report.
 
2) This pal_palv -SUM can be counted either for a company or a "company division" and therefore I created a data-field with the following formula:
if(dataSetRow["palvelutuottajaosaid"] != null && dataSetRow["palvelutuottajaosaid"] != ""){ 
dataSetRow["palautteet_osa"]} 
else dataSetRow["palautteet"]
 
-> palautteet_osa is the sum of feedback for the division and palautteet is for the company
 
3) Whatever value exists, it is presented in the "Service" -groups footer -row.
 
4) Finally: all of the feedback sum values (from both types: company and company division) from service footer row should be counted together in the company-groups footer row.
 
- The idea is that some of the feedback values are for the whole company, some for the division. And they should all be counted together in the Company-groups footer row..
 
- Elja