| Author |
Topic |
|
nehj76
Starting Member
15 Posts |
 Posted - 2011-12-20 : 10:41:38
|
Hi. I need help on how to insert formula and how to format conditionally in matrix cell.
I have a report that looks like this:
Budget Actual
Revenue $8,191,470.00 $4,250,333.54
Direct Cost of Sales $2,268,245.00 $1,517,080.61
Gross Margin $5,923,225.00 $2,733,252.93
Gross Margin % 72.30% 64.31%
Indirect Cost of Sales $0.00 $284,135.37
Net Sales Margin $5,923,225.00 $2,449,117.56
Net Sales Margin % 72.30% 57.62%
given values are: revenue, direct cost of sales, Indirect Cost of Sales
calculated values:
gross margin = revenue-direct cost of sales
grosss margin % = gross margin / revenue
net sales margin = gross margin - Indirect Cost of Sales
net sales margin % = net sales margin / revenue
My problem is how to insert this formula in a matrix cell to be able to acheive this calculated values and how to conditionally format the cell to percent if row is gross margin % and net margin %
Help would be greatly appreciated! Thank you so much in advance.
|
|
|
nehj76
Starting Member
15 Posts |
Posted - 2011-12-20 : 10:43:40
|
| for some reasons, my sample table didnt format properly |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-20 : 13:10:11
|
the expressions will be like
=SUM(Fields!grossmargin.value)/SUM(Fields!revenue.value)
etc
i dont think you need conditional formatting as each row is separate. its just a matter of selecting cell properties and then selecting one of standard % or currency formats based on what row displays.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
nehj76
Starting Member
15 Posts |
Posted - 2011-12-21 : 11:00:28
|
thank you for the reply. however,how do i reference each cell in a grouped matrix cell?
how can i apply the formula if the matrix cell is grouped by column and row?
for column, it is grouped by budget_actual
for rows, it is grouped by description (revenue, cost of good sold, gross margin etc..)
thank you.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-21 : 11:49:28
|
quote:
Originally posted by nehj76
thank you for the reply. however,how do i reference each cell in a grouped matrix cell?
how can i apply the formula if the matrix cell is grouped by column and row?
for column, it is grouped by budget_actual
for rows, it is grouped by description (revenue, cost of good sold, gross margin etc..)
thank you.
ok in that case you can check the description and see if it has % in it and then set percentage corresponding to % like ###.#0%
and for other making it currency like $##,##,###,#0
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
nehj76
Starting Member
15 Posts |
Posted - 2011-12-22 : 11:01:53
|
thanks. my question is how to code that using inscope? how to insert formula in a matrix cell.
|
|
|
|
nehj76
Starting Member
15 Posts |
Posted - 2011-12-22 : 11:01:53
|
thanks. my question is how to code that using inscope? how to insert formula in a matrix cell.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-22 : 11:59:24
|
quote:
Originally posted by nehj76
thanks. my question is how to code that using inscope? how to insert formula in a matrix cell.
what are groupings present in matrix? you need to use proper group name inside InScope
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
|
calculated matrix value