Solved

SQL Server/SSRS 2008 r2 - Percentage Expression

Posted on 2016-07-26
6
60 Views
Last Modified: 2016-07-28
Hi Experts,

I am trying to do a percentage like:

=Sum("Completion", [total]) + Sum("Response", [Total])/Sum(Total) * 100 as PCT


I have also tried to do an Expression:
=sum(Field!.Total.Value, "Completion") + sum(Field!Total.Value, "Response")/Sum(Field!Total.Value)

Using the Stored Procedure:
ALTER PROCEDURE [App].[usp_RP_REEFReports_PercentMitigated_Totals]  
	@SCANDATE as date,
	@REGION as Varchar(255),
	@VISN as varchar(255),
	@FACILITY as varchar(255)
AS
BEGIN
	SET NOCOUNT ON;

	Declare @DB_SCANDATE as date
	Set @DB_SCANDATE = @SCANDATE
	Declare @DB_REGION as Varchar(255)
	Set @DB_REGION = @REGION
	DECLARE @DB_VISN as varchar(255)
	SET @DB_VISN = @VISN
	DECLARE @DB_FACILITY as varchar(255)
	SET @DB_FACILITY = @FACILITY

;with Base_CTE as (
		SELECT  
			  CRE.[Plugin]
			  ,CRE.[IPLong], cre.scandateid, branch, branchregion as [Branch Region], cre.BranchNetwork as [Branch Network], CRE.[CVA_Remediation_EffortID]
			  ,CRE. [ScanDate],  cs.statustype,  CRE.CVA_EffortID, ce.[StatusID], vn.Severity
			  ,ROW_NUMBER () over (PARTITION by CRE.[IPLong], CRE.[Plugin] order by CRE.CVA_EffortID Desc) as cnt
			  , osg.MajorGrouping

		  FROM [FODW_PVT].[Dflt].[CVA_Remediation_Effort] as CRE
		  Inner join [FODW_PVT].[Dflt].[CVA_Effort] as CE on CRE.[CVA_EffortID] = CE.[CVA_EffortID]
		  inner join [Dflt].[CVA_Status] as cs on ce.[StatusID] = cs.[CVA_StatusID]
		  inner join dflt.Vulnerabilities	 as vn on cre.plugin = vn.plugin 
		  inner join (select distinct iplong, scandateid, osgroupid from dflt.Systems where ScanDateID in (SELECT distinct TOP 1 ID FROM [FODW_PVT].[Dflt].[ScanDates] order by ID desc))
				 as ss on cre.ScanDateID = ss.ScanDateID and cre.IPLong = ss.IPLong
		  INNER JOIN Dflt.OperatingSystemGroups as OSG on OSG.OSGroupID = ss.OSGroupID
		  where vn.severity <> 'Informational'  
		  and cre.scandate = @DB_SCANDATE
		  and cre.[BranchRegion] = @DB_REGION
		  AND (cre.[BranchNetwork] = @DB_VISN or @DB_VISN = 'All Branch Networks')
		  AND (cre.[Branch] = @DB_FACILITY or @DB_FACILITY = 'All Branches')

)

