Finding difference between columns in column group

Question

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.

I think you got 2 good answers that solves the issue. if the issue is solved you have to accept an answer also you can upvote helpful answer. check the [tour page](https://www.stackoverflow.com/tour) — Yahfoufi, Feb 06 '19 at 12:56

score 1 · Answer 1 · answered Feb 03 '19 at 21:38

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

score 0 · Answer 2 · answered Feb 03 '19 at 19:26

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:

Finding difference between columns in column group

2 Answers2