I have the matrix above that displays the subtotals per customer(group_row), per quarter(group_column). I added a subtotal to the top group_column (year) so I can get grand total. I need to modify grand total formula to display variance instead.
I don't need to modify the subtotal formula per Customer/quarter , only per cutomer/Year. Is it possible?
You can use an IIf statement with an InScope check to determine which column or row you are in (i.e. are you inside the group still, or outside it) and have a different expression accordingly. Here is an example:
=IIf(
InScope("WeekCommencingGroup"),
DateDiff("d", CDate(Parameters!AsAtSnapshot.Label), CDate(Fields!Week_Commencing.Value)),
""
)
i use this to print a date difference in the regular rows, but not in the total row. You can use the same technique for your group, and can alter your expression in your Total column.
|||
Sluggy,
Thank you so much for your answer, but I'm not sure if you understand what I'm trying to do. The totals in my matrix are from the 'subtotal' function, not from a caculated column....
Perhaps I don't understand your answer...
|||I have a similar situation I think my shed more light on the OP's Question.
If I have a database of fruit and stores, I can easily link the returns from a dataset into a matrix and get the number of each fruit at each store, and subtotals.
Apples
Oranges
Bananas
Total
Store 1
5
3
1
9
Store 2
5
2
1
8
Store 3
4
8
4
16
Store 4
8
1
2
11
Store 5
6
7
7
20
28
21
15
64
What I want however, is some way to find the percentage of each fruit at each store.
Apples
Oranges
Bananas
Total
Store 1
5
( 56% )
3
( 33% )
1
( 11% )
9
Store 2
5
( 63% )
2
( 25% )
1
( 13% )
8
Store 3
4
( 25% )
8
( 50% )
4
( 25% )
16
Store 4
8
( 73% )
1
( 9% )
2
( 18% )
11
Store 5
6
( 30% )
7
( 35% )
7
( 35% )
20
28
( 44% )
21
( 33% )
15
( 23% )
64
This would be easy if there was a way to call the Total cell at the end of each row, (# apples in the store / store total) but if that is possible I am at a loss to find it.
|||For the fruit store example with percent of total, you just need to take the current value sum and divide by the row total. Do that with the sum function and scope.
The additional IIF statement ensures the report doesn't puke on a divide by 0.
=IIF(Sum(Fields!DATACOLUMN.Value,"matrix1_RowGroupName")=0,0,Sum(Fields!DATACOLUMN.Value)/IIF(Sum(Fields!DATACOLUMN.Value,"matrix1_RowGroupName")=0,1,Sum(Fields!DATACOLUMN.Value,"matrix1_RowGroupName")))
Replace DATACOLUMN with your sales field
Replace "matrix1_RowGroupName" with your matrix Row name
David Leibowitz
Business Intelligence Practice Manager
MaxQ Technologies
www.maxqtech.com
I tried the following:
=iif(InScope("matrix1_AtYear") and InScope("matrix1_customer"),sum(fields!count1.value),var(fields!count1.value))
The Grand total will display the Variance, however it looks at the dataset to caculate this value. I'm doing all my grouping caIculation at the report level, not at the data set level. I want it to look at the total per quarter columns and use those number to come up with a variance. For instance the first row of the matrix I displayed above, I want to see (1001-200)/1001*100 = 80 .
|||
My Matrix name is Matrix1, my Row name is MatrixRow4 and the number value is "= Count(Fields!FruitDesc.Value)"
Code Snippet
=IIF(Sum(Count(Fields!FruitDesc.Value),"MatrixRow4")=0,0,Count(Fields!FruitDesc.Value)/IIF(Sum(Count(Fields!FruitDesc.Value),"MatrixRow4")=0,1,Sum(Count(Fields!FruitDesc.Value).Value,"MatrixRow4")))
On preview the report throws:
[rsAggregateofAggregate] The Value expression for the textbox 'textbox24' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions. [rsAggregateofAggregate] The Value expression for the textbox 'textbox24' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions. [rsAggregateofAggregate] The Value expression for the textbox 'textbox24' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions. [rsInvalidAggregateScope] The Value expression for the textbox ‘textbox24’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set. [rsInvalidAggregateScope] The Value expression for the textbox ‘textbox24’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set. [rsInvalidAggregateScope] The Value expression for the textbox ‘textbox24’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set. Build complete -- 6 errors, 0 warnings|||
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1494632&SiteID=1
Possible helpful answer...
|||Rageheart,
I hope this helps you or anyone else because I had the same problem.
Instead of "MartrixRow4" you need to right click on the detail and select 'Edit Group' from the dropdown menu. Use the name that is there, not the name of the row.
sql
No comments:
Post a Comment