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!Tota l.Value)
Using the Stored Procedure:
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 .....
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!Tota
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
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 .....
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:
It does not pick up:
'RBD','Response'
On:
This looks like the data:
result:
Please help and thanks....
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]
It does not pick up:
'RBD','Response'
On:
branch statustype [Total]
ABC Completed $100
ABC RBD $100
ABC Response $100
ABC No Status $100
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
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
Please help and thanks....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
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...
ASKER
Thank you again for helping...
No problem. Glad you got what you needed.
Open in new window
What I'm unsure about is exactly what you want as a result. Is it like this?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.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.