, HHH_CTE as (
				Select 	[Branch Region], [Branch Network], branch,  count(iplong) as [Total], statustype , severity, MajorGrouping 
				from Base_CTE
				where cnt = 1 
				group by [Branch Region], [Branch Network], branch, statustype , severity , MajorGrouping

				Union all

				----NOT ACTIONABLE---
				Select ra.[Branch Region], ra.[branch network], ra.branch,  sum([Total Vulnerabilities]) as [Total], 'Not Actionable per VA Response Table' as [statustype] , vn.severity
				, ra.MajorGrouping
				from [Dflt].[ReportsData_ALLPluginsTotalsOS] as ra
				inner join dflt.F_VAResponseActionItemNoDups() as vra on vra.plugin = ra.plugin and vra.scandateid = ra.scandateid 
						and vra.[Operating_Sys] = ra.majorgrouping
				inner join dflt.Vulnerabilities	 as vn on vra.plugin = vn.plugin 
				inner join dflt.scandates as sd on  sd.id = vra.scandateid 
				where sd.scandate = @DB_SCANDATE  and vra.actionable = 0 and vn.severity <> 'Informational' 
				AND ra.[Branch Region] = @DB_REGION
				AND (ra.[Branch Network] = @DB_VISN or @DB_VISN = 'All Branch Networks')
				AND (ra.[Branch] = @DB_FACILITY or @DB_FACILITY = 'All Branches')
				group by ra.[Branch Region], ra.Branch, vn.severity,  ra.[branch network], ra.MajorGrouping
				  ) 

 ---show the data here
		Select st.[Branch Region], st.[Branch Network], st.branch,  st.[Total] , st.statustype , st.severity, st.MajorGrouping
		from HHH_CTE as st
		
		Union

		Select ra.[Branch Region], ra.[Branch Network], ra.branch, 
		 case when st.total is null then sum(ra.[Total Vulnerabilities]) else  sum(ra.[Total Vulnerabilities]) - st.total end as [Total] , 
		 'No Status' as [statustype] , vn.severity, ra.MajorGrouping

		from [Dflt].[ReportsData_ALLPluginsTotalsOS] as ra
		inner join dflt.Vulnerabilities	 as vn on ra.plugin = vn.plugin  
		--this is needed to get the total not completed - take the sum of total vulns ,minus this total
		left outer join (Select [Branch Region], branch,  sum([Total]) as [total],  severity, MajorGrouping
							from HHH_CTE group by [Branch Region], branch,  severity, MajorGrouping ) as st on 
							st.branch = ra.branch and st.[Branch Region] = ra.[Branch Region] and vn.severity = st.severity and ra.MajorGrouping = st.MajorGrouping
		inner join dflt.scandates as sd on  sd.id = ra.scandateid 
		where ra.scandate = @DB_SCANDATE and vn.severity <> 'Informational' 
				AND ra.[Branch Region] = @DB_REGION
				AND (ra.[Branch Network] = @DB_VISN or @DB_VISN = 'All Branch Networks')
				AND (ra.[Branch] = @DB_FACILITY or @DB_FACILITY = 'All Branches')
		group by ra.[Branch Region], ra.branch , vn.severity, st.total,    ra.[branch network], ra.MajorGrouping
		---this is needed to take into account zero complete

		union
		
		Select ra.[Branch Region], ra.[branch network], ra.branch, 
		 0 as  [Total] , 'Completed'  as [statustype] , vn.severity, ra.MajorGrouping

		from [Dflt].[ReportsData_ALLPluginsTotalsOS] as ra
		inner join dflt.Vulnerabilities	 as vn on ra.plugin = vn.plugin  
		inner join dflt.scandates as sd on  sd.id = ra.scandateid 
		where ra.scandate = @DB_SCANDATE and vn.severity <> 'Informational' 
				AND ra.[Branch Region] = @DB_REGION
				AND (ra.[Branch Network] = @DB_VISN or @DB_VISN = 'All Branch Networks')
				AND (ra.[Branch] = @DB_FACILITY or @DB_FACILITY = 'All Branches')
		and branch not in (Select branch from HHH_CTE where statustype = 'Completed')
		group by ra.[Branch Region], ra.branch , vn.severity,      ra.[branch network], ra.MajorGrouping


END




GO

Open in new window



Looks like I am looking for something like:
Case statustype  In
 ("Completed",
   "RBD",
   "Response")
 And Sum([Total]) > 0 then

Sum("Completed (20)" + "RBD (10)" + "Response (20)")/70*100 As PCT
 50/70 = 71.43%
 Where 70 equals:
 "Completed (20)" + "RBD (10)" + "Response (20)" + "No Status(10)" + "What ever else there is (10)"

 Sum("Completed", [Total] + "RBD", [Total] + "Response", [Total])/Sum([Total])*100

Total is a dataset in SSRS with the fields:
statustype  =
("Completed",
   "RBD",
   "Response",
"No Status",
"Some other value")


 Please help and Thank You .....
