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.
Skilled SharerHi Bernice,
When a total of (Beginning Balance - YTD Expenses + YTD Receipts) comes out as if BegBal were missing, the usual cause in paginated reports is the grain of BegBal rather than the formula.
dataset rows are at transaction/line level
BegBal is an account-level value -> either
(a) repeated on every line -> Sum() counts it N times (too high), or
(b) only on one line / NULL elsewhere -> the textbox shows it via First(),
but the total row adds the dataset rows, not what is displayedSince your detail rows display correctly, the total needs to add one BegBal per account and the YTD amounts per line. Assuming a row group named "Account" (use your group name, case-sensitive, in quotes):
=Sum(Max(Fields!BegBal.Value, "Account"))
- Sum(Fields!YTDExpenses.Value)
+ Sum(Fields!YTDReceipts.Value)The nested Sum(Max(..., "Account")) takes one balance per account group and then adds those up in the total row's scope. Put this in the total row outside the Account group (the tablix footer or a parent group's footer).
If any of the three can be NULL, guard it so a missing value counts as 0:
=Sum(Max(IIf(IsNothing(Fields!BegBal.Value), 0, Fields!BegBal.Value), "Account"))
- Sum(IIf(IsNothing(Fields!YTDExpenses.Value), 0, Fields!YTDExpenses.Value))
+ Sum(IIf(IsNothing(Fields!YTDReceipts.Value), 0, Fields!YTDReceipts.Value))The most robust option, if you can change the dataset query, is to return BegBal only once per account (or as a separate account-level dataset). Then a plain Sum of the encumbrance expression works everywhere, including grand totals and exports.
So my overall approach would be:
check: is BegBal repeated per line or only on one line?
-> total = Sum(Max(BegBal, "Account")) - Sum(YTDExpenses) + Sum(YTDReceipts)
-> place it outside the Account group scope; null-guard if needed
-> long term: return BegBal at account grain from the queryAI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
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.
v-abhinavmuCommunity Support
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.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.
