x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 899

# SSRS matrix percentage for specific row

I have been asked to calculate the percentage of a given row in a matrix report, instead of percentage of entire matrix. How can this be done?  My query is below. I have read about inscope but am unsure of how that is done.

My row will group the # of late tickets per requestor group.mac.vchge.Rqstor_Group,

If group A has total of 127 tickets 39 are yes (late) and 88 are no (not late). I would like the % of late changes just for this group not the entire matrix. What is the correct syntax for that? Thanks

this is the details section
=formatpercent(Fields!late.Value/ SUM(Fields!late.Value))
this is query:
SELECT     mac.vchge.Tkt_Number.Value,
mac.vchge.Rqstor_Group,
mac.vChge.Is_Late_Change,
mac.vChge.Why_late_change,

CASE WHEN mac.vChge.Is_Late_Change = 'Yes' THEN '1' WHEN mac.vChge.Is_Late_Change = 'No' THEN '2' ELSE 'Null' END AS late,

FROM         Mac.Vchge

WHERE     (Mac.Vchge.zImpl_Start_Time >= CONVERT(BIGINT, DATEDIFF(S, '19700101', '20380119')) + CONVERT(BIGINT, DATEDIFF(S, '20380119',
@From))) AND (Mac.Vchge.Chge_Area LIKE 'USA%') AND (Mac.Vchge.Status <> 'Cancelled') AND
(Mac.Vchge.zImpl__Start_Time <= CONVERT(BIGINT, DATEDIFF(S, '19700101', '20380119')) + CONVERT(BIGINT, DATEDIFF(S, '20380119',
@To)))
0
pgmtkl
2 Solutions

Commented:
0

Author Commented:
We are using SQL 2005, arent on 2008 yet.
0

Commented:
compute command is available even in SQL 2000. Try it will work
0

Commented:
I have does this with report code. At the top of of the report place a list with a textbox and set its data source and grouping, set the text box expression to:

=Code.IncrimentCount(Fields!Is_Late_Change.Value)

And add the code tot he report properties code window and access the variables like:
=Code.TotalLate and =Code.TotalNotLate

``````public dim TotalLate as integer
public dim TotalNotLate as integer

public function IncrimentCount(strIsLate as string)
if (strIsLate = "Yes") then
TotalLate = TotalLate+  1
else
TotalNotLate = TotalNotLate + 1
end if
end function
``````
0

Commented:
What if you use the following query?

``````select [Value],
Rqstor_Group,
Is_Late_Change,
Why_late_change,
sum(case when Late = 1 then 1 else 0 end) over (partition by Rqstor_Group) *1.0 / sum(Late) over (partition by Rqstor_Group) as [percYes],
sum(case when Late = 2 then 1 else 0 end) over (partition by Rqstor_Group) *1.0 / sum(Late) over (partition by Rqstor_Group) as [percNo],
sum(case when Late = 0 then 1 else 0 end) over (partition by Rqstor_Group) *1.0 / sum(Late) over (partition by Rqstor_Group) as [percNull]
from (
SELECT
mac.vchge.Tkt_Number.Value,
mac.vchge.Rqstor_Group,
mac.vChge.Is_Late_Change,
mac.vChge.Why_late_change,
CASE WHEN mac.vChge.Is_Late_Change = 'Yes' THEN 1 WHEN mac.vChge.Is_Late_Change = 'No' THEN 2 ELSE 0 END AS late,
FROM         Mac.Vchge
WHERE     (Mac.Vchge.zImpl_Start_Time >= CONVERT(BIGINT, DATEDIFF(S, '19700101', '20380119')) + CONVERT(BIGINT, DATEDIFF(S, '20380119',
@From))) AND (Mac.Vchge.Chge_Area LIKE 'USA%') AND (Mac.Vchge.Status <> 'Cancelled') AND
(Mac.Vchge.zImpl__Start_Time <= CONVERT(BIGINT, DATEDIFF(S, '19700101', '20380119')) + CONVERT(BIGINT, DATEDIFF(S, '20380119',
@To)))
) a
``````
0

Commented:
I think the above is what your looking for, but just in case check this other alternative.
``````select [Value],
Rqstor_Group,
Is_Late_Change,
Why_late_change,
sum(Late) over (partition by Rqstor_Group, Late) *1.0 / sum(Late) over (partition by Rqstor_Group) as [perc]
from (
SELECT
mac.vchge.Tkt_Number.Value,
mac.vchge.Rqstor_Group,
mac.vChge.Is_Late_Change,
mac.vChge.Why_late_change,
CASE WHEN mac.vChge.Is_Late_Change = 'Yes' THEN 1 WHEN mac.vChge.Is_Late_Change = 'No' THEN 2 ELSE 0 END AS late,
FROM         Mac.Vchge
WHERE     (Mac.Vchge.zImpl_Start_Time >= CONVERT(BIGINT, DATEDIFF(S, '19700101', '20380119')) + CONVERT(BIGINT, DATEDIFF(S, '20380119',
@From))) AND (Mac.Vchge.Chge_Area LIKE 'USA%') AND (Mac.Vchge.Status <> 'Cancelled') AND
(Mac.Vchge.zImpl__Start_Time <= CONVERT(BIGINT, DATEDIFF(S, '19700101', '20380119')) + CONVERT(BIGINT, DATEDIFF(S, '20380119',
@To)))
) a
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.