topic report builder - using dynamically created columns in expressions in Report Server

report builder - using dynamically created columns in expressions

Anonymous — Thu, 26 Aug 2021 16:26:55 GMT

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 provide for (eg. cost / sales) other expressions?

Re: report builder - using dynamically created columns in expressions

Icey — Mon, 30 Aug 2021 07:01:55 GMT

Hi Anonymous ,

i have a report that has a column group that supplies 2 columns dynamically: cost and sales

How do you get this? Any rules? If you could share us a sample, it would be great.

Reference: How to Get Your Question Answered Quickly - Microsoft Power BI Community

Best Regards,

Icey

Re: report builder - using dynamically created columns in expressions

Anonymous — Mon, 30 Aug 2021 11:05:15 GMT

there is a column in the table that is nvar but basically a bit as it displays cost or sales. the column in the report is grouped on that value so it produces 2 columns, 1 for each value

problem is i don't know how to then use those columns (which are totaling the cost or sales value for the row) in a further column to dertermine the "margin".

Re: report builder - using dynamically created columns in expressions

Anonymous — Thu, 02 Sep 2021 15:09:16 GMT

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))