Divide by a first row of resultset in a matrix

I'm creating my first matrix based report, and find that some things that
work with tables doesn't work the same way with matrix.
My resultset comes from an MDX statement. It looks something like this
..............................Registered hours ..........Amount
.............Total.....................10...................
1000
Month1....Group 1..................6.....................600
.............Group
2..................4.....................400
.............Total.....................12...................
1200
Month2....Group 1..................8.....................800
.............Group
2..................4.....................400
The customer wants to display the values for the months next to each other,
for each value. Now, I've managed to get this with a matrix, having the
[static group] ordered on top. (Thanks to Ohjoo Kwon).
BUT one of my columns is supposed to be "Group Result as % of total result."
Meaning for Group1 in Month1 I need to divide 600 on the Total Amount. The
first row in my result set is always a calculated total for that month. So,
when using tables, I usually just do =Amount/First(Amount), and that works
like a charm. When I try to do that in a matrix, all I get in return is
"100%". So my guess is that in a matrix, I can't use First() the same way as
in a table. But how can I do the division? How to divide "Any Row" by "First
Row"?
All help appreciated.
Kaisa M. LindahlYou have to provide the "right" scope for the aggregate function. Try one of
the two following expressions to divide by the first row:
=Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "DataSetName")
=Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "MatrixName")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:enVnqz6BFHA.2260@.TK2MSFTNGP14.phx.gbl...
> I'm creating my first matrix based report, and find that some things that
> work with tables doesn't work the same way with matrix.
> My resultset comes from an MDX statement. It looks something like this
> ..............................Registered hours
..........Amount
>
.............Total.....................10...................
> 1000
> Month1....Group 1..................6.....................600
> .............Group
> 2..................4.....................400
>
.............Total.....................12...................
> 1200
> Month2....Group 1..................8.....................800
> .............Group
> 2..................4.....................400
> The customer wants to display the values for the months next to each
other,
> for each value. Now, I've managed to get this with a matrix, having the
> [static group] ordered on top. (Thanks to Ohjoo Kwon).
> BUT one of my columns is supposed to be "Group Result as % of total
result."
> Meaning for Group1 in Month1 I need to divide 600 on the Total Amount. The
> first row in my result set is always a calculated total for that month.
So,
> when using tables, I usually just do =Amount/First(Amount), and that works
> like a charm. When I try to do that in a matrix, all I get in return is
> "100%". So my guess is that in a matrix, I can't use First() the same way
as
> in a table. But how can I do the division? How to divide "Any Row" by
"First
> Row"?
> All help appreciated.
> Kaisa M. Lindahl
>|||Thanks.
It worked with
=Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "DataSetName")
Kaisa
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:etV2GOOCFHA.1392@.tk2msftngp13.phx.gbl...
> You have to provide the "right" scope for the aggregate function. Try one
of
> the two following expressions to divide by the first row:
> =Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "DataSetName")
> =Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "MatrixName")
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> news:enVnqz6BFHA.2260@.TK2MSFTNGP14.phx.gbl...
> > I'm creating my first matrix based report, and find that some things
that
> > work with tables doesn't work the same way with matrix.
> > My resultset comes from an MDX statement. It looks something like this
> >
> > ..............................Registered hours
> ..........Amount
> >
>
.............Total.....................10...................
> > 1000
> > Month1....Group
1..................6.....................600
> > .............Group
> > 2..................4.....................400
> >
> >
>
.............Total.....................12...................
> > 1200
> > Month2....Group
1..................8.....................800
> > .............Group
> > 2..................4.....................400
> >
> > The customer wants to display the values for the months next to each
> other,
> > for each value. Now, I've managed to get this with a matrix, having the
> > [static group] ordered on top. (Thanks to Ohjoo Kwon).
> >
> > BUT one of my columns is supposed to be "Group Result as % of total
> result."
> > Meaning for Group1 in Month1 I need to divide 600 on the Total Amount.
The
> > first row in my result set is always a calculated total for that month.
> So,
> > when using tables, I usually just do =Amount/First(Amount), and that
works
> > like a charm. When I try to do that in a matrix, all I get in return is
> > "100%". So my guess is that in a matrix, I can't use First() the same
way
> as
> > in a table. But how can I do the division? How to divide "Any Row" by
> "First
> > Row"?
> >
> > All help appreciated.
> >
> > Kaisa M. Lindahl
> >
> >
>|||I just realized an error in my original code. What Robert suggested (below)
works for dividing by the very first row of the result set.
Unfortunately, I need to divide the right number with the right first row...
I'll explain
My result looks like this:
.......................Measures_Amount ...
............AllGrps...100
............Grp1........30
200409...Grp2........30
............Grp3.......40
............AllGrps....120
............Grp1........40
200309...Grp2........40
............Grp3........40
My report looks someting like this
.............Measures_Amount.....%ofTotalAmt
Group........200309|200409...200309|200409
Total..............120|...100........100%|...100%
Grp1...............40|....30.........33%|....30%
Grp2...............40|....30.........33%|....30%
Grp3...............40|....40.........33%|....40%
Today I realized that my statement
=First(Fields!Measures_Amount.Value)/ First(Fields!Measures_Amount.Value,
"OmsetningHT")
doesn't work quite as planned. This will divide all rows with the very first
row, which is the total for 200409. Suddenly all results from 200309 are
divided by the total for 200409, which is wrong.
So I need a way to divide a group result by the correct total in my matrix.
The months are organized in a column group.
All help appreciated!
Kaisa M. Lindahl
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:etV2GOOCFHA.1392@.tk2msftngp13.phx.gbl...
> You have to provide the "right" scope for the aggregate function. Try one
of
> the two following expressions to divide by the first row:
> =Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "DataSetName")
> =Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "MatrixName")
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> news:enVnqz6BFHA.2260@.TK2MSFTNGP14.phx.gbl...
> > I'm creating my first matrix based report, and find that some things
that
> > work with tables doesn't work the same way with matrix.
> > My resultset comes from an MDX statement. It looks something like this
> >
> > ..............................Registered hours
> ..........Amount
> >
>
.............Total.....................10...................
> > 1000
> > Month1....Group
1..................6.....................600
> > .............Group
> > 2..................4.....................400
> >
> >
>
.............Total.....................12...................
> > 1200
> > Month2....Group
1..................8.....................800
> > .............Group
> > 2..................4.....................400
> >
> > The customer wants to display the values for the months next to each
> other,
> > for each value. Now, I've managed to get this with a matrix, having the
> > [static group] ordered on top. (Thanks to Ohjoo Kwon).
> >
> > BUT one of my columns is supposed to be "Group Result as % of total
> result."
> > Meaning for Group1 in Month1 I need to divide 600 on the Total Amount.
The
> > first row in my result set is always a calculated total for that month.
> So,
> > when using tables, I usually just do =Amount/First(Amount), and that
works
> > like a charm. When I try to do that in a matrix, all I get in return is
> > "100%". So my guess is that in a matrix, I can't use First() the same
way
> as
> > in a table. But how can I do the division? How to divide "Any Row" by
> "First
> > Row"?
> >
> > All help appreciated.
> >
> > Kaisa M. Lindahl
> >
> >
>|||This problem just resurfaced.
Anyone want to give it some thought?
Is it possible to check for something like
if month = Parameters!FirstPeriod.Value then
'use this row
end if ?
Kaisa
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:OACV70qDFHA.1296@.TK2MSFTNGP10.phx.gbl...
> I just realized an error in my original code. What Robert suggested
(below)
> works for dividing by the very first row of the result set.
> Unfortunately, I need to divide the right number with the right first
row...
> I'll explain
> My result looks like this:
> .......................Measures_Amount ...
> ............AllGrps...100
> ............Grp1........30
> 200409...Grp2........30
> ............Grp3.......40
> ............AllGrps....120
> ............Grp1........40
> 200309...Grp2........40
> ............Grp3........40
> My report looks someting like this
> .............Measures_Amount.....%ofTotalAmt
> Group........200309|200409...200309|200409
> Total..............120|...100........100%|...100%
> Grp1...............40|....30.........33%|....30%
> Grp2...............40|....30.........33%|....30%
> Grp3...............40|....40.........33%|....40%
> Today I realized that my statement
> =First(Fields!Measures_Amount.Value)/ First(Fields!Measures_Amount.Value,
> "OmsetningHT")
> doesn't work quite as planned. This will divide all rows with the very
first
> row, which is the total for 200409. Suddenly all results from 200309 are
> divided by the total for 200409, which is wrong.
> So I need a way to divide a group result by the correct total in my
matrix.
> The months are organized in a column group.
> All help appreciated!
> Kaisa M. Lindahl
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:etV2GOOCFHA.1392@.tk2msftngp13.phx.gbl...
> > You have to provide the "right" scope for the aggregate function. Try
one
> of
> > the two following expressions to divide by the first row:
> > =Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "DataSetName")
> > =Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "MatrixName")
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> > news:enVnqz6BFHA.2260@.TK2MSFTNGP14.phx.gbl...
> > > I'm creating my first matrix based report, and find that some things
> that
> > > work with tables doesn't work the same way with matrix.
> > > My resultset comes from an MDX statement. It looks something like this
> > >
> > > ..............................Registered hours
> > ..........Amount
> > >
> >
>
.............Total.....................10...................
> > > 1000
> > > Month1....Group
> 1..................6.....................600
> > > .............Group
> > > 2..................4.....................400
> > >
> > >
> >
>
.............Total.....................12...................
> > > 1200
> > > Month2....Group
> 1..................8.....................800
> > > .............Group
> > > 2..................4.....................400
> > >
> > > The customer wants to display the values for the months next to each
> > other,
> > > for each value. Now, I've managed to get this with a matrix, having
the
> > > [static group] ordered on top. (Thanks to Ohjoo Kwon).
> > >
> > > BUT one of my columns is supposed to be "Group Result as % of total
> > result."
> > > Meaning for Group1 in Month1 I need to divide 600 on the Total Amount.
> The
> > > first row in my result set is always a calculated total for that
month.
> > So,
> > > when using tables, I usually just do =Amount/First(Amount), and that
> works
> > > like a charm. When I try to do that in a matrix, all I get in return
is
> > > "100%". So my guess is that in a matrix, I can't use First() the same
> way
> > as
> > > in a table. But how can I do the division? How to divide "Any Row" by
> > "First
> > > Row"?
> > >
> > > All help appreciated.
> > >
> > > Kaisa M. Lindahl
> > >
> > >
> >
> >
>|||If you can group on year or whatever is relevant in your scenario, then
within that group, =First(Fields!Amount.Value) should refer to the
first value in that group. As Robert said, it's all about scope with
aggregate functions. By grouping the data, you are limiting the scope,
which is what you want.
Chris
Kaisa M. Lindahl wrote:
> This problem just resurfaced.
> Anyone want to give it some thought?
> Is it possible to check for something like
> if month = Parameters!FirstPeriod.Value then
> 'use this row
> end if ?
> Kaisa
> "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> news:OACV70qDFHA.1296@.TK2MSFTNGP10.phx.gbl...
> > I just realized an error in my original code. What Robert suggested
> (below)
> > works for dividing by the very first row of the result set.
> > Unfortunately, I need to divide the right number with the right
> > first
> row...
> > I'll explain
> >
> > My result looks like this:
> > .......................Measures_Amount ...
> > ............AllGrps...100
> > ............Grp1........30
> > 200409...Grp2........30
> > ............Grp3.......40
> > ............AllGrps....120
> > ............Grp1........40
> > 200309...Grp2........40
> > ............Grp3........40
> >
> > My report looks someting like this
> >
> > .............Measures_Amount.....%ofTotalAmt
> > Group........200309|200409...200309|200409
> > Total..............120|...100........100%|...100%
> > Grp1...............40|....30.........33%|....30%
> > Grp2...............40|....30.........33%|....30%
> > Grp3...............40|....40.........33%|....40%
> >
> > Today I realized that my statement
> > =First(Fields!Measures_Amount.Value)/
> > First(Fields!Measures_Amount.Value, "OmsetningHT")
> > doesn't work quite as planned. This will divide all rows with the
> > very
> first
> > row, which is the total for 200409. Suddenly all results from
> > 200309 are divided by the total for 200409, which is wrong.
> >
> > So I need a way to divide a group result by the correct total in my
> matrix.
> > The months are organized in a column group.
> >
> > All help appreciated!
> >
> > Kaisa M. Lindahl
> >
> >
> > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
> > message news:etV2GOOCFHA.1392@.tk2msftngp13.phx.gbl...
> > > You have to provide the "right" scope for the aggregate function.
> > > Try
> one
> > of
> > > the two following expressions to divide by the first row:
> > > =Sum(Fields!Amount.Value) / First(Fields!Amount.Value,
> > > "DataSetName") =Sum(Fields!Amount.Value) /
> > > First(Fields!Amount.Value, "MatrixName")
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers
> > > no
> > rights.
> > >
> > >
> > > "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> > > news:enVnqz6BFHA.2260@.TK2MSFTNGP14.phx.gbl...
> > > > I'm creating my first matrix based report, and find that some
> > > > things
> > that
> > > > work with tables doesn't work the same way with matrix.
> > > > My resultset comes from an MDX statement. It looks something
> > > > like this
> > > >
> > > > ..............................Registered hours
> > > ..........Amount
> > > >
> > >
> >
> .............Total.....................10...............
> .....
> > > > 1000
> > > > Month1....Group
> > 1..................6.....................600
> > > > .............Group
> > > > 2..................4.....................400
> > > >
> > > >
> > >
> >
> .............Total.....................12...............
> .....
> > > > 1200
> > > > Month2....Group
> > 1..................8.....................800
> > > > .............Group
> > > > 2..................4.....................400
> > > >
> > > > The customer wants to display the values for the months next to
> > > > each
> > > other,
> > > > for each value. Now, I've managed to get this with a matrix,
> > > > having
> the
> > > > [static group] ordered on top. (Thanks to Ohjoo Kwon).
> > > >
> > > > BUT one of my columns is supposed to be "Group Result as % of
> > > > total
> > > result."
> > > > Meaning for Group1 in Month1 I need to divide 600 on the Total
> > > > Amount.
> > The
> > > > first row in my result set is always a calculated total for that
> month.
> > > So,
> > > > when using tables, I usually just do =Amount/First(Amount), and
> > > > that
> > works
> > > > like a charm. When I try to do that in a matrix, all I get in
> > > > return
> is
> > > > "100%". So my guess is that in a matrix, I can't use First()
> > > > the same
> > way
> > > as
> > > > in a table. But how can I do the division? How to divide "Any
> > > > Row" by
> > > "First
> > > > Row"?
> > > >
> > > > All help appreciated.
> > > >
> > > > Kaisa M. Lindahl
> > > >
> > > >
> > >
> > >
> >
> >|||OK, thanks to both. I finally figured it out. :)
I'm still getting confused by when to use what scope. The final solution was
to change from
First(Fields!Amount.Value, "Dataset") to First(Fields!Amount.Value,
"MatrixColumnGroupName").
Kaisa M. Lindahl
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:uO5VPAzIFHA.2656@.TK2MSFTNGP09.phx.gbl...
> If you can group on year or whatever is relevant in your scenario, then
> within that group, =First(Fields!Amount.Value) should refer to the
> first value in that group. As Robert said, it's all about scope with
> aggregate functions. By grouping the data, you are limiting the scope,
> which is what you want.
> Chris
>
> Kaisa M. Lindahl wrote:
> > This problem just resurfaced.
> > Anyone want to give it some thought?
> >
> > Is it possible to check for something like
> > if month = Parameters!FirstPeriod.Value then
> > 'use this row
> > end if ?
> >
> > Kaisa
> >
> > "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> > news:OACV70qDFHA.1296@.TK2MSFTNGP10.phx.gbl...
> > > I just realized an error in my original code. What Robert suggested
> > (below)
> > > works for dividing by the very first row of the result set.
> > > Unfortunately, I need to divide the right number with the right
> > > first
> > row...
> > > I'll explain
> > >
> > > My result looks like this:
> > > .......................Measures_Amount ...
> > > ............AllGrps...100
> > > ............Grp1........30
> > > 200409...Grp2........30
> > > ............Grp3.......40
> > > ............AllGrps....120
> > > ............Grp1........40
> > > 200309...Grp2........40
> > > ............Grp3........40
> > >
> > > My report looks someting like this
> > >
> > > .............Measures_Amount.....%ofTotalAmt
> > > Group........200309|200409...200309|200409
> > > Total..............120|...100........100%|...100%
> > > Grp1...............40|....30.........33%|....30%
> > > Grp2...............40|....30.........33%|....30%
> > > Grp3...............40|....40.........33%|....40%
> > >
> > > Today I realized that my statement
> > > =First(Fields!Measures_Amount.Value)/
> > > First(Fields!Measures_Amount.Value, "OmsetningHT")
> > > doesn't work quite as planned. This will divide all rows with the
> > > very
> > first
> > > row, which is the total for 200409. Suddenly all results from
> > > 200309 are divided by the total for 200409, which is wrong.
> > >
> > > So I need a way to divide a group result by the correct total in my
> > matrix.
> > > The months are organized in a column group.
> > >
> > > All help appreciated!
> > >
> > > Kaisa M. Lindahl
> > >
> > >
> > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
> > > message news:etV2GOOCFHA.1392@.tk2msftngp13.phx.gbl...
> > > > You have to provide the "right" scope for the aggregate function.
> > > > Try
> > one
> > > of
> > > > the two following expressions to divide by the first row:
> > > > =Sum(Fields!Amount.Value) / First(Fields!Amount.Value,
> > > > "DataSetName") =Sum(Fields!Amount.Value) /
> > > > First(Fields!Amount.Value, "MatrixName")
> > > >
> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers
> > > > no
> > > rights.
> > > >
> > > >
> > > > "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> > > > news:enVnqz6BFHA.2260@.TK2MSFTNGP14.phx.gbl...
> > > > > I'm creating my first matrix based report, and find that some
> > > > > things
> > > that
> > > > > work with tables doesn't work the same way with matrix.
> > > > > My resultset comes from an MDX statement. It looks something
> > > > > like this
> > > > >
> > > > > ..............................Registered hours
> > > > ..........Amount
> > > > >
> > > >
> > >
> > .............Total.....................10...............
> > .....
> > > > > 1000
> > > > > Month1....Group
> > > 1..................6.....................600
> > > > > .............Group
> > > > > 2..................4.....................400
> > > > >
> > > > >
> > > >
> > >
> > .............Total.....................12...............
> > .....
> > > > > 1200
> > > > > Month2....Group
> > > 1..................8.....................800
> > > > > .............Group
> > > > > 2..................4.....................400
> > > > >
> > > > > The customer wants to display the values for the months next to
> > > > > each
> > > > other,
> > > > > for each value. Now, I've managed to get this with a matrix,
> > > > > having
> > the
> > > > > [static group] ordered on top. (Thanks to Ohjoo Kwon).
> > > > >
> > > > > BUT one of my columns is supposed to be "Group Result as % of
> > > > > total
> > > > result."
> > > > > Meaning for Group1 in Month1 I need to divide 600 on the Total
> > > > > Amount.
> > > The
> > > > > first row in my result set is always a calculated total for that
> > month.
> > > > So,
> > > > > when using tables, I usually just do =Amount/First(Amount), and
> > > > > that
> > > works
> > > > > like a charm. When I try to do that in a matrix, all I get in
> > > > > return
> > is
> > > > > "100%". So my guess is that in a matrix, I can't use First()
> > > > > the same
> > > way
> > > > as
> > > > > in a table. But how can I do the division? How to divide "Any
> > > > > Row" by
> > > > "First
> > > > > Row"?
> > > > >
> > > > > All help appreciated.
> > > > >
> > > > > Kaisa M. Lindahl
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
>|||Kaisa, in general, leaving the scope out lets RS decide for you.
Generally this will work the way you want.
Chris
Kaisa M. Lindahl wrote:
> OK, thanks to both. I finally figured it out. :)
> I'm still getting confused by when to use what scope. The final
> solution was to change from
> First(Fields!Amount.Value, "Dataset") to First(Fields!Amount.Value,
> "MatrixColumnGroupName").
> Kaisa M. Lindahl
>
> "Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
> news:uO5VPAzIFHA.2656@.TK2MSFTNGP09.phx.gbl...
> > If you can group on year or whatever is relevant in your scenario,
> > then within that group, =First(Fields!Amount.Value) should refer to
> > the first value in that group. As Robert said, it's all about scope
> > with aggregate functions. By grouping the data, you are limiting
> > the scope, which is what you want.
> >
> > Chris
> >
> >
> >
> > Kaisa M. Lindahl wrote:
> >
> > > This problem just resurfaced.
> > > Anyone want to give it some thought?
> > >
> > > Is it possible to check for something like
> > > if month = Parameters!FirstPeriod.Value then
> > > 'use this row
> > > end if ?
> > >
> > > Kaisa
> > >
> > > "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> > > news:OACV70qDFHA.1296@.TK2MSFTNGP10.phx.gbl...
> > > > I just realized an error in my original code. What Robert
> > > > suggested
> > > (below)
> > > > works for dividing by the very first row of the result set.
> > > > Unfortunately, I need to divide the right number with the right
> > > > first
> > > row...
> > > > I'll explain
> > > >
> > > > My result looks like this:
> > > > .......................Measures_Amount ...
> > > > ............AllGrps...100
> > > > ............Grp1........30
> > > > 200409...Grp2........30
> > > > ............Grp3.......40
> > > > ............AllGrps....120
> > > > ............Grp1........40
> > > > 200309...Grp2........40
> > > > ............Grp3........40
> > > >
> > > > My report looks someting like this
> > > >
> > > > .............Measures_Amount.....%ofTotalAmt
> > > > Group........200309|200409...200309|200409
> > > > Total..............120|...100........100%|...100%
> > > > Grp1...............40|....30.........33%|....30%
> > > > Grp2...............40|....30.........33%|....30%
> > > > Grp3...............40|....40.........33%|....40%
> > > >
> > > > Today I realized that my statement
> > > > =First(Fields!Measures_Amount.Value)/
> > > > First(Fields!Measures_Amount.Value, "OmsetningHT")
> > > > doesn't work quite as planned. This will divide all rows with
> > > > the very
> > > first
> > > > row, which is the total for 200409. Suddenly all results from
> > > > 200309 are divided by the total for 200409, which is wrong.
> > > >
> > > > So I need a way to divide a group result by the correct total
> > > > in my
> > > matrix.
> > > > The months are organized in a column group.
> > > >
> > > > All help appreciated!
> > > >
> > > > Kaisa M. Lindahl
> > > >
> > > >
> > > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
> > > > message news:etV2GOOCFHA.1392@.tk2msftngp13.phx.gbl...
> > > > > You have to provide the "right" scope for the aggregate
> > > > > function. Try
> > > one
> > > > of
> > > > > the two following expressions to divide by the first row:
> > > > > =Sum(Fields!Amount.Value) / First(Fields!Amount.Value,
> > > > > "DataSetName") =Sum(Fields!Amount.Value) /
> > > > > First(Fields!Amount.Value, "MatrixName")
> > > > >
> > > > > --
> > > > > This posting is provided "AS IS" with no warranties, and
> > > > > confers no
> > > > rights.
> > > > >
> > > > >
> > > > > "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> > > > > news:enVnqz6BFHA.2260@.TK2MSFTNGP14.phx.gbl...
> > > > > > I'm creating my first matrix based report, and find that
> > > > > > some things
> > > > that
> > > > > > work with tables doesn't work the same way with matrix.
> > > > > > My resultset comes from an MDX statement. It looks something
> > > > > > like this
> > > > > >
> > > > > > ..............................Registered hours
> > > > > ..........Amount
> > > > > >
> > > > >
> > > >
> > > .............Total.....................10............
> > > ... .....
> > > > > > 1000
> > > > > > Month1....Group
> > > > 1..................6.....................600
> > > > > > .............Group
> > > > > > 2..................4.....................400
> > > > > >
> > > > > >
> > > > >
> > > >
> > > .............Total.....................12............
> > > ... .....
> > > > > > 1200
> > > > > > Month2....Group
> > > > 1..................8.....................800
> > > > > > .............Group
> > > > > > 2..................4.....................400
> > > > > >
> > > > > > The customer wants to display the values for the months
> > > > > > next to each
> > > > > other,
> > > > > > for each value. Now, I've managed to get this with a matrix,
> > > > > > having
> > > the
> > > > > > [static group] ordered on top. (Thanks to Ohjoo Kwon).
> > > > > >
> > > > > > BUT one of my columns is supposed to be "Group Result as %
> > > > > > of total
> > > > > result."
> > > > > > Meaning for Group1 in Month1 I need to divide 600 on the
> > > > > > Total Amount.
> > > > The
> > > > > > first row in my result set is always a calculated total for
> > > > > > that
> > > month.
> > > > > So,
> > > > > > when using tables, I usually just do =Amount/First(Amount),
> > > > > > and that
> > > > works
> > > > > > like a charm. When I try to do that in a matrix, all I get
> > > > > > in return
> > > is
> > > > > > "100%". So my guess is that in a matrix, I can't use First()
> > > > > > the same
> > > > way
> > > > > as
> > > > > > in a table. But how can I do the division? How to divide
> > > > > > "Any Row" by
> > > > > "First
> > > > > > Row"?
> > > > > >
> > > > > > All help appreciated.
> > > > > >
> > > > > > Kaisa M. Lindahl
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> >