Matrix subtotal - Subtracting values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Matrix subtotal - Subtracting values

Author

Topic

DHL
Starting Member

16 Posts

Posted - 2007-11-05 : 07:33:37

Hi,

I have a matrix which looks as follows:
----- Actuals | Budget
ItemA 20 | 50
ItemB 45 | 30
ItemC 20 | 15

I would like to add a column with the difference between actuals and budget.
Actuals/budget is 1 group in the matrix being the dimensionname.
How can I calculate the difference (subtract actuals and budget), because if I just use the subtotal
function it adds both totals together and I need to have the difference
between them?

Please help...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 08:14:51

To create calculated members, use the Calculations tab in Cube Designer.
You can create the value for a calculated member using the existing measures Actuals and Budgets.

E 12°55'05.25"
N 56°04'39.16"

DHL
Starting Member

16 Posts

Posted - 2007-11-05 : 08:23:58

I am using visual studio for the reporting. I am really new to all this, but where can I find this cube designer?

Will H
Yak Posting Veteran

56 Posts

Posted - 2007-11-05 : 12:10:22

First, add in another column where you want the subtotal to display. Then, change the expression to:

=SUM(Fields!Budget.value) - SUM(Fields!Actuals.value)

Where the Budget/actuals is the name of the field in your dataset. If I'm reading your problem correctly, subtotals are not the way to approach it.

---------------------------------------------------------
SSRS Kills Kittens.

DHL
Starting Member

16 Posts

Posted - 2007-11-06 : 02:57:54

Thanks for the update, but this is not working. Both the actuals and budget data are in the same grouping under "matrix1_ColumnGroup4".

I need to be able to subtract 2 values (actuals - budget) within the same grouping.

Please help as this is very important to me...

Thanks!

DHL
Starting Member

16 Posts

Posted - 2007-11-07 : 07:25:53

Does anyone can help with this? It is really important and I need your expertise...

Thanks!!

tjwent69
Starting Member

30 Posts

Posted - 2007-11-07 : 08:26:59

This might help a little. I ran across some help but it's not very much. It's seems too simple. Maybe you can get the column then modify it.
http://technet.microsoft.com/en-us/library/ms159652.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources