Matrix Report Calculation Issue.

Hi There,
I have this matrix report that shows sales for last couple of years and is
grouping on 5 different regions and separate years on different columns, I
want to calculate the % Sales for each region in another column. how do I
accomplish this in Reporting Services? I think the static cell on a matrix
don't allow this, I have been trying to somehow call the single cell name
from a Expression but no luck. I think this could be accomplish with some
code, if it is, can you please give some samples or direct me to a place
where I could research this topic a bit more?
any advices would be really appriaciated.
Thanks very much in advance.
MannyHi Manny:
There is a way to achieve this using "IIF" and "InScope" function. I did it
on a Matrix Report and it will dynamically calculate the percentage based the
Row/Column grouping.
Manny wrote:
>Hi There,
>I have this matrix report that shows sales for last couple of years and is
>grouping on 5 different regions and separate years on different columns, I
>want to calculate the % Sales for each region in another column. how do I
>accomplish this in Reporting Services? I think the static cell on a matrix
>don't allow this, I have been trying to somehow call the single cell name
>from a Expression but no luck. I think this could be accomplish with some
>code, if it is, can you please give some samples or direct me to a place
>where I could research this topic a bit more?
>any advices would be really appriaciated.
>Thanks very much in advance.
>Manny
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200612/1|||Can you give us a sample on how you accomplished this please?
Thanks,
Manny
pmak wrote:
>Hi Manny:
>There is a way to achieve this using "IIF" and "InScope" function. I did it
>on a Matrix Report and it will dynamically calculate the percentage based the
>Row/Column grouping.
>>Hi There,
>[quoted text clipped - 12 lines]
>>Manny|||Here is the example:
IIF(InScope("matrix1_RowGroup1"), IIF(InScope("matrix1_ColumnGroup1"),Sum
(Fields!Funding.Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup1"),Sum
(Fields!Funding.Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup2")),IIF
(InScope("matrix1_Program"),IIF(InScope("matrix1_ColumnGroup1"),Sum(Fields!
Funding.Value,"matrix1_Program")/Sum(Fields!Funding.Value,
"matrix1_ColumnGroup1"),Sum(Fields!Funding.Value,"matrix1_Program")/Sum
(Fields!Funding.Value,"matrix1_ColumnGroup2")),""))IIF(InScope
("matrix1_RowGroup2"), IIF(InScope("matrix1_ColumnGroup1"),Sum(Fields!Funding.
Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup1"),Sum(Fields!Funding.
Value)/Sum(Fields!Funding.Value,"matrix1_ColumnGroup2")),IIF(InScope
("matrix1_RowGroup3"),IIF(InScope("matrix1_ColumnGroup1"),Sum(Fields!Funding.
Value,"matrix1_Program")/Sum(Fields!Funding.Value,"matrix1_ColumnGroup1"),Sum
(Fields!Funding.Value,"matrix1_Program")/Sum(Fields!Funding.Value,
"matrix1_ColumnGroup2")),""))
Paul
Manny123 wrote:
>Can you give us a sample on how you accomplished this please?
>Thanks,
>Manny
>>Hi Manny:
>[quoted text clipped - 7 lines]
>>Manny
--
Message posted via http://www.sqlmonster.com

Matrix PDF

Is there some sort of calculation to make a matrix export to pdf without page breaks?
I have
2 dynamic rows
1 dynamic column
1 value field
Please help
thanksPlease make sure that your report is setup as follows:
Report.PageWidth - Report.LeftMargin - Report.Right Margin) >=Body.Width
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"AHH" <AHH@.discussions.microsoft.com> wrote in message
news:CE79F248-E5B9-4500-84C6-5CB91195BA6C@.microsoft.com...
> Is there some sort of calculation to make a matrix export to pdf without
page breaks?
> I have
> 2 dynamic rows
> 1 dynamic column
> 1 value field
> Please help
> thanks
>|||Would you please post or send me your RDL? I will need it to understand your
situation better.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"AHH" <AHH@.discussions.microsoft.com> wrote in message
news:8F4A4F3C-63DD-4F66-B24C-952BBF52A728@.microsoft.com...
> Report.PageWidth - Report.LeftMargin - Report.Right Margin = 8
> My body.width is 3.5
> Thanks
>
> "Bruce Johnson [MSFT]" wrote:
> > Please make sure that your report is setup as follows:
> > Report.PageWidth - Report.LeftMargin - Report.Right Margin) >=> > Body.Width
> >
> > --
> > Bruce Johnson [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "AHH" <AHH@.discussions.microsoft.com> wrote in message
> > news:CE79F248-E5B9-4500-84C6-5CB91195BA6C@.microsoft.com...
> > > Is there some sort of calculation to make a matrix export to pdf
without
> > page breaks?
> > >
> > > I have
> > > 2 dynamic rows
> > > 1 dynamic column
> > > 1 value field
> > >
> > > Please help
> > >
> > > thanks
> > >
> >
> >
> >|||How many ReferringPhysicians do you have? If you are expecting the matrix to
fit to a specific size as column groups are added, this is not supported.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"AHH" <AHH@.discussions.microsoft.com> wrote in message
news:8F4A4F3C-63DD-4F66-B24C-952BBF52A728@.microsoft.com...
> Report.PageWidth - Report.LeftMargin - Report.Right Margin = 8
> My body.width is 3.5
> Thanks
>
> "Bruce Johnson [MSFT]" wrote:
>> Please make sure that your report is setup as follows:
>> Report.PageWidth - Report.LeftMargin - Report.Right Margin) >=>> Body.Width
>> --
>> Bruce Johnson [MSFT]
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "AHH" <AHH@.discussions.microsoft.com> wrote in message
>> news:CE79F248-E5B9-4500-84C6-5CB91195BA6C@.microsoft.com...
>> > Is there some sort of calculation to make a matrix export to pdf
>> > without
>> page breaks?
>> >
>> > I have
>> > 2 dynamic rows
>> > 1 dynamic column
>> > 1 value field
>> >
>> > Please help
>> >
>> > thanks
>> >
>>|||I made all of the column widths (the data) the same to no avail.
I also tried making the data conatained within the dynamic rows the same width in addition to making the dynamic column's data the same width and still get blank pages
from the documentation, the report should wrap to the next page
This only happens for PDF export - all other formats are fine
Still working on it
if you have anymore ideas please let me know - been at this for 30 hours now. :)
Later, allen|||I took this issue offline with AHH and was able to determine that a bug is
causing the problem.
ISSUE:
Periodically, blank pages are inserted into the a PDF rendering.
DISCUSSION:
The report that caused the problem contained a list that contained a matrix.
What appears to be occurring is that the list did not resize to the width of
the matrix - its width continued to expand until it was wider than the
report page width. At this point a blank page was inserted into the
rendering. If a workaround is discovered it will be posted on this thread. A
fix for this should appear in a future service pack or release.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"AHH" <AHH@.discussions.microsoft.com> wrote in message
news:CE79F248-E5B9-4500-84C6-5CB91195BA6C@.microsoft.com...
> Is there some sort of calculation to make a matrix export to pdf without
page breaks?
> I have
> 2 dynamic rows
> 1 dynamic column
> 1 value field
> Please help
> thanks
>

