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)))  
pgmtklAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
puru1981Connect With a Mentor Commented:
compute command is available even in SQL 2000. Try it will work
0
 
puru1981Commented:
0
 
pgmtklAuthor Commented:
We are using SQL 2005, arent on 2008 yet.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
gbarcalowCommented:
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

Open in new window

0
 
ralmadaConnect With a Mentor 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

Open in new window

0
 
ralmadaCommented:
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

Open in new window

0
All Courses

From novice to tech pro — start learning today.