Using variable for passing values in birt report

Hello. 
I'm creating a report, in which I need to calculate a value of 2 fields, compare it to a 3.rd one. Depending on the values, I need to calculate a value and pass it to the next row and repeat the calculations there.
 
I desperately need some help. I already thought I had this solved, but as I ran the report again, the report doesn't produce any result set. Although the main data set preview shows the correct collection of dataset rows..
 
I checked the table shouldn't contain any extra bindings and there are no visibility issues.

Now, I created:
1) global variable "jaannos"
2) I copy the dataSetRow value "SUMMA" to the global variable in Main data sets onFetch -script as follows:
vars["jaannos"] = dataSetRow["SUMMA"].value;
* In the main table I computed the value like this:
if((row["LIIKAMAKSU"].value + row["KULUKORVAUS"].value) < vars["jaannos"].value) 
{ 
(row["LIIKAMAKSU"].value + row["KULUKORVAUS"].value) 
} 
else { 
vars["jaannos"] 
}
3) I count the remaining value, if any like this to another dedicated field:
if((row["LIIKAMAKSU"].value + row["KULUKORVAUS"].value) < row["Muuttujan_arvo"].value) 
{ 
row["Muuttujan_arvo"].value - (row["LIIKAMAKSU"].value + row["KULUKORVAUS"].value) 
} 
else { 
0 
//vars["jaannos"] 
}
4) I replace the global variable value with the remaining value like follows:
vars["jaannos"].value = row["Seur_rivi"].value;
 
The points 2-4 are used as data fields expression formulas..
 
RESULT:
No values appear in the report.
 
Do you have any idea where the problem might be?
 
- Elja

Find more posts tagged with

Comments

micajblock

Can you provide an example using a text file with dummy data? Also what version are you using?

Elja

Hey Mica.
Thanks for assistance. I created a simple excel file and tried to fix the report to get the data out of it.
 
In the sample report I present 3 tables, one just showing the data from Excel.
 
The second one resembles the report I'm trying to produce. no variable is here.
 
The third one is similar to the second table, but doesn't work. In this one I try to use the variable..
 
- Elja
 
Ps. The way I'm trying to use the variable is explained in the "Using_variable.docx" -attachment.

micajblock

What version of BIRT are you using?

Elja

sorry, forgot to mention: 4.6

micajblock

I was not able to run the report. Can you make sure the report runs without Oracle?

Elja

Hello Mblock.
My bad, I didn't remove the old data sources and sets and there was some binding left in the background.
 
Now I removed them and it seems to do the job.
The tables are better looking in pdf-format.
 
- Elja

micajblock

Are you still having an issue? I am still having problems running the report.
 
P.S. Why do you use the notation of row["fieldname"].value? There is no need for the '.value' (it is actually causing me issues when running reports?
 
P.P.S. If you are still having issues can you share a screenshot of the 'Output Columns' tab of the data set so I can see what data types you are using. for some reason it is erasing it for me. Also provide details of what you are trying to accomplish.

Elja

Hello Mblock.
 
Yes, this problem still exists. I've been working on some other stuff and hoping I could find the answer from here.
 
1) I've been using row["fieldname"].value -notation since we discovered that this was the necessary way to compare NULL-value in before open -script. I guess it shouldn't be done here. I'll test without the .value.
 
2) I'll attach the output columns.
 
3) The goal. I'll try to put this in a nut shell:
In this report, a value counted from a substraction (based on database field values) needs to be passed to the next row for a new calculation..
 
Example:
* There are 3 values on row 1: A (Liikamaksu), B (Kulukorvaus), C (Summa)
Case 1: A+B < C, C-(A+

is transferred to the next row.
Case 2: A+B >= C, C is presented in the row, now value is transferred to the next row.

micajblock

Take a look at the simplified example

Elja

Hey Mica and thanks.
 
I think there are some things in your example that need tuning:
1) The value of SUMMA should be presented, if SUMMA < LIIKAMAKSU + KULUKORVAUS
else "LIIKAMAKSU + KULUKORVAUS"
2) The remaining value of SUMMA - (LIIKAMAKSU + KULUKORVAUS) should be transmitted to variable and be used in the next rows calculation.
Let's see with an example:
LIIKAMAKSU = 300 (on each row)
KULUKORVAUS = 200 (on each row)
SUMMA = 1100 (original value, this will change row by row)
 
