Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

report builder - using dynamically created columns in expressions

i have a report that has a column group that supplies 2 columns dynamically: cost and sales   i want to use the results here in other expressions in adjacent columns   what is the syntax to provi...
Dynamic Column
  • Anonymous's avatar
    Anonymous
    4 years ago

    it's actually not that bad.  add a column, use the scoped sum function (=Sum(Fields!YearlyIncome.Value,"GroupByInitial") where "GroupByInitial" is the name of the target group) and the name of the group and add an if statement to exclude the undesired values(all months except last month)  and a group filter to exclude the parameter of the other dynamically provided column "cost".  in this case i only wanted to sum if the type = sales.  with no "cost" data to return, only the 1 row ("sales") is dynamically provided.

     

    =Sum(IIF(Fields!GLPeriodBal_FiscalPeriod.Value = Month(Today) - 1,Fields!Calculated_REBalance_Amt.Value , nothing),"Domestic") / Sum(IIF(Fields!GLPeriodBal_FiscalPeriod.Value = Month(Today) - 1, Fields!Calculated_REBalance_Amt.Value, nothing))

Anonymous's avatar
Anonymous
Not applicable
4 years ago

it's actually not that bad.  add a column, use the scoped sum function (=Sum(Fields!YearlyIncome.Value,"GroupByInitial") where "GroupByInitial" is the name of the target group) and the name of the group and add an if statement to exclude the undesired values(all months except last month)  and a group filter to exclude the parameter of the other dynamically provided column "cost".  in this case i only wanted to sum if the type = sales.  with no "cost" data to return, only the 1 row ("sales") is dynamically provided.

 

=Sum(IIF(Fields!GLPeriodBal_FiscalPeriod.Value = Month(Today) - 1,Fields!Calculated_REBalance_Amt.Value , nothing),"Domestic") / Sum(IIF(Fields!GLPeriodBal_FiscalPeriod.Value = Month(Today) - 1, Fields!Calculated_REBalance_Amt.Value, nothing))