Matrix calculation using more than one column

I have a matrix of data for a period of months. What I am trying to do is to
add a column at the end which will be YOY change. So how in a matrix do I
reference the value in column 4 (for example) and the last column. Also how
do I add this calculated column to the end of the matrix.For the column at the end, right click your column group and click Subtotal.
If your months show only one field, make sure the expression for this field
is =sum(Fields!MyField.value), and the total column will sum all the months
in the last column.
I'm not sure what you mean by YOY change. If this is something else than
summing the months, you need to use the InScope function to tell the matrix
what to display in which type column, in the details cell.
If your column group is called matrix_Months, you could try
=IIF(InScope("matrix_Months"), Fields!MyField.Value, (your YOY change
calculation))
Not quite sure on how to reference specific columns. What do you need this
for?
Kaisa M. Lindahl Lervik
"stu..." <stu@.discussions.microsoft.com> wrote in message
news:F82841D5-2FA1-4E78-8D1C-31FBEBB5E397@.microsoft.com...
>I have a matrix of data for a period of months. What I am trying to do is
>to
> add a column at the end which will be YOY change. So how in a matrix do I
> reference the value in column 4 (for example) and the last column. Also
> how
> do I add this calculated column to the end of the matrix.
>|||Kaisa,
What I have is
Col1 Col2 Col3 Col4 ..... Col12 YOY
1 2 6 9 16 5
The last column YOY is year on year change so this will be value in col12
minus the value in col4 - for example. So how in a matrix do you get it to
look at the values in two different columns.
Stu...
"Kaisa M. Lindahl Lervik" wrote:
> For the column at the end, right click your column group and click Subtotal.
> If your months show only one field, make sure the expression for this field
> is =sum(Fields!MyField.value), and the total column will sum all the months
> in the last column.
> I'm not sure what you mean by YOY change. If this is something else than
> summing the months, you need to use the InScope function to tell the matrix
> what to display in which type column, in the details cell.
> If your column group is called matrix_Months, you could try
> =IIF(InScope("matrix_Months"), Fields!MyField.Value, (your YOY change
> calculation))
> Not quite sure on how to reference specific columns. What do you need this
> for?
> Kaisa M. Lindahl Lervik
> "stu..." <stu@.discussions.microsoft.com> wrote in message
> news:F82841D5-2FA1-4E78-8D1C-31FBEBB5E397@.microsoft.com...
> >I have a matrix of data for a period of months. What I am trying to do is
> >to
> > add a column at the end which will be YOY change. So how in a matrix do I
> > reference the value in column 4 (for example) and the last column. Also
> > how
> > do I add this calculated column to the end of the matrix.
> >
> >
>
>|||I'm a bit slow today.
Is each column one month in the same year? Can you have months from more
than one year?
How do you group these?
And what does your dataset look like?
Kaisa M. Lindahl Lervik
"stu..." <stu@.discussions.microsoft.com> wrote in message
news:6ACE8DB6-C4D9-470F-9E90-46050B6E1218@.microsoft.com...
> Kaisa,
> What I have is
> Col1 Col2 Col3 Col4 ..... Col12 YOY
> 1 2 6 9 16 5
> The last column YOY is year on year change so this will be value in col12
> minus the value in col4 - for example. So how in a matrix do you get it to
> look at the values in two different columns.
> Stu...
> "Kaisa M. Lindahl Lervik" wrote:
>> For the column at the end, right click your column group and click
>> Subtotal.
>> If your months show only one field, make sure the expression for this
>> field
>> is =sum(Fields!MyField.value), and the total column will sum all the
>> months
>> in the last column.
>> I'm not sure what you mean by YOY change. If this is something else than
>> summing the months, you need to use the InScope function to tell the
>> matrix
>> what to display in which type column, in the details cell.
>> If your column group is called matrix_Months, you could try
>> =IIF(InScope("matrix_Months"), Fields!MyField.Value, (your YOY change
>> calculation))
>> Not quite sure on how to reference specific columns. What do you need
>> this
>> for?
>> Kaisa M. Lindahl Lervik
>> "stu..." <stu@.discussions.microsoft.com> wrote in message
>> news:F82841D5-2FA1-4E78-8D1C-31FBEBB5E397@.microsoft.com...
>> >I have a matrix of data for a period of months. What I am trying to do
>> >is
>> >to
>> > add a column at the end which will be YOY change. So how in a matrix do
>> > I
>> > reference the value in column 4 (for example) and the last column. Also
>> > how
>> > do I add this calculated column to the end of the matrix.
>> >
>> >
>>|||It will be a 12 month rolling window, so can have different years.
Grouping just by the month.
Stu...
"Kaisa M. Lindahl Lervik" wrote:
> I'm a bit slow today.
> Is each column one month in the same year? Can you have months from more
> than one year?
> How do you group these?
> And what does your dataset look like?
> Kaisa M. Lindahl Lervik
> "stu..." <stu@.discussions.microsoft.com> wrote in message
> news:6ACE8DB6-C4D9-470F-9E90-46050B6E1218@.microsoft.com...
> > Kaisa,
> >
> > What I have is
> >
> > Col1 Col2 Col3 Col4 ..... Col12 YOY
> > 1 2 6 9 16 5
> >
> > The last column YOY is year on year change so this will be value in col12
> > minus the value in col4 - for example. So how in a matrix do you get it to
> > look at the values in two different columns.
> >
> > Stu...
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> >> For the column at the end, right click your column group and click
> >> Subtotal.
> >> If your months show only one field, make sure the expression for this
> >> field
> >> is =sum(Fields!MyField.value), and the total column will sum all the
> >> months
> >> in the last column.
> >> I'm not sure what you mean by YOY change. If this is something else than
> >> summing the months, you need to use the InScope function to tell the
> >> matrix
> >> what to display in which type column, in the details cell.
> >> If your column group is called matrix_Months, you could try
> >> =IIF(InScope("matrix_Months"), Fields!MyField.Value, (your YOY change
> >> calculation))
> >>
> >> Not quite sure on how to reference specific columns. What do you need
> >> this
> >> for?
> >>
> >> Kaisa M. Lindahl Lervik
> >>
> >> "stu..." <stu@.discussions.microsoft.com> wrote in message
> >> news:F82841D5-2FA1-4E78-8D1C-31FBEBB5E397@.microsoft.com...
> >> >I have a matrix of data for a period of months. What I am trying to do
> >> >is
> >> >to
> >> > add a column at the end which will be YOY change. So how in a matrix do
> >> > I
> >> > reference the value in column 4 (for example) and the last column. Also
> >> > how
> >> > do I add this calculated column to the end of the matrix.
> >> >
> >> >
> >>
> >>
> >>
>
>