* First row: SUMMA > LIIKAMAKSU + KULUKORVAUS; 1100 > 300+200 the value on this row should be "LIIKAMAKSU + KULUKORVAUS" = 500.
* For the next row, this LIIKAMAKSU+KULUKORVAUS -> 500 should be subtracted from old SUMMA to create the value of new SUMMA:
SUMMA (new) = SUMMA (former) - (LIIKAMAKSU + KULUKORVAUS) -> 1100 - 500 = 600.
* Second row: SUMMA (600) > (LIIKAMAKSU + KULUKORVAUS), 600 > 500. 
Value of LIIKAMAKSU + KULUKORVAUS = 500 is presented. New SUMMA value is: 600-500 = 100. This is transmitted to the third row.
* Third row: SUMMA < (LIIKAMAKSU + KULUKORVAUS): 100 < 500. NOW, the value of SUMMA is presented (100).
As SUMMA was finally < LIIKAMAKSU + KULUKORVAUS, there's nothing to remain for the forth row and from now on the value of "LIIKAMAKSU + KULUKORVAUS" should be presented on each of the remaining rows.
 
- Elja

micajblock

I am sorry, I am not sure I am clear on the logic. Instead of me can you provide a spreadsheet with formula's so I can be sure that I get it right? 
Thanks.

Elja

I created a simplified example in an excel sheet, in which I marked just the 3 fields in question and the value which reduces each time and is presented in the next row...
 
Let's hope I'm able to explain this matter well enough this time.
 
- Elja
 
PS. I think I figured out one thing that might have made this case difficult to follow..
1) The field "Liikamaksu" and "Kulukorvaus" can differ on each row.
The field "Summa" is a one time value, which is sliced into many rows, never exceeding the sum of "Liikamaksu + kulukorvaus".
 
In a nutshell: In "Summa" -field should be represented the value of either:
1) Summa, IF Summa <= Liikamaksu + Kulukorvaus.
2) Liikamaksu + Kulukorvaus, IF Liikamaksu + Kulukorvaus < Summa. AND in this case, the remaining value of "Summa - (Liikamaksu + Kulukorvaus)" is counted and used in the next rows Summa field! This will be repeated until Summa <= Liikamaksu + Kulukorvaus.

Elja

Hey, we managed to get this working. I'll explain shortly how..

Elja

Ok.
So, the basic dilemma was to obtain a certain value from database and then use it in Excel-like fashion on the report: to subtract the value of C from A+B until C < A+B and after that C = 0.
 
Our solution for this matter was to use a variable and a computed field:
 
1) we created a variable "jaannos" -> default value 0.
2) we created a computed field in the main data set: "Saatu_perittya" with the following formula:
if (row["LIIKAMAKSU"] + row["KULUKORVAUS"] < row["SUMMA"] - vars["jaannos"]) {
 row["LIIKAMAKSU"] + row["KULUKORVAUS"]
}
else if(row["SUMMA"] - vars["jaannos"] > 0) {
 row["SUMMA"] - vars["jaannos"]
}
else {
0
}
 
* Here, LIIKAMAKSU = A, KULUKORVAUS = B and SUMMA = C. C is the value that is obtained once from the database and after that diviced into many rows after it's been used.
 
3) variable "jaannos" is given a new value also in computed column "Col_Jaannos":
vars["jaannos"] += row["LIIKAMAKSU"] + row["KULUKORVAUS"]
-> jaannos (new) = jaannos (old) + A + B
 
Now, this will affect the formula shown in number 2 to give the correct values to the report.
I didn't go with the onFetch script anymore as it seemed to be run on every row. (Before I thought it was run once).
 
- Elja

micajblock

So you have a solution? I was going to look at this, this morning.

micajblock

I think I created a much simpler solution. I have one report variable named 'nextRow' with an initial value of -1. Then I have a a single data binding with this expression. It seems to work. Let me know if this works for you.
<div>
<pre class="_prettyXprint">
if (vars["nextRow"]==-1) {
vars["nextRow"]=row["SUMMA"];
}
if (row["LIIKAMAKSU"]+row["KULUKORVAUS"]<vars["nextRow"]) {
vars["nextRow"]=vars["nextRow"]-(row["LIIKAMAKSU"]+row["KULUKORVAUS"]);
row["LIIKAMAKSU"]+row["KULUKORVAUS"];
}
else {vars["nextRow"]}</pre>
</div>