CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2012reportbuilder
user2672332
user2672332

Reputation: 55

Finding difference between columns in column group

I am using report builder to create a report showing a budget for a project. The dataset includes line items for both budget and projected. See below for example rows. I am using a matrix with column group to display budget and projected side by side as well as a row group to show section, category, etc. I need to have a variance column that subtracts projected from budget.

I have scoured the interwebs for solutions but nothing that has worked so far. I feel like there has to be simple solution to this given it is something that could be done in a sql query with zero effort. Most solutions are assuming I have two separate fields, but these are dynamic fields pull out with the column group. 

Dataset Row Samples
Type      Section  Cateogry Phase            Task             Total
Budget    Building Kitchen  Pre-Construction Cabinet Hardware $100 
Projected Building Kitchen  Pre-Construction Cabinet Hardware $220 

Report sample


           COL GROUP        This is the column i want
           Budget Projected Variance
+Buidling  $100   $220      -$120
+Kitchen  
+Pre-Con

EDIT: I tried the below solution without success and have already visited every link provided in the second answer. Maybe there is something I am missing, but I ended up just doing everything in the SQL query and not use Column groups. This is 100% the simplest solution. I am very surprised there is no easy way to reference individual columns in a column group. The below may work for others, but I just could not get them to work for me. Not sure why.

Upvotes: 2

Views: 1885

Answers (2)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

You could add an additional column inside the “Type” group (provided that this is the name of your column group). Set the Column Visibility to hide the column by an expression like

= IsNothing(Previous(Field!Type.Value, “Type”)

Calculate the values for that column as

= Previous(Sum(Fields!Total.Value), “Type”) – Sum(Fields!Total.Value)

That should calculate the difference between the values of the previous type and the current type, and only show that column for the "Projected" type (when there is a previous type).

Upvotes: 1

Hadi
Hadi

Reputation: 37368

On the matrix, you can use the group subtotals to achieve this, you only have to overwrite the SUM operation with an expression that subtract to values. There are many link mentioning how to do that or that can helps you:

Upvotes: 0

Related Questions