Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# SSRS 2008 r2 - Expression adding columns together to get percentages

Posted on 2016-07-22
Medium Priority
48 Views
Hi Experts,

I am trying to do a percentage in one of my reports.

I have this:
``````=IIF(Fields!statustype.Value <> "No Status", IIF(Fields!statustype.Value = "Complete", Sum(Fields!Total), 0) + IIF(Fields!statustype.Value = "RBD", Sum(Fields!Total), 0) + IIF(Fields!statustype.Value = "Response", Sum(Fields!Total), 0) /Sum(Fields!Total), 0)
``````

But I get a #ERROR in the report....

Statustype has these values:
"Completed"
"RBD"
"Response"
"No Status"

Report:
Status Type | Severity

statustype: | [Total]                                        |
Total:           | [=sum(Fields!Total.Value)]     |
Percent:      |[Expression I am looking for] |

I am adding all the values of Statustype(s) together and dividing by the total to get the percentage.

0
Question by:Amour22015
[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
• 7
• 5

LVL 53

Expert Comment

ID: 41725507
IF(Fields!statustype.Value = "Response", Sum(Fields!Total), 0) /Sum(Fields!Total), 0) should probably be

IF(Fields!statustype.Value = "Response", Sum(Fields!Total) /Sum(Fields!Total),0)

Unless Sum(Fields!Total) is greater than zero, it will always be 1 or 100%.  If it is zero, you will get an error.  Are you sure that is what you want to divide?
0

Author Comment

ID: 41726002
Hi,

I tried using your expression but that is not correct either.

Remember there is:
"Completed"
"RBD"
"Response"
"No Status"

Report this is what I am looking for:
Status Type | Severity

statustype:
"Completed"| 0 |0|
"RBD"           |2  |0|
"Response" | 24|0|
"No Status"| 2   |2|
Total:           | 28|2|
Percent:     |92%|0%|

So I am guessing the last read gets the results so if NOT "Response" then 0 most all the outcome was 0 even when there was a "Response" selected.

``````=IIF(Fields!statustype.Value <> "No Status", IIF(Fields!statustype.Value = "Complete", Sum(Fields!Total.Value), 0) + IIF(Fields!statustype.Value = "RBD", Sum(Fields!Total.Value), 0) + IIF(Fields!statustype.Value = "Response", Sum(Fields!Total.Value), 0) /Sum(Fields!Total), 0)"
``````

Report this is result:
Status Type | Severity

statustype:
"Completed"| 0 |0|
"RBD"           |2  |0|
"Response" | 24|0|
"No Status"| 2   |2|
Total:           | 28|2|
Percent:     |0%|0%|

This only gives me a 0 percentage regardless if other numbers are included, because there is a "No Status" included.

0

LVL 49

Expert Comment

ID: 41727477
I can only offer a T-SQL based solution and have made assumptions about the data structure, hope it helps.
``````declare @d table
( statustype varchar(20), Total decimal(12,3) )
;

insert into @d
values
('Completed',10.00)
,('RBD',10.00)
,('Response',10.00)
,('No Status',10.00)
;

select
statustype
, sumtotal
, sumtotal / sum(sumtotal) over(partition by (select 1)) as pct
from (
SELECT
statustype
, SUM([Total]) sumtotal
FROM @d
GROUP BY
statustype
) d
``````
result
``````statustype sumtotal pct
---------- -------- --------
Completed  10.000   0.250000
No Status  10.000   0.250000
RBD        10.000   0.250000
Response   10.000   0.250000

(4 row(s) returned)

(4 row(s) affected)
``````
tip:
A question that has both 1. sample data, and 2. expected result
0

Author Comment

ID: 41727545
Hi,

So I take your code in put it in the current SP and then I would place the pct value in the field?
Like:
Sum(Field!PCT.VALUE)?

But I get a error.

0

Author Comment

ID: 41727709
Ok,

So I ran the query you posted but I get errors:
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.
The statement has been terminated.

Also on this:
('Completed', 10.00)
etc...

You are putting in the value = 10.00?
so I would do something like?:
('Completed', [Total])

