1. Home
  2. Forum
  3. Blazor
  4. Running total / Balance

Running total / Balance

Hi team, 

Just like in excel Is this possible in the Grid?

Running total.png


Best Regards,

Tyrone


7 Replies
MS Monisha Saravanan Syncfusion Team January 25, 2022 08:57 AM UTC

Hi Tyrone, 

Greetings from Syncfusion support. 

Query: Just like in excel Is this possible in the Grid? 
 
Yes, it is possible to create running total in grid. You can use below mentioned method CalculateTotal. Here we used column template and calculated the sum of previous values programmatically and updated to the DataGrid template column. We had prepared an sample based on your requirement. Kindly refer the attached code snippet and sample for your reference. 
 
<SfGrid @ref="Grid" DataSource="@Orders" AllowPaging="true" Height="400"> 
    <GridColumns> 
... 
        <GridColumn HeaderText="Running Age Total" TextAlign="TextAlign.Right" Width="120"> 
            <Template> 
                @{ 
                    var test = @CalculateTotal(context as Order); 
                    <div>@test.Result</div> 
                } 
            </Template> 
        </GridColumn> 
    </GridColumns> 
</SfGrid> 
@code { 
    SfGrid<Order> Grid { get; set; } 
    public List<Order> Orders { get; set; } 
    public async Task<int?> CalculateTotal(Order val) 
    { 
        var index = Orders.IndexOf(val); 
        var total = Orders.Skip(0).Take(index + 1).Select(x => x.Freight).Sum(); 
        return total; 
    } 
} 
 



Kindly get back to us if you have further queries. 

Regards, 
Monisha 


TY Tyrone February 7, 2022 07:15 AM UTC


Hi Monisha, thanks, it worked like magic.


Btw, I still have concerns regarding running balance.

1) How will I apply this in groupings? Example. Group by Gender. There is running balance for Male and there is separated running balance for female as well.

2) Running balance column did not appear in excel and pdf export. I think template is not rendering. 


Best Regards,

Tyrone


VN Vignesh Natarajan Syncfusion Team February 8, 2022 07:09 AM UTC

Hi Tyrone,  
 
Thanks for the update.  
 
Query: “How will I apply this in groupings? Example. Group by Gender. ” 
 
Previously we have provided solution to achieve your requirement using Column Template feature to display custom calculated value by calling a custom function and calculation. Similar for grouped records, we suggest you to perform calculation by grouping the column in custom function using LINQ and display the calculated value.  
 
Refer the below code example. 
 
public int? CalculateTotal(Order val) 
    { 
        //to get grouped list. 
        //var check = Orders.GroupBy(x => x.CustomerID).Where(x => x.Key == val.CustomerID).ToList(); 
 
       return total; 
    } 
 
Please get back to us if you are facing any issues with above approach. 
 
Query: “Running balance column did not appear in excel and pdf export. I think template is not rendering.” 
 
Yes, to include the template column in pdf / excel export, we need to enabled the IncludeTemplateColumn property of ExportProperties. Also to display the custom value in column template, we suggest you to handle the actions in PdfQueryCellInfoEvent and ExcelQueryCellInfo event of Grid.  
 
Refer the below code example.   
 
<SfGrid ID="Grid" @ref="Grid" DataSource="@Orders" AllowGrouping="true" Toolbar="@(new List<string>() { "ExcelExport", "PdfExport" })" AllowPdfExport="true" AllowExcelExport="true" AllowPaging="true"> 
    <GridEvents OnToolbarClick="ToolbarClickHandler" ExcelQueryCellInfoEvent="ExcelQuery" PdfQueryCellInfoEvent="PdfQuery" TValue="Order"></GridEvents> 
    <GridColumns> 
        <GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn> 
        <GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn> 
        <GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn> 
        <GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" TextAlign="TextAlign.Right" Width="120"></GridColumn> 
        <GridColumn HeaderText="Running Age Total" TextAlign="TextAlign.Right" Width="120"> 
            <Template> 
                @{ 
                    var test = @CalculateTotal(context as Order); 
                    <div>@test</div> 
                } 
            </Template> 
        </GridColumn> 
    </GridColumns> 