0
Comment
Question by:Amour22015
  • 3
  • 3
6 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41730673
I assume the data looks like this (if I simplify the columns)
branch statustype [Total]     
ABC    Completed  $100
ABC    RBD        $100
ABC    Response   $100 
ABC    No Status  $100

Open in new window

What I'm unsure about is exactly what you want as a result. Is it like this?
branch statustype [Total] [Gtotal]  [C_R_R_total] is this what you want?
ABC    Completed  $100     $400     $300          (300*100)/400 = 75
ABC    RBD        $100     $400     $300          (300*100)/400 = 75
ABC    Response   $100     $400     $300          (300*100)/400 = 75
ABC    No Status  $100     $400     $300          (300*100)/400 = 75

Open in new window

Please look at lines 67 to 78, (75-77 in particular) for calculating "total of [Total]" and you seem to need at least 2 of these. One needs to be limited by status values and the other is not limited. I think you can use SUM() OVER() to produce these wanted values which lines 75-77 attempt to display.
ALTER PROCEDURE [App].[usp_RP_REEFReports_PercentMitigated_Totals]  
	@SCANDATE as date,
	@REGION as Varchar(255),
	@VISN as varchar(255),
	@FACILITY as varchar(255)
AS
BEGIN
	SET NOCOUNT ON;

	Declare @DB_SCANDATE as date
	Set @DB_SCANDATE = @SCANDATE
	Declare @DB_REGION as Varchar(255)
	Set @DB_REGION = @REGION
	DECLARE @DB_VISN as varchar(255)
	SET @DB_VISN = @VISN
	DECLARE @DB_FACILITY as varchar(255)
	SET @DB_FACILITY = @FACILITY

;with Base_CTE as (
		SELECT  
			  CRE.[Plugin]
			  ,CRE.[IPLong], cre.scandateid, branch, branchregion as [Branch Region], cre.BranchNetwork as [Branch Network], CRE.[CVA_Remediation_EffortID]
			  ,CRE. [ScanDate],  cs.statustype,  CRE.CVA_EffortID, ce.[StatusID], vn.Severity
			  ,ROW_NUMBER () over (PARTITION by CRE.[IPLong], CRE.[Plugin] order by CRE.CVA_EffortID Desc) as cnt
			  , osg.MajorGrouping

		  FROM [FODW_PVT].[Dflt].[CVA_Remediation_Effort] as CRE
		  Inner join [FODW_PVT].[Dflt].[CVA_Effort] as CE on CRE.[CVA_EffortID] = CE.[CVA_EffortID]
		  inner join [Dflt].[CVA_Status] as cs on ce.[StatusID] = cs.[CVA_StatusID]
		  inner join dflt.Vulnerabilities	 as vn on cre.plugin = vn.plugin 
		  inner join (select distinct iplong, scandateid, osgroupid from dflt.Systems where ScanDateID in (SELECT distinct TOP 1 ID FROM [FODW_PVT].[Dflt].[ScanDates] order by ID desc))
				 as ss on cre.ScanDateID = ss.ScanDateID and cre.IPLong = ss.IPLong
		  INNER JOIN Dflt.OperatingSystemGroups as OSG on OSG.OSGroupID = ss.OSGroupID
		  where vn.severity <> 'Informational'  
		  and cre.scandate = @DB_SCANDATE
		  and cre.[BranchRegion] = @DB_REGION
		  AND (cre.[BranchNetwork] = @DB_VISN or @DB_VISN = 'All Branch Networks')
		  AND (cre.[Branch] = @DB_FACILITY or @DB_FACILITY = 'All Branches')

)