0

LVL 49

Expert Comment

ID: 41728508
I was providing sample data to show how it works, you do not need that sample data in your solution.

>>"String or binary data would be truncated."
I am proposing some arithmetic, not text handling, so I have no idea what change you made to your code that would cause that error. Are you attempting to squeeze some long string into a varchar(20) perhaps?

I have proposed a way for you to get the overall total. If you need further help getting that into your existing query then I need to see that query; not just the error messages.
0

Author Comment

ID: 41728582
Ok,

``````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
``````

0

LVL 49

Expert Comment

ID: 41728864
Where did you attempt to put in the percentage logic?
0

LVL 49

Expert Comment

ID: 41728887
try this, see if the new [Gtotal] column holds what you expect it to hold, if that's OK than use it to produce the percentage being careful about divide by zero

note I doubt you get value by using UNION so I changed to UNION ALL - but check it

``````DECLARE @SCANDATE AS date
SET @SCANDATE = '20160701' -- << whatever you need here

DECLARE @DB_SCANDATE AS date
DECLARE @DB_REGION AS varchar(255)
DECLARE @DB_VISN AS varchar(255)
DECLARE @DB_FACILITY AS varchar(255)
SET @DB_SCANDATE = @SCANDATE
SET @DB_REGION = @REGION
SET @DB_VISN = @VISN
SET @DB_FACILITY = @FACILITY

SELECT
d.[Branch Region]
, d.[Branch Network]
, d.branch
, d.[Total]
, d.[Gtotal]
, d.statustype
, d.severity
, d.MajorGrouping
FROM (
SELECT
st.[Branch Region]
, st.[Branch Network]
, st.branch
, st.[Total]
, SUM([Total]) OVER(ORDER BY (SELECT 1)) as Gtotal
, st.statustype
, st.severity
, st.MajorGrouping
FROM (
SELECT
[Branch Region]
, [Branch Network]
, branch
, COUNT(iplong) AS [Total]
, statustype
, severity
, MajorGrouping
FROM (
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')
) Base_Q
WHERE cnt = 1
GROUP BY
[Branch Region]
, [Branch Network]
, branch
, statustype
, severity
, MajorGrouping
) AS st
UNION ALL /* I really doubt you ned UNION DISTINCT but check it please*/
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 /* I really doubt you ned UNION DISTINCT but check it please*/
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 d
``````
as a footnote. CTEs do not make code easier to read.
0

Author Comment

ID: 41730100
Hi Paul Maxwell,

Thank you for helping, but I most have been confusing in my post.

On your code there is an error:

HHH_CTE

has not been verified in the query.

But the main reason for my post is not to change the stored procedure but only to add the logic for:
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)"

So in the equation one does not even have to mention "No Status"

Which might convert to?: <== yes this is a question if this is not correct need help...
Sum("Completed", [Total] + "RBD", [Total] + "Response", [Total])/Sum([Total])*100

0

LVL 49

Accepted Solution

PortletPaul earned 2000 total points
ID: 41730207
hmmm. You are asking me if that formula (just above this comment) is correct?

To know if that formula is correct requires an understanding of the functional requirements (or "business rules") that you have been asked to meet. It also requires an understanding of the data itself.

I cases such as this all I can suggest is some sample data (a) and the expected result (b)  and when bth (a) and (b) are available we can work out the wanted logic. Without it we are flying blind.

In short I can't really confirm if that formula is correct or not. but percentage is usually done this way:

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

Author Comment

ID: 41730371
I have been with Experts for years.

Seems I have had much harder questions posted in the past and the experts would answer them within a hour after I posted.

Know looks like even an easy question goes unanswered for days or maybe weeks...

I guess Experts has changed.

0

Author Closing Comment

ID: 41730376
It is tough to get an answer on Experts, even Scott Fell, EE MVE did not post a correcting answer because I found out in that case I was missing Field!Tota..VALUE...     VALUE
0

## Featured Post

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
###### Suggested Courses
Course of the Month8 days, 9 hours left to enroll