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.
| 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.54Direct Cost of Sales $2,268,245.00 $1,517,080.61Gross Margin $5,923,225.00 $2,733,252.93Gross Margin % 72.30% 64.31%Indirect Cost of Sales $0.00 $284,135.37Net Sales Margin $5,923,225.00 $2,449,117.56Net Sales Margin % 72.30% 57.62%given values are: revenue, direct cost of sales, Indirect Cost of Salescalculated values:gross margin = revenue-direct cost of salesgrosss margin % = gross margin / revenuenet sales margin = gross margin - Indirect Cost of Salesnet sales margin % = net sales margin / revenueMy 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)etci 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 MVPhttp://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_actualfor 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_actualfor 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 MVPhttp://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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
calculated matrix value