Matrix calculation issues

Hello,
I have a matrix that has the following data
Week1 Week2 Week3 Week4 Week 5 ... Week 52
Total % a/z b/y c/x d/v e/
u aa/zz
Value 1 a b c d
e aa
Value 2 z y x v
u zz
The expression for Total %, sum(value1)/sum(value2)
The expression for Value1 is sum(value1)
The expression for Value2 is sum(value2)
All calcuations are correct.
Now, I added another level called Year and made Week expandable. So
Week can rolled up/collapsed into Year.
For some reason, when I collapse Week into year, the calculation for
Total % is incorrect. It shows me the Average of Total%
In other words, I see this:
+Year
Total % Avg( a/z, b/y,c/x....aa/zz)
Value 1 Sum(a,b,c,d...aa)
Value 2 Sum(z,y,x,v,u..zz)
Can anyone tell me why Total% is showing me an average as opposed to
Sum(a,b,c,d...aa)/Sum(z,y,x,v,u..zz)?
Thanks in Advance.
ClemOn Jan 29, 9:11 pm, clemts <clem...@.yahoo.com> wrote:
> Hello,
> I have a matrix that has the following data
> Week1 Week2 Week3 Week4 Week 5 ... Week 52
> Total % a/z b/y c/x d/v e/
> u aa/zz
> Value 1 a b c d
> e aa
> Value 2 z y x v
> u zz
> The expression for Total %, sum(value1)/sum(value2)
> The expression for Value1 is sum(value1)
> The expression for Value2 is sum(value2)
> All calcuations are correct.
> Now, I added another level called Year and made Week expandable. So
> Week can rolled up/collapsed into Year.
> For some reason, when I collapse Week into year, the calculation for
> Total % is incorrect. It shows me the Average of Total%
> In other words, I see this:
> +Year
> Total % Avg( a/z, b/y,c/x....aa/zz)
> Value 1 Sum(a,b,c,d...aa)
> Value 2 Sum(z,y,x,v,u..zz)
> Can anyone tell me why Total% is showing me an average as opposed to
> Sum(a,b,c,d...aa)/Sum(z,y,x,v,u..zz)?
> Thanks in Advance.
> Clem
What is the relationship between Week and Year in the report?
Enrique Martinez
Sr. Software Consultant

