Solved

# SSRS matrix percentage for specific row

Posted on 2010-08-23
887 Views
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
Question by:pgmtkl

LVL 9

Expert Comment

0

Author Comment

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

LVL 9

Accepted Solution

puru1981 earned 250 total points
compute command is available even in SQL 2000. Try it will work
0

LVL 3

Expert Comment

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

LVL 41

Assisted Solution

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

LVL 41

Expert Comment

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

## Featured Post

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…