Creating a variance column in a Nested Crosstab

[Poochi62]

I am trying to create a variance column for a nested crosstab where I have Years, and then Budgets and Actuals nested underneath each year.  All the data is coming from a relational data-source.  Right now I only have Budget and Actuals side by side for each year.  I need a new column for the variance for each year.

For each year, I simply want the variance between budget and actual.
For current year, I simply want the variance between this year's budget and last year's.

Example in attachment.  If this was based on a cube, I would need a tuple.  Not sure how to do this with relational?  I am experimenting with the variance formula but don't know the exact syntax to use.

                         

[sdf]

can you try?

([Acatual] - [Budget]) ----> can create new item for [Var]

total([Var] FOR [YEAR])

[Poochi62]

It is not as simple as [Actual] - [Budget] because these are not separate fields. They are from the same field, but nested under the year.  Basically I need to subtract the nested values.  Then in addition to figuring that out, I need to subtract one year's budget from another.

[Lynn]

It is not as simple as [Actual] - [Budget] because these are not separate fields. They are from the same field, but nested under the year.  Basically I need to subtract the nested values.  Then in addition to figuring that out, I need to subtract one year's budget from another.

Do you mean that you have one measure field set as the default measure for the crosstab and then another attribute that identifies if the figure pertains to budget or actual? There must be something that causes the single amount field to split into budget and actual in the layout.

If this is the case then you can create two data items in the report to split your amount into two separate fields:

Code Select Expand


#/* expression for a data item called Budget Amount */#
case
  when [Amount Type] = 'Budget' then [Amount]
  else 0
end


Code Select Expand


#/* expression for a data item called Actual Amount */#
case
  when [Amount Type] = 'Actual' then [Amount]
  else 0
end


Now you can nest these two separate query items beneath your year and use them in further computations. It is situations like these where one appreciates the power of an OLAP source  :)

[Poochi62]

Yes that is exactly it.  I have 1 measure field and another attribute that designates 'Budget' or 'Actual'.  They are nested under each 'Year'.  I could do as you say and make 2 separate data items within the query or better yet in my data-source, but clearly there is a function or way to do this without having to do that?

Also how do I subtract Year's in different columns, for example subtract 2016 Budget from 2015 Budget?

[Poochi62]

Okay, as suggested I created 2 separate data items and then nested those under Years.  Now I just need to be able to create 2 new variance columns for:

2016 Budget - 2015 Budget
2016 Budget - 2015 Actuals

Is this possible?  See example attached.

[Lynn]

Yes that is exactly it.  I have 1 measure field and another attribute that designates 'Budget' or 'Actual'.  They are nested under each 'Year'.  I could do as you say and make 2 separate data items within the query or better yet in my data-source, but clearly there is a function or way to do this without having to do that?

Also how do I subtract Year's in different columns, for example subtract 2016 Budget from 2015 Budget?

The best way to do this without having to create data items or complicated expressions is to build a cube or DMR.

If you want to calculate A minus B then you kinda-sorta-really need an A and a B in order to write the formula. Or at the very least you need some way to segregate the figures that go with A from those that go with B. You can either create two data items as a first step and then reference them in a subtraction formula as a second step. Alternatively, you can do it all in one big ugly formula that does both steps at the same time.

When you use a crosstab layout against a relational data source a little mini-cube is spun up to fulfill the request. That means you can use dimensional functions if you prefer them to the relational case expressions I provided previously.

In the example below I used the sample GO Sales (query) package. This gets the total for the default measure of the crosstab in the last year we have data for (2013) and subtracts it from the total for the year before (2012). One formula, one step, a little mind-bending and probably more difficult to understand at first.

Code Select Expand


aggregate (currentMeasure within set tail ( tail ( [Sales (query)].[Time].[Year], 2 ) ) )
-
aggregate (currentMeasure within set head ( tail ( [Sales (query)].[Time].[Year], 2 ) ) )


If we look at it in pieces, the inner most expression returns a set that is the last two years available in the database. The actual data runs from 2010 to 2013.

Code Select Expand


tail ( [Sales (query)].[Time].[Year], 2 )


The above is wrapped in another "tail" function to get the latest year. That whole bundle defines the set for which we want to aggregate the current measure. The part after the minus sign is exactly the same, except the "head" function is used to get the first member of our last two years set.

There are lots of different ways to do things so if someone else can chime in with a really simple way to do what you want from a relational model then I'd be really eager to learn about it also.

[Poochi62]

Here's how I solved this problem without creating a cube:
1. Created 2 new data-fields of BUDGET and ACTUAL using the Case statements suggested by Lynn (columns 1 & 2 in attachment).
2. Then created a 3rd data-field expression of simply: (BUDGET - ACTUAL).
3. Then dragged a new Query Expression into my crosstab at the highest level creating the #4 column (shown in attachment).  In the expression I subtracted 2 Case statements representing the 2016 Budget and 2015 Actual:
(Case when [YEAR] = '2016' and [TYPE] = 'BUDGET' then [AMOUNT_BUDGET] else 0
end)
-
(Case when [YEAR] = '2015' and [TYPE] = 'ACTUALS' then [AMOUNT_ACTUAL] else 0
end)
4.  Then dragged in another new Query Expression into my crosstab creating the #5 column (shown in attachment) at the highest level of the crosstab.  In the expression I subtracted 2 Case statements that represent the 2016 Budget and 2015 Budget:
(Case when [YEAR] = '2016' and [TYPE] = 'BUDGET' then [AMOUNT_BUDGET] else 0
end)
-
(Case when [YEAR] = '2015' and [TYPE] = 'BUDGET' then [AMOUNT_BUDGET] else 0
end)
5. Made sure to set the Aggregate function to 'Total' for both this new Query Expression columns.
6. Magnifico! ;)