Wednesday, August 14, 2019

How to get running totals in view

Let's say we need to see bank account balances per day with running totals.

A running totals query is a query in which the total for each record is a summation of that record and any previous records. This type of query is useful for displaying cumulative totals over a group of records (or over a period of time) in a graph or report.

The simplest way to achieve it is to create a view for BankAccountTrans table and add any groupings you need.

In my example, these are Bank account, transaction currency, financial dimension set, and, of course, transaction date.

I use standard aggregation Sum for my daily total and a computed column for running total.

The computed column method reads as follows

public class myBankAccTransAggrView extends common
    private static server str amountCurRunningTotal()   // X++
        return "sum(sum(t1.AMOUNTCUR)) OVER (PARTITION by t1.DATAAREAID, t1.ACCOUNTID, t1.CURRENCYCODE  order by t1.TransDate)";


It is ready for consumption; enjoy your BI inside of D365!

No comments: