Dividing first two columns in matrix table
Hey,
I've got matrix table like this:
year
month sum()
Preview looks like this
2009 2010
feb 10 3
mar 20 4
I need to add a column named Index (sec column/first column * 100) like this:
2009 2010 Index
feb 10 3 30
mar 20 4 20
Please help
December 13th, 2010 7:24am
Hi,
As a workaround, we can use 2 adjacent columns group, one for 2009 and the other for 2010. And use another static column for the Index. Please refer to the following steps:
Add a filter to the current Year group.
1.
Right click the Year group in
Column Groups pane, and select Group Properties…
2.
In the
Group Properties window, select Filters in the left list.
3.
Click
Add to add a filter. Then, specify the Expression to the Year field,
Operator to =, and Value to 2009.
4.
Click
OK.
Add an adjacent year column group.
1.
Right click the handle of the Year column, and select
Add Group -> Column Group -> Adjacent Right…
2.
In the
Tablix group dialog, select the Year field in the Group by dropdown list. Then, click
OK.
3.
Add a filter to the new Year column group by the steps above, and set the filter value to 2010.
Add a static column for Index.
1.
Right click the handle of the second Year column, and select
Insert Column -> Outside Group – Right.
2.
In the cell of the new column, specify an expression to calculate the Index.
For example, if the textbox name in the first Year column is Textbox1 and in the second Year column is Textbox1, we can use the expression like
=ReportItems!Textbox1.Value/ReportItems!Textbox2.Value*100
Please correct the Textbox names in the expression based on your report.
Thanks,
Tony Chain Tony Chain [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Free Windows Admin Tool Kit Click here and download it now
December 15th, 2010 12:17am
Thank you for reply,
I already solved thi by adding two calculated fields. One for 2009 and one for 2010. Then I added static column and just used expression calcField2.value/calcField1*100
Thanks again for very detail explanation.
December 15th, 2010 2:33am