Subtotal not working

Aspose.Cells Product Family
1

Hi,

hopefully its just a syntax issue and an easy fix. I have 3 columns in excel with the following smart markers:

Column A: &=Positions.assetclass

Column B: &=Positions.security

Column C: &=Positions.marketvalue

I run the report as is and I see my list of 50 positions. What I would like to do is add a subtotal in column C after the last market value record.

So I put the following smart marker for subtotal:

&=Positions.marketvalue(subtotal9: Positions.assetclass)

but the data isn't subtotaled. What am I doing wrong?

2

Hi,


Well, you need to include “group” parameter for your first marker accordingly. For complete reference, screen shots and example codes, please see the sub-topic “Grouping Data” in the document:
http://www.aspose.com/docs/display/cellsnet/Smart+Markers

Thanks,

3

ok, so I tried that and it still didn't work.

in my assetclass column(column A) I have the following:

&=Positions.assetclass(group:normal, skip:1)

also, it doesn't skip a row after each group

4

Hi,


Please follow the example on the topic which I referred (in my previous post) and input your markers accordingly. If you still find the issue, kindly create a sample runnable console application (you may use dynamic datatable(s) using your code), zip it and post it here with the template files. Also attach your expected file that you may manually create in MS Excel, we will check your issue soon.

Thank you.
5

Hi,

I looked at the site and followed the example. Were the smart markers I put in entered incorrectly or was there a problem with the syntax? To me everything looks fine...

6

Hi,


Well, these look ok but unless we have your sample console application (runnable) with the template file, we cannot evaluate your issue.

Thanks for your understanding!
7

I'll try to put an app together.

in the meantime I pulled these from the smart market page; what's the difference between these two?

&=[Order Details].UnitPrice(subtotal9:Order Details.OrderID)

and

&=subtotal9:Order Details.OrderID&=subtotal9:Order Details.OrderID

8

Hi,


"&=[Order Details].UnitPrice(subtotal9:Order Details.OrderID)“

The Summary function would be applied in the UnitPrice field with respect to OrderID (group by) field.

”&=subtotal9:Order Details.OrderID&=subtotal9:Order Details.OrderID"

Well, I am not sure why you are repeating this or writing the same marker two times and mixing it. If you write it one time, there is not much difference though.


Thank you.
9

Hi,

I got it working thank you

the second line I must have pasted twice by accident from the smart marker webpage.

it only should have been &=subtotal9:Order Details.OrderID

so is there a difference between the two lines?

10

Hi,


Not much difference though, but I think you may use this type of separate marker ("&=subtotal9:Order Details.OrderID") to place it below your main marker, so you could make it bold or apply formattings to the summary rows/records only, etc.


Thank you.
11

thanks for the explanation....

so now that I have subtotals working I have two more requirements:

a. how do I calculate grand total?

b. how do I show subtotal label on each subtotal row

example for (b):

Region State Sales

East NH 25000

VT 15000

NJ 35000

East Subtotal: 75000

12
Hi,


In grouping data for Smart Markers, as you might already know, there would be always summary row(s) accordingly in b/w details when the markers would be processed. The summary row(s) are inserted dynamically too. I am afraid, the summary rows are inserted dynamically in b/w the cells, so by default, it might not possible to insert your static data/labels to be placed in your desired position in the cells. Also, if you place a formula e.g place a formula "Sum(C1:C2)" in the C3 cell - where as we have the main marker for Sales field, it would also calculate the summary records to be counted/included which you might not want to be added.

By the way, MS Excel has a feature i.e. Subtotals that you may try with it.



Thank you.


13

Hi,

I was afraid you'd say something like that. I'll play around with it a little, maybe I'll hide some cells to get it to work without showing the entire row.

Does the answer you gave me also apply to grand totals, is there a way to calculate it?