Forum Discussion
Paginated Report - Calculated Field
Hi Bernice,
Thanks for the additional details.Since the Unexpended Balance is calculated as Beginning Balance − YTD Expenses + YTD Receipts, I recommend checking the aggregation scope of the total expression and how BegBal is represented in the underlying dataset.
According to the Microsoft documentation, Sum aggregates non-null numeric values within the specified scope. If no scope is specified, it uses the current scope, which depends on where the expression is placed in the report. The documentation also explains that the scope of an expression affects which data is included in an aggregate calculation.
Could you check whether BegBal is repeated across multiple dataset rows for each fund type, or whether it appears only once? Also, please confirm the row group associated with the fund type and where the total expression is placed. This information will help determine the appropriate aggregation for the Unexpended Balance and the resulting End Balance.
For more details, please refer to the below Official Microsoft Documentation:
• Sum function in a paginated report - Microsoft Report Builder & Power BI Report Builder | Microsoft Learn
• Expression scope for totals, aggregates, and built-in collections in a paginated report - Microsoft Report Builder & Power BI Report Builder | Microsoft LearnI hope this helps. Please feel free to reach out if you have any further questions.
Thank you.
A BegBal is displayed once for every fund type, i.e General Fund, State Highway. Each fund type also calculates MTD Receipts (sum of receipts during that calendar month), YTD Receipts (sum of receipts for the year for given fund), MTD Expenses (sum of expenses during that calendar month), YTD Expenses (sum of expenses for the year for given fund), Unexpended Bal (calculated field - BegBal-YTDExpenses+YTDReceipts), Outstanding Encumbrance - calculated field - single value based on fund type), and EndBal (calculation - Unexpended Bal - Outstanding Encumbrance). Unexpended Balance is the only calculation that is not calculating properly. But it also is throwing off the End Bal but the calculation is working based on the data that is being displayed.
Community SupportHi Bernice,
Thanks for the additional details.
Since the Unexpended Balance is calculated as Beginning Balance − YTD Expenses + YTD Receipts, I recommend checking the aggregation scope of the total expression and how BegBal is represented in the underlying dataset.
According to the Microsoft documentation, Sum aggregates non-null numeric values within the specified scope. If no scope is specified, it uses the current scope, which depends on where the expression is placed in the report. The documentation also explains that the scope of an expression affects which data is included in an aggregate calculation.
Could you check whether BegBal is repeated across multiple dataset rows for each fund type, or whether it appears only once? Also, please confirm the row group associated with the fund type and where the total expression is placed. This information will help determine the appropriate aggregation for the Unexpended Balance and the resulting End Balance.
For more details, please refer to the below Official Microsoft Documentation:
• Sum function in a paginated report - Microsoft Report Builder & Power BI Report Builder | Microsoft Learn
• Expression scope for totals, aggregates, and built-in collections in a paginated report - Microsoft Report Builder & Power BI Report Builder | Microsoft Learn
I hope this helps. Please feel free to reach out if you have any further questions.
Thank you.
I had forgotten that Beg Bal wasn't a straight forward "pick this value", once I carried the formula forward for the totals, that solved my issues. Thank you for your response.