Solved

SSRS matrix percentage for specific row

Posted on 2010-08-23
6
893 Views
Last Modified: 2013-11-27
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
Comment
Question by:pgmtkl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 9

Expert Comment

by:puru1981
ID: 33501747
0
 

Author Comment

by:pgmtkl
ID: 33501995
We are using SQL 2005, arent on 2008 yet.
0
 
LVL 9

Accepted Solution

by:
puru1981 earned 250 total points
ID: 33502059
compute command is available even in SQL 2000. Try it will work
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 3

Expert Comment

by:gbarcalow
ID: 33504201
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
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 250 total points
ID: 33505148
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
 
LVL 41

Expert Comment

by:ralmada
ID: 33505179
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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question