To help you with exporting data from your galleries to Excel and adding subtotals after each group, you can follow these steps:
Step 1: Set up the report design
- Create two galleries: the parent gallery and the child gallery.
- Use the GroupBy function to group the items in your data source. For example, if your data source is a collection called "MyData", you can use the following formula for grouping: GroupBy(MyData, "GroupName", "GroupedItems"). Replace "GroupName" with the field in your data source that you want to group by.
- Bind the parent gallery to the GroupedItems collection.
Step 2: Add subtotals after each group
- Inside the parent gallery, add a label or any other control where you want to display the subtotal.
- Set the text of the label to the desired formula to calculate the subtotal. For example, if you have a field called "Amount" in your data source, you can use the following formula: Sum(ThisItem.GroupedItems, Amount). Replace "Amount" with the appropriate field in your data source.
- Format the subtotal label as desired.
Step 3: Export data to Excel To export the data from your galleries to Excel, you can use the ExportToExcel function. Here's an example of how you can use it:
- Create a button or any other control that will trigger the export process.
- Add an OnSelect property to the control and set it to the following formula:
ExportToExcel(
ShowColumns(
AddColumns(
ParentGallery.AllItems,
"GroupedItems", ChildGallery.AllItems
),
"GroupName", "GroupedItems", "Subtotal"
),
"Report.xlsx",
"Sheet1"
)
In this formula, replace "ParentGallery" with the name of your parent gallery and "ChildGallery" with the name of your child gallery. Also, adjust the column names in the ShowColumns function to match your data source's field names.
- Customize the file name ("Report.xlsx") and the sheet name ("Sheet1") to your preference.
When you click the button or trigger the control with the OnSelect property, the data from the galleries, including the subtotals, will be exported to an Excel file named "Report.xlsx" with a sheet named "Sheet1".
Make sure to adjust the formulas and control names according to your specific app design and data source.
