How can I find the number of records inserted into a spreadsheet using Report.AddTable ?
On one sheet I have imported my data from a TList<T>. I now want to get the sum of one of the columns.
Lets say I imported 85 rows of data going from row 2 in the spreadsheet to row to row 86. I now want the sum of column B from row 2 to row 86.
I could use the Excel function =Sum(B:B) but this will sum the entire column, not just the imported data.
I can get the address of the first cell where I want the sum to start (B2) but I can't get the address of the last cell (B86). If I knew the number or rows inserted then this would be possible.
Is there a better way of doing this ?
Jim
Hi,
Normally you don't have to do anything, the range will just expand when you insert the 85 rows. So let's say you have a template:
B2: <#someproperty.value>
D1: =Sum(B2:B3)
Where B3 is empty, and then name the range as __SomeProperty__X (the X at the end is to remove the B3 row once the report is completed)
When the report runs, you will have +Sum(B3:B86) at D1.
You can look for example at the demo: TMSSoftware\FlexCelVCLNT\Demo\Delphi\Modules\20.Reports\20.Range Reports
In the second sheet (products by category), in cell F10 you have the formula: "=COUNT(E8:E9)"
When you run the report, the formula will expand to the whole range.
If for any reason you actually need the number of rows in the datatable, you can use the tag <#sometable.#RowCount> You can see a demo of it at:
TMSSoftware\FlexCelVCLNT\Demo\Delphi\Modules\20.Reports\91.User Tables
And you could use a <#formula> tag to manually calculate the text of the formula:
FlexCelVCLNT\Demo\Delphi\Modules\20.Reports\48.Manual Formulas
But I think this would be overdoing it. Just writing the formula in a way it will expand should be enough. (=SUM(B2:B2) won't expand, you need at least 2 rows like B2:B3)
Hi Adrian,
Thanks for your quick reply. This leads me to another question.
I want to use DSUM and DCOUNT Excel functions. Both of these require a range to be defined in which the first row will contain the column names and subsequent rows contain the data.
I have my column names in row 1 and my data starts in row 2. I have defined a range Arrears as B1:B10 and my data inserts as expected.
I now need a range starting on row 1 and ending on my last row of data. How can I set this up ? Do I need to use <#sometable.#RowCount> or is there a better way to do it ?
Jim
Hi,
Honestly I don't think I can recall a case where you need to use #RowCount to manually create formulas. #RowCount can have other uses (like for example deleting the row if row count is 0, or doing something with the first 5 rows, etc) but formulas normally don't need anything. I actually don't recall needing the <#formula> tag in any real report either.
I am not really sure on how exactly your data looks like, but it is normally always the same: Define a range with at least 2 rows so it expands, and end the name with an "X" so the empty extra row is deleted after the report runs.
I've uploaded a simple example using DSum which recreates the example here:
And you can get the example here:
As said, I don't think there is a need to manually create a formula here, but if your case is different, can you modify this template to show what the problem would be?
Regards,
Adrian.
I want to use the Excel function DSUM. The first parameter of DSUM is an excel database. This is a range where the first row contains column names and subsequent rows contain data.
In my FlexCel report how do I define a range where the first row contains column names and the subsequent rows contain data.
Given the following
A B
1 Name Count
2 <#Table.Name> <#Table.Count>
If I set Data as A2:B2 then Data does not contain column names and I can't use DSUM.
If I set Data as A1:B2 then the column name row is repeated for every row of data imported and I can use DSUM.
How should I define Data so that the first row contains column names and subsequent rows contain data so I can use DSUM ?
You just can't use the Data name as a parameter of DSUM. You can either:
1)Write the range directly as in the example I sent in the last post.
That is: DSum(A1:B3,...)
2)If you want to use a name instead of directly, define a new range:
MyDSumRange: A1:B3
Then write =Sum(MyDSumRange,...)
Please ignore my last post - I am having a bad hair day! I am trying to work with named ranges when I just need to use A1:B3.
Jim