New to TFlexCelReport, though not to FlexCel in general.
Simple Master-Detail report; one field from Master is displayed on each row & two fields from Detail. I'd like to suppress the value from Master if it duplicates the previous row. Can't seem to find a demo on point for this.
Hi,
If I understood correctly, I think you are looking for something like Merging similar cells (Delphi) | FlexCel Studio for VCL and FireMonkey documentation
If you look at for example cell B17 in the template, it is:
<#if(<#SameShipper>;<#merge range(<#ref(-1;0)>:<#ref(0;0)>)>;<#shipper>)>
Where sameshipper is defined in the <#config> sheet as:
<#if(<#getShipper(-1)> = <#getshipper(0)>;true;false)>
And getShipper as:
<#dbvalue(OrdersByShipper;<#OrdersByShipper.#rowpos> + <#roffset>;"ShipVia";)>
Basically, the important thing is the DBValue tag: FlexCel Reports Tag Reference. | FlexCel Studio for VCL and FireMonkey documentation
You can use this tag to get the previous value in the database. If it is different from the value in the current row, you write the new value, else you write empty. As a bonus, here we also merge the cells if the values are similar, but you can skip that part if you don't want the master cell merged over all the same values
Yeah, I futzed around with DBValue. Problem is DBValue gets the value of the previous record. If a particular record in Master has no children in Detail this is a problem.
#ref(-1,0) gets the cell reference; I can't seem to figure out how to get the value of #ref(-1,0). Probably because I'm missing something real obvious & basic.
#ref shouldn't be used for this: in particular you can't assume the order in which cells are filled. They used to be filled from the bottom-up, and they might be filled in parallel in the future. You can't rely in a cell already being filled with the value when filling other cell.
So yes, you need to look directly in the db. I am not sure on what is the problem with a master with no detail? If it is different from the previous, it won't be written, if not it will be. But you can always make the <#if more complex to add stuff like if <#detail.#recordcount>=0 do something else.
But I can assume the order in which the cells are filled. The way in which the ranges are configured determines that.
As said, up to version 4, it would start by the last record in the db, then fill up to record 0. That actually made stuff much easier, but wasn't just performing and confused people, so we changed. But it might happen in the future that cell A1 and A1000 are filled at the same time, in different threads. As a rule of thumb, just don't assume a cell will be filled before another.
So if I'm going to use #ref, how would I do it? I'd like to use something like <#If(<#Evaluate(<#ref(-1;0)>)> <> <#mytable.fieldname>;<#mytable.fieldname>;)>
Ref gives you a reference, (cell A1), not a value (value of cell A1). So you couldn't use it directly even if you wanted it...
If you really wanted to get the value of a cell, you should use <#= (see FlexCel Reports Tag Reference. | FlexCel Studio for VCL and FireMonkey documentation ) ands yes, you could use <#ref> inside <#=>
But once again, you shouldn't, unless the value on the cell is fixed in the template. The order in which FlexCel fills the sheet is not guaranteed, it has already changed in FlexCel 4, and will likely change again in the future. While the report engine looks simple from a user point of view, it is really a really complex inside, having multiple passes over the sheet. It is not just reading the value from the db and writing it into the sheet, it needs to insert rows, move stuff around, and it has to do it as fast as possible. You can't trust anything that FlexCel writes in the sheet to have a specific order, and just because record n+1 comes after record n you can't assume FlexCel will fill record n first. What you can assume is that dbvalue(record -1) will give you the previous record.
I'd just like to know how to get FlexCel to let me duplicate the look of a simple master-detail relation with suppressed repeating master rows like the attached report.
In CR this behavior is as easy as checking a single checkbox. It seems FlexCel wants to force me to use named ranges in a way that the value from the master is spatially outside the named range of the detail table.
product.pdf (40.1 KB)
The problem with a single checkbox is that it allows you to do the simple case, but once you want to have more options, or you add a thousand more checkboxes, or it is just not possible.
In FlexCel, what you need is a __Master__ named range covering the range of cells that you want to repeat for each master record, and inside it a __Details__ range that repeats for each detail. The nice part of this approach opposed to a checkbox is that you can have more than one row for the master, or just one.
Again, take a look at the Merging similar cells (Delphi) | FlexCel Studio for VCL and FireMonkey documentation demo (you can run it from the demos included in the FlexCel setup). It does what you want, for multiple levels:
Here, Nancy Davolio has all orders at the right. From them, some are from "Speedy express". Then, you have different countries, and Austria has orders 10258 and 10351.
There are 2 ways to do this: One is with a master-detail (in this demo this is used for Nancy Davolio and the orders for her).
But if you find this gets too complex in your case, well, there is a simpler way that is just manipulating the data so there is a simple single dataset, as it is the case with all the other columns at the right.
In your case, you could create a table like:
Product Family | Product Name | Product Description | Part Number/Change Level | Available Target Market Sales |
Injection Molded Components | ABC Injection Molded Product | 0
| Master Injection Molded Product |
...
Then exporting the dataset is a single dump of this dataset to the sheet. Honestly, it is the approach I would take for a simple case like yours: In many times manipulating the data before exporting is simpler than adapting the report to the data. Normally a simple SQL can be done to join the datasets in the way you want them.
Still, you have the problem of merging cells (they aren't merged in your example, but I think merging the cells in the master as in the "Merging similar cells" example looks nicer. You can do this with the <#merge cell> tag as in that example.
I actually seem to understand how repeated values are suppressed better in the "Ship Via" and "Ship country" columns better than I understand the Employee column. Even though it appears to be a rather convoluted series of function calls.
Please, in ELI5 manner, explain how the repeated values are suppressed in the Employee column.
The shipvia could be a single expression, but the functions I think make it clearer. But basically you are comparing the current record of the db with the previous record: If it is different you write the value, else you merge the cell with the previous. There are other things unrelated to this in the functions (for example, shipvia is a number 1, 2 or 3, and we need to lookup to get the text of shipvia), but it isn't really that complex.
The Employee is actually simpler, just a "classic" master-detail report, but uses a little trick and it is that the Employee cell is merged with the cell below. It is actually writing the value in all the cells, but as the cell is merged, it will only show one value.
You can see it if you unmerge the cell in the generated report. Here it is with the image and formatting removed for more clarity:
With the cells merged:
If you right-click and unmerge the cells:
Now, if you wanted to write only the name for the first record, not using merged cells, you could write in cell A17:
<#if(<#OrdersByShipper.#rowpos>=0;<#Employees.LastName>)>
And you would get only the name for the first time. Since this is a master-detail report, the detail's rowpos resets to 0 every time a new master is written. So, you can write the <#Employees.LastName> only when the detail is at the first record. You will see something like:
Note that there are no merged cells in there. Only the first record of each detail writes the master.