Matrix calculation for percent

How do I write expression to calculate a value to show percentage of the
subtotal value in a Matrix?
I have already tried =Fields!name.value/sum(Fields!name.value) and this
does not work
Example:
Matrix has Coulmn group Matrix_col and a Row group Matrix_Row and I would
like to show the percentage of the subtotal for Matrix_Row in a column next
to Matrix Col.
Matrix_Col Percent
Matrix_Row
value 1 %25
value 2 %50
value 1 %25
Total 4Create a column, and set your expression to Fields!Name.Value /
First(Fields!Amount.Value, "MatrixColumnGroupName") - assuming that your
first row is a total.
If it's not, you might be able to use SUM if you add the Matrix column group
name = Fields!Name.Value / SUM(Fields!Amount.Value, "MatrixColumnGroupName")
Kaisa M. Lindahl
"Curtis" <gilbertson.curtis@.leg.wa.gov> wrote in message
news:uB5u4igFGHA.1028@.TK2MSFTNGP11.phx.gbl...
> How do I write expression to calculate a value to show percentage of the
> subtotal value in a Matrix?
> I have already tried =Fields!name.value/sum(Fields!name.value) and this
> does not work
> Example:
> Matrix has Coulmn group Matrix_col and a Row group Matrix_Row and I would
> like to show the percentage of the subtotal for Matrix_Row in a column
> next
> to Matrix Col.
> Matrix_Col Percent
> Matrix_Row
> value 1 %25
> value 2 %50
> value 1 %25
>
> Total 4
>|||Thank you very much for your answer, I've been looking for a solution for
months.
"Kaisa M. Lindahl" wrote:
> Create a column, and set your expression to Fields!Name.Value /
> First(Fields!Amount.Value, "MatrixColumnGroupName") - assuming that your
> first row is a total.
> If it's not, you might be able to use SUM if you add the Matrix column group
> name = Fields!Name.Value / SUM(Fields!Amount.Value, "MatrixColumnGroupName")
> Kaisa M. Lindahl
> "Curtis" <gilbertson.curtis@.leg.wa.gov> wrote in message
> news:uB5u4igFGHA.1028@.TK2MSFTNGP11.phx.gbl...
> > How do I write expression to calculate a value to show percentage of the
> > subtotal value in a Matrix?
> >
> > I have already tried =Fields!name.value/sum(Fields!name.value) and this
> > does not work
> >
> > Example:
> > Matrix has Coulmn group Matrix_col and a Row group Matrix_Row and I would
> > like to show the percentage of the subtotal for Matrix_Row in a column
> > next
> > to Matrix Col.
> >
> > Matrix_Col Percent
> >
> > Matrix_Row
> >
> > value 1 %25
> >
> > value 2 %50
> >
> > value 1 %25
> >
> >
> >
> > Total 4
> >
> >
>
>

