Link to home
Start Free TrialLog in
Avatar of Amour22015
Amour22015

asked on

SQL Server/SSRS 2008 r2 - Percentage Expression

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 .....
Avatar of PortletPaul
PortletPaul
Flag of Australia image

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.
Avatar of Amour22015
Amour22015

ASKER

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....
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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...
Thank you again for helping...
No problem. Glad you got what you needed.