Hello,
...delete row... inside FlexCel 3 template works ok, but <#delete row> inside FlexCel 6 template not works ok.
Template:
row 6 col 1: <#Q.FIELDA> <== range Q ($A$6:$G$6)
row 7 col 1: <#delete row>
row 8 col 1: =COUNTA(A6:A7)
With FlexCel 6 report result is:
row 6 col 1: A
row 7 col 1: B
...
row n col 1: =COUNTA(A6:A6) <== value = 1
With FlexCel 3 report result is:
...
row n col 1: =COUNTA(A6:An-1) <== value = n - 6
TIA and best regards
Branko
Hi,
Indeed, delete row changed the way it behaves in FlexCel 6. The report converter should actually automatically convert the code like you posted into "X" ranges to get the same output in FlexCel 6. (and log a message explaining it did the conversion to an "X" range). But it might happen that in some cases it fails to detect that a conversion is needed.
What has happened under the hood is that the first versions of FlexCel only had an "OLEAdapter", not "XlsAdapter" which created files by ole automation. It was impossible to "delete the rows as you go" in OLE, so this was done in an extra pass over all the cells:
pass1: copy all the rows in the template for each record in the db
pass2: fill the values from the db
pass3: search for all the ...delete row... tags in the generated file in pass2
...
passn: do other stuff that I don't even remember again in the whole file.
When we introduced XlsAdapter we had the ability to mix all those passes into one, but we kept them for backwards compatibility.
But for FlexCel 6, we mixed all those passes into one:
Pass1: copy the files, while at the same time filling the cells from the database and deleting the rows and doing all the extra stuff that had to be done in separate passes before to support OLE automation.
I think the result is much better, as the report can run much faster now (specially big reports) because it does all on one pass. But this does have the side effect you mention: now delete row behaves differently.
On the old approach, we would first generate all the sheet, then do another pass on all the cells and delete the ...delete row... tags.
The result of pass2 would be something like:
row 6 col 1: Firstrecord
row 7 col 1: Secondrecord
row 8 col 1: Thirdrecord
row 9 col 1: <#delete row>
row 10 col 1: =COUNTA(A6:A9)
Then in pass3 we search for all delete rows and delete row 9, arriving at what you would expect.
But in FlexCel 6, as this is all done in one pass, the row will be deleted before the rows expand in the formula, so the formula won't expand.
We were aware of this when we introduced the new report engine in 2003 (for FlexCel .NET), but doing an extra pass in the data just to keep the old behavior was considered too inefficient. (specially because you have to do this pass even if you don't use deleterow at all. So we introduced new X ranges that provide the same functionality but don't require an extra pass.
In short, to fix your example, just remove the <#delete row> tag from row 7, and rename the range Q to be __Q__X
It will now work as before.
You can read more about this in FlexCelReportsDesign.pdf, section "X Ranges"
Thank you.
> The report converter should actually automatically convert the code ...
I convert template manualy. I did not even know that converter exists :)
Best regards
Branko
ah ok :)
Just in case, the converter is at start menu->tms flexcel->Tools
IT doesn't do a 100% perfect job, but takes care of most of the boring stuff.