Hello,
We would like to document the subtotal functionality in Smart Markers for our users. Since there is currently no official documentation available, we have created a rough draft based on the behavior we’ve observed and tested.
I’ve attached the draft document for your review. Could you please take a look and confirm whether this usage is accurate, or if there are any details or edge cases we might have missed?
Your feedback will help ensure we provide correct and complete guidance to our users. Subtotal.docx (16.9 KB)
A couple of points seem to be missing from the documentation. Could you please confirm if our understanding below is correct?
subtotal9 syntax: From what we understand, the number 9 refers to the Excel SUBTOTAL function code for SUM. Similarly, other function codes like 1 for AVERAGE, 2 for COUNT, etc., should also be supported.
(group) parameter: Adding (group) to a field groups the data by that field, and subtotals are then applied per group. This seems to automatically insert a subtotal row after each group.
Please let us know if this is accurate or if there are any additional details we should be aware of. We’re working on internal documentation and want to ensure it’s fully aligned with intended behavior.
Your understanding is correct. We also recommend you to see the document for parameters details on grouping data in smart markers for your complete reference: Using Smart Markers|Documentation
Thanks ,
We encountered an issue where the subtotal functionality does not work correctly when the data is not sorted. We attempted to resolve this by adding the ascending: 1 parameter before the group operation, but it did not produce the expected results.
Could you please take a look and advise on how to proceed?
Attaching template and payload. smart_markers.zip (14.2 KB)
Thanks for the template Excel file and JSON data file.
After initial testing, I am able to reproduce the issue as you mentioned by using your template file with JSON data. I encountered an issue where the subtotal functionality does not work correctly.
We require through evaluation of the issue. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSJAVA-46414
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
@pgcentric
If you want to use group smart marker, please group or sort data source first.
So please do not set “ascending” marker for “group” marker.
Now we only support sorting as table.column, not support nested data source.
We will check/analyze this and provide you with details on the possibility of supporting automatic sorting of the subtotal data source when the “subtotal” parameter is included in the template. We will get back to you soon.
@pgcentric
We currently insert all the data into Excel and sort the data range, so we are unable to sort the data after group and subtotal.
Sorting the data sources is currently the only feasible solution.
But we cannot change the order of records in the data source, so if you cannot sort, we will have to copy the data source and sort it again. Due to the application of different smartmarker groups for the same data source, we have to repeatedly copy and sort them. If the data is large, this performance issue will be inevitable.
Another possibility is that the data source may not only be JSON, but also Datatable, Generic List, and so on
So it’s hard to support auto sorting of subtotal data source when “subtotal” parameter is present.
Hi ,
We have a requirement to filter data based on the condition "Fabric".
We understand that Excel does not support conditional rendering in the same way as the LINQ Reporting Engine’s <<if>> directive. As a workaround, we tried using the "group" parameter in Smart Markers, but it is not producing the desired output.
Could you please guide us on how to achieve this using Smart Markers?
We’ve attached the expected output, template, and data for your reference. Please review the “BOM Material” section grouping_data.zip (28.9 KB)
@pgcentric
Thank you for your feedback. We have analyzed your sample files and data. Sorry, currently Aspose.Cells cannot support this feature. When using “group” parameter in Smart Markers, Aspose.Cells can only support operations on the single row. Sorry for any inconvenience caused to you.
This was in context with Aspoe.words like in word template we can check if key==“something” then print this on document. but that is not possible in excel that is Aspose.cells is what I meant. So is there a workaround or solution for such use case.
Hi ,
Regarding above subtotal issue. I understand that sorting the data source prior to applying the subtotal marker is currently the only supported approach, and that auto-sorting after grouping/subtotaling is not feasible at this time.
Could you please clarify whether support for automatic sorting of the data source (when subtotal is used) is something that is on your future roadmap, or if this is a limitation that is unlikely to be addressed due to architectural or design constraints?
@pgcentric
Thank you for your feedback. We will further evaluate your needs. We’ll update you with the latest information or an ETA (if possible) soon. We appreciate your patience in this regard. We will get back to you soon.
@pgcentric
As I said in the previous post, we have to copy and sort the data source first to support this feature. Can you accept a decrease in performance？
Will you sort by the value of subtotal?
Are all data source JSON file?