Matrix Calculation - New to RS2000

Hello!
Iâ'm hoping somebody out there can help me! I am a new user of SQL Reporting
Services 2000 and have been asked to create a report showing studentsâ'
attendance. After a lot of reading and research I created a matrix report
that looks like this;
=Fields!attendance_type.value Total
=Fields!student_id.value
=Fields!week_no.value =Fields!register_id.Value =count(Fields!attendance_type.value)
It gives me the following results;
PR AA AB Total
0003 7 ACCA2 4 0 0 4
BUSA2 4 0 0 4
LANA2 2 2 0 4
8 ACCA2 2 2 0 4
BUSA2 2 2 0 4
LANA2 2 0 0 2
9 ACCA2 4 0 0 4
BUSA2 2 0 2 4
LANA2 3 0 0 3
10 ACCA2 4 0 0 4
BUSA2 4 0 0 4
LANA2 3 0 0 3
I would now like to add another column next to Total (subtotal of
=Fields!attendance_type.value) to calculate the % Attendance by using
PR/Total*100.
So in the end, I want something like this;
PR AA AB Total % Attendance
0003 7 ACCA2 4 0 0 4 100
BUSA2 4 0 0 4 100
LANA2 2 2 0 4 50
8 ACCA2 2 2 0 4 50
BUSA2 2 2 0 4 50
LANA2 2 0 0 2 100
Your help would be much appreciatedâ?¦
Wilma KhaliqYou can do a second query and append it to this one with "union", like
select blah blah from blah where blah
union
select blah2 blah2 from blah2 where blah2
except in the bottom select you can do your total, making a new type called
'TOTAL'
--
"Everyone knows something you don't know"
"Whee4ever" wrote:
> Hello!
> Iâ'm hoping somebody out there can help me! I am a new user of SQL Reporting
> Services 2000 and have been asked to create a report showing studentsâ'
> attendance. After a lot of reading and research I created a matrix report
> that looks like this;
> =Fields!attendance_type.value Total
> =Fields!student_id.value
> =Fields!week_no.value =Fields!register_id.Value =count(Fields!attendance_type.value)
> It gives me the following results;
> PR AA AB Total
> 0003 7 ACCA2 4 0 0 4
> BUSA2 4 0 0 4
> LANA2 2 2 0 4
> 8 ACCA2 2 2 0 4
> BUSA2 2 2 0 4
> LANA2 2 0 0 2
> 9 ACCA2 4 0 0 4
> BUSA2 2 0 2 4
> LANA2 3 0 0 3
> 10 ACCA2 4 0 0 4
> BUSA2 4 0 0 4
> LANA2 3 0 0 3
> I would now like to add another column next to Total (subtotal of
> =Fields!attendance_type.value) to calculate the % Attendance by using
> PR/Total*100.
> So in the end, I want something like this;
> PR AA AB Total % Attendance
> 0003 7 ACCA2 4 0 0 4 100
> BUSA2 4 0 0 4 100
> LANA2 2 2 0 4 50
> 8 ACCA2 2 2 0 4 50
> BUSA2 2 2 0 4 50
> LANA2 2 0 0 2 100
> Your help would be much appreciatedâ?¦
> Wilma Khaliq