, HHH_CTE as (
				Select 	[Branch Region], [Branch Network], branch,  count(iplong) as [Total], statustype , severity, MajorGrouping 
				from Base_CTE
				where cnt = 1 
				group by [Branch Region], [Branch Network], branch, statustype , severity , MajorGrouping

				Union all

				----NOT ACTIONABLE---
				Select ra.[Branch Region], ra.[branch network], ra.branch,  sum([Total Vulnerabilities]) as [Total], 'Not Actionable per VA Response Table' as [statustype] , vn.severity
				, ra.MajorGrouping
				from [Dflt].[ReportsData_ALLPluginsTotalsOS] as ra
				inner join dflt.F_VAResponseActionItemNoDups() as vra on vra.plugin = ra.plugin and vra.scandateid = ra.scandateid 
						and vra.[Operating_Sys] = ra.majorgrouping
				inner join dflt.Vulnerabilities	 as vn on vra.plugin = vn.plugin 
				inner join dflt.scandates as sd on  sd.id = vra.scandateid 
				where sd.scandate = @DB_SCANDATE  and vra.actionable = 0 and vn.severity <> 'Informational' 
				AND ra.[Branch Region] = @DB_REGION
				AND (ra.[Branch Network] = @DB_VISN or @DB_VISN = 'All Branch Networks')
				AND (ra.[Branch] = @DB_FACILITY or @DB_FACILITY = 'All Branches')
				group by ra.[Branch Region], ra.Branch, vn.severity,  ra.[branch network], ra.MajorGrouping
				  ) 

 ---show the data here
 
SELECT
      [Branch Region]
    , [Branch Network]
    , branch
    , severity
    , MajorGrouping
    , statustype 
    , [Total] 
    , sum([Total]) over(partition by   [Branch Region], [Branch Network], branch, severity, MajorGrouping) as [Gtotal]
    , sum(case when statustype IN ('Completed','RBD','Response') then [Total] end) 
        over(partition by   [Branch Region], [Branch Network], branch, severity, MajorGrouping) as [C_R_R_total]
FROM (
 		Select st.[Branch Region], st.[Branch Network], st.branch,  st.[Total] , st.statustype , st.severity, st.MajorGrouping
		from HHH_CTE as st
		
		Union

		Select ra.[Branch Region], ra.[Branch Network], ra.branch, 
		 case when st.total is null then sum(ra.[Total Vulnerabilities]) else  sum(ra.[Total Vulnerabilities]) - st.total end as [Total] , 
		 'No Status' as [statustype] , vn.severity, ra.MajorGrouping

		from [Dflt].[ReportsData_ALLPluginsTotalsOS] as ra
		inner join dflt.Vulnerabilities	 as vn on ra.plugin = vn.plugin  
		--this is needed to get the total not completed - take the sum of total vulns ,minus this total
		left outer join (Select [Branch Region], branch,  sum([Total]) as [total],  severity, MajorGrouping
							from HHH_CTE group by [Branch Region], branch,  severity, MajorGrouping ) as st on 
							st.branch = ra.branch and st.[Branch Region] = ra.[Branch Region] and vn.severity = st.severity and ra.MajorGrouping = st.MajorGrouping
		inner join dflt.scandates as sd on  sd.id = ra.scandateid 
		where ra.scandate = @DB_SCANDATE and vn.severity <> 'Informational' 
				AND ra.[Branch Region] = @DB_REGION
				AND (ra.[Branch Network] = @DB_VISN or @DB_VISN = 'All Branch Networks')
				AND (ra.[Branch] = @DB_FACILITY or @DB_FACILITY = 'All Branches')
		group by ra.[Branch Region], ra.branch , vn.severity, st.total,    ra.[branch network], ra.MajorGrouping
		---this is needed to take into account zero complete

		union
		
		Select ra.[Branch Region], ra.[branch network], ra.branch, 
		 0 as  [Total] , 'Completed'  as [statustype] , vn.severity, ra.MajorGrouping

		from [Dflt].[ReportsData_ALLPluginsTotalsOS] as ra
		inner join dflt.Vulnerabilities	 as vn on ra.plugin = vn.plugin  
		inner join dflt.scandates as sd on  sd.id = ra.scandateid 
		where ra.scandate = @DB_SCANDATE and vn.severity <> 'Informational' 
				AND ra.[Branch Region] = @DB_REGION
				AND (ra.[Branch Network] = @DB_VISN or @DB_VISN = 'All Branch Networks')
				AND (ra.[Branch] = @DB_FACILITY or @DB_FACILITY = 'All Branches')
		and branch not in (Select branch from HHH_CTE where statustype = 'Completed')
		group by ra.[Branch Region], ra.branch , vn.severity,      ra.[branch network], ra.MajorGrouping

   ) AS derived

END

Open in new window

It is quite possible I still don't understand the objective of your question. If so could you PLEASE provide a worked-up example because you can see the data and understand what you want: we cannot see any data and can only read what is on the page about your needs.
0
 

Author Comment

by:Amour22015
ID: 41732220
Hi Paul Maxwell,

Thank you for helping.

This is very close to what I am looking for.  I noticed that [C_R_R_total] is only taking the first value so in this case I only get:
'Completed'/SumTotal
(100*100)/400 = 25
Seems there is something wrong with:
So this part:
 
sum(case when statustype IN ('Completed','RBD','Response') then [Total] end) 
        over(partition by   [Branch Region], [Branch Network], branch, severity, MajorGrouping) as [C_R_R_total]

Open in new window


It does not pick up:
'RBD','Response'

On:
branch statustype [Total]     
ABC    Completed   $100
ABC    RBD               $100
ABC    Response     $100 
ABC    No Status     $100

Open in new window


This looks like the data:
Branch Network statustype [Total]     
A                 ABC    Completed  1
A                 ABC    RBD              10
A                 ABC    Response    20
A                 ABC    No Status    10
B                 ABC    Completed  1
B                 ABC    RBD               10
B                 ABC    Response      20
B                 ABC    No Status      10

Open in new window


result:
Network statustype [Total] [Gtotal]  [C_R_R_total] is this what you want?
ABC        Completed  2          82           62                   (62*100)/82= 75
ABC        RBD              20        82           62                   (62*100)/82= 75
ABC        Response    40        82           62                   (62*100)/82= 75
ABC        No Status    20        82           62                   (62*100)/82= 75

Open in new window



Please help and thanks....
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41732320
It works for me. Note I have changed the values of [Total] so that each row is different for [Total]
declare @Table1 table
    ([branch] varchar(3), [statustype] varchar(9), [Total] int)
;
    
INSERT INTO @Table1
    ([branch], [statustype], [Total])
VALUES
    ('ABC', 'Completed', 80),
    ('ABC', 'RBD', 70),
    ('ABC', 'Response', 60),
    ('ABC', 'No Status', 50)
;

Open in new window


select
      branch
    , statustype
    , [Total]
    , [C_R_R_total]
    , [Gtotal]
    , [Total]       * 100 / [Gtotal] as pct_1
    , [C_R_R_total] * 100 / [Gtotal] as pct_2
    , [Total]  * 100 / [C_R_R_total] as pct_3
from (
   select
          branch
        , statustype
        , [Total]
        , sum([Total]) over(partition by branch) as [Gtotal]
        , sum(case when statustype IN ('Completed','RBD','Response') then [Total] end) 
              over(partition by branch) as [C_R_R_total]
   from @table1
   ) d

Open in new window

Result is:
branch statustype Total C_R_R_total Gtotal pct_1 pct_2 pct_3 

------ ---------- ----- ----------- ------ ----- ----- ----- 
ABC    Completed  80    210         260    30    80    38    
ABC    RBD        70    210         260    26    80    33    
ABC    Response   60    210         260    23    80    28    
ABC    No Status  50    210         260    19    80    23    

Open in new window

NB:
If the case expression isn't working properly then one or more of the values inside the IN() may be incorrect
        , sum(case when statustype IN ('Completed','RBD','Response') then [Total] end)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Amour22015
ID: 41733735
Hi Paul Maxwell,

Thanks for helping..

I did not fully test, most have been something else causing.  

I did several test today and all is great.  

Great Job thanks for your help again...
0
 

Author Closing Comment

by:Amour22015
ID: 41733736
Thank you again for helping...
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41733815
No problem. Glad you got what you needed.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

705 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

18 Experts available now in Live!

Get 1:1 Help Now