Matrix Expression Problem

Have a matrix with three columns depicting the following table:
# of Results # of Results
% of Results
within 5 working days > 5 working days >5
working days
January 33 1
February 24 16
etc
The two # of Results colums have a count(Fields!January.Value) in the data
box.
I now need to get the % column done. I am having difficulty in doing this.
Any help would be greatly appreciated.You could add a subtotal column. In the matrix cell, you then need to take
into account the subtotal scope and use a different expression to calculate
the percentage. Something similar to this:
=iif(InScope("MatrixColumnGroupingName"), Count(Fields!Cases.Value),
Sum(iif( /* condition for the first column group instance */, 1, 0)) /
Sum(iif( /* condition for the second column group instance */, 1, 0)))
The comments in the expression above need to be replaced with the matrix
column grouping expression (basically identify the data rows that contribute
to the first grouping column vs. the second grouping column). For more
information about the usage of the InScope(...) function, please check the
MSDN documentation: http://msdn2.microsoft.com/en-us/library/ms226955
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
news:A5123C30-6A02-4480-A75E-8D3D6FD9AD43@.microsoft.com...
> Have a matrix with three columns depicting the following table:
> # of Results # of Results
> % of Results
> within 5 working days > 5 working days
> >5
> working days
> January 33 1
> February 24 16
> etc
> The two # of Results colums have a count(Fields!January.Value) in the data
> box.
> I now need to get the % column done. I am having difficulty in doing
> this.
> Any help would be greatly appreciated.
>|||BTW: in your case it seems like you don't really need a matrix. You can use
a table with a table grouping based on the month. Then inside the table
columns, you use expressions like shown below to determine the actual
counts:
=Sum(iif( /* condition */, 1, 0))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:ucv9B0uwFHA.720@.TK2MSFTNGP15.phx.gbl...
> You could add a subtotal column. In the matrix cell, you then need to take
> into account the subtotal scope and use a different expression to
> calculate the percentage. Something similar to this:
> =iif(InScope("MatrixColumnGroupingName"), Count(Fields!Cases.Value),
> Sum(iif( /* condition for the first column group instance */, 1, 0)) /
> Sum(iif( /* condition for the second column group instance */, 1, 0)))
> The comments in the expression above need to be replaced with the matrix
> column grouping expression (basically identify the data rows that
> contribute to the first grouping column vs. the second grouping column).
> For more information about the usage of the InScope(...) function, please
> check the MSDN documentation:
> http://msdn2.microsoft.com/en-us/library/ms226955
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
> news:A5123C30-6A02-4480-A75E-8D3D6FD9AD43@.microsoft.com...
>> Have a matrix with three columns depicting the following table:
>> # of Results # of Results
>> % of Results
>> within 5 working days > 5 working days >5
>> working days
>> January 33 1
>> February 24 16
>> etc
>> The two # of Results colums have a count(Fields!January.Value) in the
>> data
>> box.
>> I now need to get the % column done. I am having difficulty in doing
>> this.
>> Any help would be greatly appreciated.
>>
>|||Thanks for the point in the right direction Robert.
Still not sure how to get the percentage column. Below is my code for the
report thus far. Probably crude but hey im still learning this stuff.
SELECT
PMI.LaboratorySentToOranisationId as LabId,
O.Name as LabName,
TTLI.Name as TestType,
LTD.DateRecieved as DateReceived,
LTD.TestCompletedDate as CompleteDate,
/*# of Results within 5 working days*/
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '01') then 1
Else 0
End) as January,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '02') then 1
Else 0
End) as February,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '03') then 1
Else 0
End) as March,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '04') then 1
Else 0
End) as April,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '05') then 1
Else 0
End) as May,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '06') then 1
Else 0
End) as June,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '07') then 1
Else 0
End) as July,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '08') then 1
Else 0
End) as August,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '09') then 1
Else 0
End) as September,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '10') then 1
Else 0
End) as October,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '11') then 1
Else 0
End) as November,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '12') then 1
Else 0
End) as December,
/*# of Results >5 Working Days*/
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '01') then 1
Else 0
End) as Jan1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '02') then 1
Else 0
End) as Feb1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '03') then 1
Else 0
End) as Mar1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '04') then 1
Else 0
End) as Apr1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '05') then 1
Else 0
End) as May1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '06') then 1
Else 0
End) as Jun1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '07') then 1
Else 0
End) as Jul1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '08') then 1
Else 0
End) as Aug1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '09') then 1
Else 0
End) as Sep1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '10') then 1
Else 0
End) as Oct1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '11') then 1
Else 0
End) as Nov1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '12') then 1
Else 0
End) as Dec1
FROM
PostMortemInspection PMI
INNER JOIN LabTestRequest LTR on LTR.PostMortemInspectionId = PMI.Id
INNER JOIN Organisation O on PMI.LaboratorySentToOranisationId = O.Id
AND (O.EndDate is NULL)
INNER JOIN TestTypeLookUpItem TTLI on LTR.TestTypeLookUpItemId = TTLI.Id and
(TTLI.Id = '13')
INNER JOIN LabTestDetail LTD on LTR.Id = LTD.LabTestRequestId
--where (ltd.DateRecieved is NOT NULL)
As you can see im using Case statements for each column of data. In the
field of the table im using =SUM(Fields!January.Value). So getting the % is
hard. What I was trying to do was take (column B * 100) / column A.
Something along these lines: =SUM(Fields!Jul1.Value * 100) /
Fields!July.Value. However im getting Infinity displayed.
Any more help would be greatly appreciated.
Cheers
"Robert Bruckner [MSFT]" wrote:
> BTW: in your case it seems like you don't really need a matrix. You can use
> a table with a table grouping based on the month. Then inside the table
> columns, you use expressions like shown below to determine the actual
> counts:
> =Sum(iif( /* condition */, 1, 0))
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:ucv9B0uwFHA.720@.TK2MSFTNGP15.phx.gbl...
> > You could add a subtotal column. In the matrix cell, you then need to take
> > into account the subtotal scope and use a different expression to
> > calculate the percentage. Something similar to this:
> >
> > =iif(InScope("MatrixColumnGroupingName"), Count(Fields!Cases.Value),
> > Sum(iif( /* condition for the first column group instance */, 1, 0)) /
> > Sum(iif( /* condition for the second column group instance */, 1, 0)))
> >
> > The comments in the expression above need to be replaced with the matrix
> > column grouping expression (basically identify the data rows that
> > contribute to the first grouping column vs. the second grouping column).
> > For more information about the usage of the InScope(...) function, please
> > check the MSDN documentation:
> > http://msdn2.microsoft.com/en-us/library/ms226955
> >
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> >
> > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
> > news:A5123C30-6A02-4480-A75E-8D3D6FD9AD43@.microsoft.com...
> >> Have a matrix with three columns depicting the following table:
> >>
> >> # of Results # of Results
> >> % of Results
> >> within 5 working days > 5 working days >5
> >> working days
> >>
> >> January 33 1
> >> February 24 16
> >>
> >> etc
> >>
> >> The two # of Results colums have a count(Fields!January.Value) in the
> >> data
> >> box.
> >> I now need to get the % column done. I am having difficulty in doing
> >> this.
> >> Any help would be greatly appreciated.
> >>
> >>
> >
> >
>
>