</SfGrid> 
@code { 
    SfGrid<Order> Grid { get; set; } 
 
    public List<Order> Orders { get; set; } 
 
    public async void PdfQuery(PdfQueryCellInfoEventArgs<Order> args) 
    { 
       if (args.Column.HeaderText == "Running Age Total") 
        { 
            var total = CalculateTotal(args.Data); 
            args.Cell.Value = total.ToString(); 
        } 
    } 
    public void ExcelQuery(ExcelQueryCellInfoEventArgs<Order> args) 
    { 
        if (args.Column.HeaderText == "Running Age Total") 
        { 
            var total = CalculateTotal(args.Data); 
            args.Cell.Value = total; 
        } 
    } 
 
    public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args) 
    { 
        if (args.Item.Id == "Grid_excelexport") //Id is combination of Grid's ID and itemname 
        { 
            ExcelExportProperties ExportProperties = new ExcelExportProperties(); 
            ExportProperties.FileName = "test.xlsx"; 
            ExportProperties.IncludeTemplateColumn = true; 
            await this.Grid.ExcelExport(ExportProperties); 
        } 
        if (args.Item.Id == "Grid_pdfexport")  //Id is combination of Grid's ID and itemname 
        { 
            PdfExportProperties ExportProperties = new PdfExportProperties(); 
            ExportProperties.FileName = "test.pdf"; 
            ExportProperties.IncludeTemplateColumn = true; 
            await this.Grid.PdfExport(ExportProperties); 
        } 
    } 
 
Kindly refer the below sample for your reference.  
 
 
Refer our UG documentation for your reference 
 
 
Please get back to us if you have further queries.  
 
Regards, 
Vignesh Natarajan. 


TY Tyrone February 20, 2022 07:20 AM UTC

Thanks so much Vignesh, I'll get back to you as soon I tried the solutions


Best Regards,


Tyrone.


MS Monisha Saravanan Syncfusion Team February 21, 2022 04:59 AM UTC

Hi Tyrone,  
 
Thanks for the update.  
 
We will wait to hear from you. 
Please get back to us if you have further queries.  
  
 
Regards, 
Monisha 


PA Paolo September 10, 2024 03:51 PM UTC

Hi support,

I link to this thread, as my query is on the same subject.

I tried your first example and it works; but I have a slightly different requirement, as the list of records is in a descendant order based on date.

This is my result, where "Saldo" column has values calculated on DB side, and Saldo 2 column as per your example, client side:


Image_4774_1725983129714

So "Saldo 2" has to represent values as "Saldo", i.e. starting from the last row. Of course, I have modified your function to calculate the Saldo 2 value as a subtraction "Entrata" - "Uscita".

My requirements ask to have a running total using filtering; for example, if a fiter on "Conto" column, the running total as to calculate values only on the filtered rows.

May you suggest a feasible solution for both specific requirements: 1) running total starting from the bottom row to the first; 2) running total also taking into account only filtered rows

Best regards

Paolo Capirci


NP Naveen Palanivel Syncfusion Team September 11, 2024 11:34 PM UTC

Hi Paolo,

Query 1 : “ running total starting from the bottom row to the first;”

Based on your query, it seems you want to calculate the total starting from the bottom row and moving upwards. We would like to inform you that this can be done if the last value is known, as the calculation depends on it. If you have the last value, we can proceed with the calculation.

Query2 : “running total also taking into account only filtered rows”

From your query, it seems you want to calculate the running total based only on the filtered rows in the grid. For this, you can use the GetFilteredRecordsAsync method, which retrieves the filtered rows, and then base your calculations on those values. Please refer to the code snippet and sample for further clarification.

Sample : https://blazorplayground.syncfusion.com/embed/htVJDaWsnBQFdavG?appbar=true&editor=true&result=true&errorlist=true&theme=bootstrap5

  public async Task<int?> CalculateTotal(Order val)

  {

      var records = await Grid.GetFilteredRecordsAsync() as List<Order>;

      if (records != null)

      {

          var index = records.IndexOf(val);

          var total = records.Take(index + 1).Select(x => x.Freight).Sum();

          return total;

      }

      else

      {

          var index = Orders.IndexOf(val);

          var total = Orders.Skip(0).Take(index + 1).Select(x => x.Freight).Sum();

          return total;

      }

 



Regards,
Naveen


Loader.
Up arrow icon