Report or Framework question

[terry_stjean]

I have a table designed as follows:
Year
Customer,
Item,
Budget Amount
Actual Amount.
I have been asked for a report which shows Customer as a section.
Item as the row and Budget Amount and Actual Amount per year as the columns.
Easy enough.
I have also been asked to have variance columns which is where I'm stumped.
For example a column (2007 Budget - 2006 Actual).

I can't figure out how to do this in Report Studio.
I have both relational and dimensional data built over this table.
Would I have to put the calculation in Framework manager.
I know I can add a calc (Budget - Actual), but how do I get each measure based on different years.
It's almost like I have to pivot the data so each row has each measure per year.

Any ideas?

[rockytopmark]

Create two queries... one each for the Budget Year and the other for the Actual Year.  Include the Key dimensional Query Items in both, and of course the proper amount column. 

Each of the queries should have a filter to reduce to rows to the desired year.

Next create a Join Query and include the two queries, and set the relationships using all the dimensional elements, except the date/time.

In the Join Query, include all the Dimensional Elements, and the two amount columns, one from each of the underlying queries, as desired.  Now you can create your calculated "Variance" column between those two amount columns.