Matrix Calculation - % Growth

I have a report that list customer's 2004 and '05 Sales by month:
Jan Feb Mar ...
Acct 10 2004 $$$ $$$ $$$ ...
2005 $$
% Growth
Acct 11 2004 $$$ $$$ $$$ ...
2005 $$
% Growth
The report currently list all of our accounts in a Matrix report. How can I
calculate the "% Growth"?
The Subtotal function obviouly isn't what I need. Any help is much
appreciated!
DavidDJONES wrote:
> I have a report that list customer's 2004 and '05 Sales by month:
> Jan Feb Mar ...
> Acct 10 2004 $$$ $$$ $$$ ...
> 2005 $$
> % Growth
> Acct 11 2004 $$$ $$$ $$$ ...
> 2005 $$
> % Growth
> The report currently list all of our accounts in a Matrix report. How
> can I calculate the "% Growth"?
> The Subtotal function obviouly isn't what I need. Any help is much
> appreciated!
hmm *scratching head**untestet*
=(100-(Sum(Fields!Jan 2004.Value)/(Sum(Fields!Jan 2005.Value)/100)))
should be the growth in percent
i.e. 100-(1555)/(1888/100) ~ 17,64%
regards
frank|||I was planning to place this formula in a group footer, but just realized
that I can't add a group footer in a Matrix style report. Is that correct?
Could I use a similar formula in a tabular report?
Thanks
"Frank Matthiesen" wrote:
> DJONES wrote:
> > I have a report that list customer's 2004 and '05 Sales by month:
> >
> > Jan Feb Mar ...
> > Acct 10 2004 $$$ $$$ $$$ ...
> > 2005 $$
> > % Growth
> >
> > Acct 11 2004 $$$ $$$ $$$ ...
> > 2005 $$
> > % Growth
> >
> > The report currently list all of our accounts in a Matrix report. How
> > can I calculate the "% Growth"?
> >
> > The Subtotal function obviouly isn't what I need. Any help is much
> > appreciated!
>
> hmm *scratching head**untestet*
> =(100-(Sum(Fields!Jan 2004.Value)/(Sum(Fields!Jan 2005.Value)/100)))
> should be the growth in percent
> i.e. 100-(1555)/(1888/100) ~ 17,64%
>
> regards
> frank
>
>

