I have created a matrix and I am trying to add a percentage subtotal on the
following. I have this Fiscal Year (FY) and Last Fiscal Year (LFY) data. Then
I added in a subtotal by right clicking on the column group for Male/Female.
Know I need to add and additional field that calculates the Total % .
M F Total Total %
Age FY LFY FY LFY FY LFY FY LFY
0 10 5 1 5 11 10 .33 .46
1-4 4 2 12 3 16 5 .47 .23
5-9 3 1 4 6 7 7 .21 .32
Total 17 8 17 14 34 22This is a multi-part message in MIME format.
--=_NextPart_000_003D_01C61F7D.57B99130
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
You'll probably need to check for scope in your cell expression, to make =the right calculation based on "where" you are in your matrix.
Use the following guideline:
=3DIif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", ="In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of =ColumnGroup1", "In Subtotal of entire matrix"))
Then, to calculate the percent, in the right scope, use this:
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 =3D Fields!Name.Value / SUM(Fields!Amount.Value, ="MatrixColumnGroupName")
Kaisa M. Lindahl
"Asim" <Asim@.discussions.microsoft.com> wrote in message =news:2BAED4A1-4E68-4B13-A0E0-A76A8B8D9BC3@.microsoft.com...
>I have created a matrix and I am trying to add a percentage subtotal on =the > following. I have this Fiscal Year (FY) and Last Fiscal Year (LFY) =data. Then > I added in a subtotal by right clicking on the column group for =Male/Female. > Know I need to add and additional field that calculates the Total % .
> > M F Total =Total %
> Age FY LFY FY LFY FY LFY FY LFY
> 0 10 5 1 5 11 10 .33 = .46
> 1-4 4 2 12 3 16 5 .47 = .23
> 5-9 3 1 4 6 7 7 .21 = .32
> Total 17 8 17 14 34 22
--=_NextPart_000_003D_01C61F7D.57B99130
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You'll probably need to check for scope in your cell =expression, to make the right calculation based on "where" you are in =your matrix.
Use the following guideline:
=3DIif(InScope("ColumnGroup1"), =iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In =Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))
Then, to calculate the percent, in the right scope, =use this:
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 =3D Fields!Name.Value / =SUM(Fields!Amount.Value, "MatrixColumnGroupName")
Kaisa M. Lindahl
"Asim" =wrote in message news:2BAED4A1-4E68-4B13-A0E0-A76A8B8D9BC3@.microsoft.com...>I have created a matrix and =I am trying to add a percentage subtotal on the > following. I have this =Fiscal Year (FY) and Last Fiscal Year (LFY) data. Then > I added in a =subtotal by right clicking on the column group for Male/Female. > Know =I need to add and additional field that calculates the Total % .> > =; M F = Total Total %> Age &nbs=p; FY LFY FY LFY FY LFY FY =LFY> 0 = 10 5 =1 5 11 10 .33 =.46> 1-4 &nbs=p; 4 2 12 3 =16 5 =.47 .23> 5-9 &nbs=p; 3 1 =4 6 7 =7 .21 .32> Total &n=bsp; 17 8 =17 14 34 22
--=_NextPart_000_003D_01C61F7D.57B99130--|||Kaisa,
You seem to understand this InScope function so well and yet I take your
suggestion as you say "you have to check for scope in your cell expression".
The cell expression only *allows* one expression. In my detail cell I have
something like Sum(Fields!CriticalCount.value) and I can see how to put that
into the InScope but I want to do something else if I am in the subtotal and
I have questions about that. You are getting close to providing the answer
here when you say "then to calculate the percent in the right scope..." but
*where* would you put this calculation in the *one* cell expression that is
there? I have messed with this for a long time and almost got it working
with ONE column and ONE row ... but still couldnt figure out how to create a
subtotal expression different ( I tried using some thing like
SUM(ReportItems!tbCritCount.value) for the subtotal expression and of course
got errors on the aggregate and using ReportItems! ) So how can I reference
the values I want? lets say I want to check the max value in a column at the
subtotal level? OK ... if I cant do that ... then lets say I want to SUM the
values of an expression that I have in the detail cell of the column. I am
just not getting this and I am really really trying to ... :-(
"Kaisa M. Lindahl" wrote:
> You'll probably need to check for scope in your cell expression, to make the right calculation based on "where" you are in your matrix.
> Use the following guideline:
> =Iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))
> Then, to calculate the percent, in the right scope, use this:
> 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
> "Asim" <Asim@.discussions.microsoft.com> wrote in message news:2BAED4A1-4E68-4B13-A0E0-A76A8B8D9BC3@.microsoft.com...
> >I have created a matrix and I am trying to add a percentage subtotal on the
> > following. I have this Fiscal Year (FY) and Last Fiscal Year (LFY) data. Then
> > I added in a subtotal by right clicking on the column group for Male/Female.
> > Know I need to add and additional field that calculates the Total % .
> >
> > M F Total Total %
> > Age FY LFY FY LFY FY LFY FY LFY
> > 0 10 5 1 5 11 10 .33 .46
> > 1-4 4 2 12 3 16 5 .47 .23
> > 5-9 3 1 4 6 7 7 .21 .32
> > Total 17 8 17 14 34 22sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment