Post reply

Division between two Summed fields giving errors

  • May 11, 2021 at 8:39 pm

    Go to Answer

    #3882069

    Hi,

    I am trying to get a Percentage of Budget field added to my Budget to Actuals report and the simplistic formula is supposed to be [(Actuals + Encumbrance) / Budget] with expected results shown below in red.

    Issue

    To provide a little more context, below is a rough data-table structure from which the Budget, Encumbrance, Actual and Remaining were extracted.

    Data-table

    Calculated Formulas I have used are:

    • Budget: =iif(Fields!a_type.Value="B",a_amount,"0")
    • Actual: =iif(Fields!a_type.Value="A",a_amount,"0")
    • Encumbrance: =iif(Fields!a_type.Value="E",a_amount,"0")
    • Remaining: =Fields!Budget.Value-Fields!Encumbrance.Value-Fields!Actual.Value

     

    Since all fields used in the table are sum, I didn't have to use the sum function anywhere.

     

    Now for Percentage of Budget, I have tried the following and none of them work (some give me infinite, whereas others give me error "Failed to preview report. The expression used for the calculated field 'PercentageOfBudget' includes an aggregate, Row Number, RunningValue, Previous or lookup function").

    Percentage of Budget formulas I have tried:

    • =IIF(RunningValue(Fields!Budget.Value, Sum, "DataSet1") = 0, 0, (RunningValue(Fields!Encumbrance.Value, Sum, "DataSet1") + RunningValue(Fields!Actual.Value, Sum, "DataSet1")) / RunningValue(Fields!PercentageOfBudget.Value, Sum, "DataSet1"))
    • (RunningValue(Fields!Encumbrance.Value, Sum, "DataSet1") + RunningValue(Fields!Actual.Value, Sum, "DataSet1")) / RunningValue(Fields!PercentageOfBudget.Value, Sum, "DataSet1")
    • =IIF(Sum(Fields!Budget.Value) = 0, 0, (Sum(Fields!Actual.Value) + Sum(Fields!Encumbrance.Value)) / Sum(Fields!Budget))
    • (Sum(Fields!Actual.Value) + Sum(Fields!Encumbrance.Value)) / Sum(Fields!Budget)
    • =IIF(Fields!Budget.Value = 0, 0, (Fields!Actual.Value + Fields!Encumbrance.Value) / Sum(Fields!Budget)
    • =(Fields!Actual.Value + Fields!Encumbrance.Value) / Fields!Budget

    I have also tried the coding Report Properties and then replacing the formula with code.division(Fields!Actual,Fields!Budget). No luck whatsoever.

    Any assistance you can provide will be greatly appreciated.

  • May 12, 2021 at 8:37 pm

    #3882748

    Sad bump, no one knows 🙁

  • May 13, 2021 at 2:20 am

    #3882836

    well, when I ran the CREATE TABLE and INSERT scripts, nothing happened, so I couldn't reproduce the problem.

  • May 14, 2021 at 3:12 pm

    #3883484

    Hi,

    Can you please elaborate? When you say nothing happened - as in, you saw the proper percentages as shown in picture 2 red and green text?

     

    If so, which formula did you use for Percentage of Budget field? And what did you use in the table, aggregate, average or sum etc.

  • May 17, 2021 at 7:10 pm

    Answer

    #3884543

    I found my answer by looking at this video: https://www.youtube.com/watch?v=Zebwsa-3QZQ&list=PLNIs-AWhQzcmEFHyxCRwA_gb29WOz5SJU&index=44

    Solution:

    • Function could not be a calculated field but a column with expression.
    • On non-aggregate rows needed to use the following formula with an extra caveat on summing at what level (ObjectGroup is simply a name I chose when grouping a_object together).

      • = iif (SUM(Fields!Budget.Value, "ObjectGroup") = 0, "-", ( SUM(Fields!Actual.Value, "ObjectGroup") + SUM(Fields!Encumbrance.Value, "ObjectGroup") ) / ( SUM(Fields!Budget.Value, "ObjectGroup") ) )

    • On aggregate rows, there was no need to specify at what level (otherwise the report breaks). Use a formula like below:

      • = iif (SUM(Fields!Budget.Value) = 0, "-", ( SUM(Fields!Actual.Value) + SUM(Fields!Encumbrance.Value) ) / ( SUM(Fields!Budget.Value) ) )

    That is all. Super easy once you learn the trick.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply