Solved

SSRS matrix percentage for specific row

Posted on 2010-08-23
6
889 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
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now