Solved

SSRS 2008 r2 - Expression adding columns together to get percentages

Posted on 2016-07-22
13
31 Views
Last Modified: 2016-07-26
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)

Open in new window



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.


 Please help and thanks...
0
Comment
Question by:Amour22015
  • 7
  • 5
13 Comments
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
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

by:Amour22015
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)"

Open in new window


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.

Please help and thanks...
0
 
LVL 48

Expert Comment

by:PortletPaul
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

Open in new window

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)

Open in new window

tip:
A question that has both 1. sample data, and 2. expected result
generally gets an answer faster
0
 

Author Comment

by:Amour22015
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.

Please help and thanks...
0
 

Author Comment

by:Amour22015
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])

Please help and thanks...
0
 
LVL 48

Expert Comment

by:PortletPaul
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Amour22015
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

Open in new window


Please help and thanks...
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41728864
Where did you attempt to put in the percentage logic?
0
 
LVL 48

Expert Comment

by:PortletPaul
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

Open in new window

as a footnote. CTEs do not make code easier to read.
0
 

Author Comment

by:Amour22015
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


Please help and Thank You .....
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 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

by:Amour22015
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.

Thank you for your help...
0
 

Author Closing Comment

by:Amour22015
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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