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

Dealing with previous rows, groups, sorts and subtotals?

akk2

To keep it as simple as I can, say you have: 3 columns, grouped by ID, so here 3 groups:
id, v1, v2
1, 1, 2
1, 2, 3
2, 1, 1
2, 2, 2
3, 3, 3
 
And you want to get per group: previous v1 + v1 + previous v2 + v2
 
id, v1, v2
1, 1, 2 -> 0+1 + 0+2 = 3 (no previous for group so using 0)
1, 2, 3 -> 1+2 + 2+3 = 8 
2, 1, 1 -> 0+1 + 0+1 = 2 (no previous for group so using 0)
2, 2, 3 -> 1+2 + 1+3 = 7
3, 3, 1 -> 0+3 + 0+1 = 4 (no previous for group so using 0)
 
and subtotals for each group
Grp Id1 = 3 + 8 = 11
Grp Id2 = 2 + 7 = 9
Grp Id3 = 4
big total = 24
 
What would be the best way to do it? 
 
I've managed to display the correct values using variables to store the previous values and using 'data' objects and their Expression section to update variable values & make computations, and this displays the correct information for each row, but the subtotals and totals are always wrong... It's as if the Expressions entered in 'data' objects are evaluated multiple times; once going through the details, and another time when generating the group subtotals and totals. Subtotals display either 0s or incorrect values, which seem to suggest that it is a 'previous variable' or multiple issue... Even using variables to keep a subtotal, seems to display erroneous data, which again points to multiple executions of data expressions falsifying results...
 
If it wasn't for the grouping, sorting and resetting values, and the fact that the actual formulas are quite complex (involves cos, sins, tan, etc.), doing it via SQL or using computedColumn might be an option...
 
Searching online, some mention using scripts; OnCreate for instance and then storing previous values in variables, seems to work for detail row values, but this also seems to fail somewhere... So anyway, is there a guide or do's or don'ts for dealing with previous values in Birt? It seemed relatively simple at first, but after 2 days I'm still not getting correct values!

Thanks!

Find more posts tagged with

Comments

akk2

snip.. (see below)

akk2

Yeah nothing... What I've tried, in the OnCreate of v1 and v2:
 
var curValue = row["v1"]; 
this.setDisplayValue(vars["previous_v1"]); 
vars["previous_v1"] = curValue;
 
This displays the correct previous values in the report.
 
In the data where I display the computations, I've put the formula in the Expression. It works well, it displays the correct values for each row. But when I do an aggregation on the computed data; sum of the Data that contains my formula in the Expression, I get '0', which really doesn't make much sense to me. 
 
[edit] ok workaround seems to be to keep updated variables with the subtotals and the totals (values are incremented in the Data Expression), and then just display these variables. For some reason, aggregations do not work... But as a workaround, this seems to work. 
 
Inexplicably, I had at one point used two Datas with Expression: row["X"]-vars["previous_x"] to debug x and y vs previous values and their binding remained in my table... Removing these unused bindings from my table fixed my total and subtotal variable problem...... Why?! I have no clue, as there's no assignment in there, and yet..

akk2

Ok, after some grief, here is what I have learned about dealing with previous rows: Don't do it! That said, if you must do it, as I mentioned, aggregations do not work. Worse, if you try to group your data and display variables in group footers, it won't work either. The value of the variables will be wrong, I'm not sure how, but the values of the variables change between the last detail row and the footers... So, pretty much everything breaks...
 
So... If you must do it. Here's how I managed it work. Have ONE element with an OnCreate script and manage all your values through variables. So for instance, in my basic example, I'd have these vars:
 
prev_id
prev_x
prev_y
subtotal_x
subtotal_y
total_x
total_y
 
The prev_id is used to keep the last id to know when to reset the subtotals. To debug, just putting the variables in your detail row won't work, it'll display incorrect values. So if I want to display these var values, then I just set the value of my 'data' element containing the OnCreate script (ex: this.setDisplayValue('d= '+dist + ' *** t1= ' + vars["dist_total_group"] + ' ***d2= ' ... ). Luckily, I don't have to display each row, only the subtotals. If you need to display each row .... Maybe a script OnRender to set the value might work? 
 
Maybe trying to get your previous values set in your dataset ComputedColumns might be a better way to deal with previous values... Not sure if it's doable though.
 
Also, for your footers to display the correct variable values, set their value in their respective OnCreate scripts.