Using subtotal function on template

I have a template, that I need insert a subtotal every time a column change its value, and the excel subtotal function will work fine.

Is there a template demo showing how to use subtotal function?

I am trying to add a subtotal, but it is adding a subtotal on every single row.

José Carlos


Have you looked at 
Demo\Delphi\Modules\20.Reports\98.Encryption And Subtotals


If you look at the template (all passwords are "flexcel"), in G14 we use a subtotal function to calculate the subtotals.

Note that you need 2 different ranges for this, that is a master-detail report where the master is the column that changes the value. Look at "Master detail on one table" demo if you have the data in a single table. (you'll need to create a new table with DISTINCT in the config sheet with the values of the column that changes)

Ok, I saw this sample,  I wonder if I could use subtotal on my template like the excel use it, because I can create a subtotal, point to a  column to group it and tell which columns I will sum ... on the subtotal dialog, so I cant use subtotal this way  ?

José Carlos.

In short I want to use subtotal like this:

There are 2 concepts here:

1)There is one function called subtotal, which you write as "=Subtotal(9,...)" 

The function subtotal works like "SUM" (when the number is 9), or like other functions like "AVERAGE" (when you use a different number). the difference with a normal "SUM" is that it doesn't sum other SUBTOTAL functions.

So if you have in A6: = Sum(A1:A5), in A10 =Sum(A7:A10) and in A11 = SUM(A1:A10) the cell A11 will contain the wrong number, because it will sum all values including the partial sums in A6 and and A10. But if you use SUBTOTAL, as A5 and A10 will also have subtotal, those will be ignored by Excel and you'll have the total without the subtotals.

2).There is a command named "SUBTOTAL" in the ribbon->Data->Subtotal, which I think is what you are speaking about. This command will just loop over the cells, find different values in a column, then add a row, insert a =SUBTOTAL(9, ...) formula and then insert a "grand total".

You can do the same with FlexCel by manually looping over all the existing cells, detecting cell changes and inserting rows with Subtotal. I'll attach code at the end on how you would do it.

But the fact that you can, doesn't mean that you should. This is a very inefficient way to do subtotals, because you are going to fill the wrong cells and then insert rows with the real values.

Using the "subtotal" command might be useful if you are modifying an existing file, but if you are creating one from scratch, why wouldn't you create the right file in the first place?

 You can get the same as in without making FlexCel work so hard. In fact, you can do a much more customized version of that: The Excel command is very limited and will always use for example the text "Grand Total" and "whatever Total", you can't customize that. Also the text inserted by Excel will always be bold, you can't make it for example italic or use a different font, or format the row differently.

But the worse part is just that you are making FlexCel work double, first fill the report, and then loop over all cells to insert rows with subtotals. You can do all in one step, and it will be faster.

Now, let's go by parts. To do this in the reports without doing a subtotal, you need to 
1)Create a new table in the config sheet with the "product" (that is the column where you want to do the subtotal). this table will have all the distinct values of the product.

2)Add a relationship between the old data table and the new product one.

3)Define the master and detail ranges in the template.

I've attached a simple example that shows how you would create a report similar to the screenshot you sent. It does the same as "Encryption and subtotals" example, but it will create exactly the screenshot.

You can get the example here.

And as you can see, while I replicated the screenshot, this report is much more customizable. You could for example make the rows with subtotals green, you could write "Total" instead of "Grand total", and "Total de Productos" instead of "Productos total".

Now, if you still would prefer to make the report and then in a second step run the subtotal command, you could do it too. After all, all the magic is in the "SUBTOTAL" function (which FlexCel fully supports), the command SubTotal is just a script of commands you could do manually: Loop over the cells, see if there is a change, if there is insert a row with "=SUBTOTAL". It is simple to do in FlexCel, and I could paste the code here to do it, but I think I will just add this code to the FlexCel codebase. So in the next version there should be a "subtotal" command which should work the same as Excel. Email me next week to if you want an urgent version.


It is working now.

 thanks for your sample, it shows me how to use the relation ship.

José Carlos.