asked on

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)))

puru1981

Look at the link if it works.

http://msdn.microsoft.com/en-us/library/ms181708.aspx

pgmtkl

ASKER

We are using SQL 2005, arent on 2008 yet.

ASKER CERTIFIED SOLUTION

puru1981

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gbarcalow

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

SOLUTION

ralmada