Matrix calculation

Hi,
I have a matrix which looks as follows:
Actuals Budget
Item 1 20 50
Item 2 45 30
Item 3 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, because if I just use the subtotal
function it adds both totals together and I need to have the difference
between them?
Please help...Right click on the cells that contain the data ("Details" as they are
called). Press on the "Add Column" button. In the new column, click
on the details part where the data goes and then put and expression in
there that will take the two data points you want and subtract them in
the way that you want.|||Thanks for the update, I have both actuals and budget in the
"matrix1_ColumnGroup4", how can I then subtract both fields from eachother?
How can I indicate to which field the database needs to look at?
Please help as this is important for me...
"SQL Guy" wrote:
> Right click on the cells that contain the data ("Details" as they are
> called). Press on the "Add Column" button. In the new column, click
> on the details part where the data goes and then put and expression in
> there that will take the two data points you want and subtract them in
> the way that you want.
>|||Ok so you have a new column, in the cell put an expression that will
subtract the two like this:
=Fields!Budget.Value-Fields!Actuals.Value
That should automatically take the budget and actual from the current
row and subtract them from each other. You would also put this in your
select statement. For example
Select actual,budget,(actual-budget) as difference
from YOUR_TABLE_NAME
where ......
Then you would have an already calculated field to use in your
matrix. Really they both go the same way. My preference is to do it
on the SQL side rather than put a formula in the matrix. It's up to
you. Hope it helps!|||I had a question, why would you even need a matrix for this? I think
a table would suffice.|||I had a question, why would you even need a matrix for this? I think
a table would suffice.|||On Nov 5, 5:41 am, DHL <D...@.discussions.microsoft.com> wrote:
> Hi,
> I have a matrix which looks as follows:
> Actuals Budget
> Item 1 20 50
> Item 2 45 30
> Item 3 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, because if I just use the subtotal
> function it adds both totals together and I need to have the difference
> between them?
> Please help...
If this is a Matrix, this implies your data is set up like:
Items, Type, Amount
Item 1, Actuals, 20
Item 1, Budget, 50
Item 2, Actuals, 45
...
which means you can't actually do a single expression of Fields!
Actual.Value - Fields!Budget.Value.
Two ways I would do this:
1. I would add a new field to the dataset, Amount2, with an
expression of
=IIF( Fields!Type = "Budget", Fields!Amount, IIF( Fields!Type ="Actuals", -1 * Fields!Amount, Nothing ) )
This flips the sign of your budget, so that the Sum of types Actuals
and Budget gets you the difference you asked for. Then, in the
Matrix, add a subtotal on the Columns (group matrix1_Type) and use the
following expression in the Details:
=IIF( InScope("matrix1_Type"), Sum(Fields!Amount.Value), Sum(Fields!
Amount2.Value) )
What happens here is that when the Matrix is calculating the Sum() in
each column, it's doing so under the Scope of that column
("matrix1_Type"). The Total Sum at the end of the Column is done at
the Matrix Scope, so InScope() returns false, and returns the Sum of
the custom field instead.
2. Or, change the Data tab's SQL SELECT to include a union with the
calculated amount
SELECT T.ITEMS, T.TYPE, T.AMOUNT FROM TABLE_NAME T
UNION
SELECT T.ITEMS, 'Total' AS TYPE,
SUM( CASE T.TYPE
WHEN 'Actuals' THEN T.AMOUNT
WHEN 'Budget' THEN -T.AMOUNT ELSE NULL END ) AS AMOUNT
FROM TABLE_NAME T
WHERE T.TYPE IN ( 'Actuals', 'Budget' )
GROUP BY T.ITEMS
then just refresh your existing Matrix and a Totals column will appear
with the total you requested.
-- Scott