An SSRS expression to calculate percentages

Hello,

I am preparing exam results reports in SSRS using Visual Studio 2008, and the actual report is visually very simple.

I have attached a simplified version here, and it simply lists subjects, grades, and the total of each grade.

I am trying to add a column at the end, to show the percentage of both A and B grades together, within all grades (A,B,C,D).

So, for example, in English, out of a total of 25 candidates, 14 had A or B, so the percentage of A,B is 56%

I'm struggling to write an expression to do this. I would normally do a expression, consisting of a calculation, and reference textboxes in the report grid
to get the values, but in this case the data is grouped, so I'm not sure what to do.

I can't find any similar example on the web.

I have attached sample of the report preview, rendered report, and the SQL output grid showing all the field names.

Can anyone help, please?

Many thanks,

Jim Dorans

the report in design modethe rendered reportthe SQL output showing file names
ict supportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
That would be tricky, given your column matrix. It should be easier to do this in your SQL query: if you share that, I can work something up for you.
John_VidmarCommented:
* Rename the Candidates textbox to Candidates
* Add a percentage-format to the textbox holding the below expression (note: I converted the denominator to Decimal because I think SSRS does that weird thing where int / int = int):
=SUM( IIF(Fields!Grade.Value = "A" or Fields!Grade.Value = "B", 1, 0) ) / CDec( ReportItems!Candidates.Value )

Open in new window

ict supportAuthor Commented:
John Vidmar :

Many thanks for your help here.

Your proposed solution, in principle, worked perfectly. It did return a number in the format nn.nnnnnn , so I amended it as follows :

old :
=SUM( IIF(Fields!Grade.Value = "A" or Fields!Grade.Value = "B", 1, 0) ) / CDec( ReportItems!Candidates.Value )

Open in new window

new :
= ROUND( SUM ( IIF(Fields!Grade.Value = "A" or Fields!Grade.Value = "B", 1, 0) ) / ( ReportItems!Candidates.Value ) * 100,0)

Open in new window

...in order to round to whole numbers.

Now, in the totals row, I want to add an expression in the bottom right box, to show the average of all the percentages. I encapsulated the above ROUND statement in a AVG() function, but got the error "aggregate functions can only be used on report items contained in page headers and footers".

Presumably this is because it is trying to average amounts that have not yet been calculated?

Do you think there is a solution for this please?

Thanks again!

Jim
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

ict supportAuthor Commented:
Russell Fox : I have chosen Jon Vidmar's SSRS solution, but I would very much like the option of doing this in SQL. My successor will be advanced in SQL, but not so experienced in SSRS.

As there will be many more exams reports to write, an SQL solution would be most useful.

The SSRS code is this :
SELECT * FROM  Grades WHERE Grade IN ('A', 'B', 'C', 'D') 

Open in new window


This is the sample code to create and populate table (full code script is attached in a file) :

CREATE TABLE [dbo].[Grades](
	[ResultID] [int] NOT NULL,
	[schoolid] [nvarchar](4) NULL,
	[intFormatYear] [int] NULL,
	[Subject] [nvarchar](255) NULL,
	[Grade] [nvarchar](255) NULL
)

INSERT [dbo].[Grades] ([ResultID], [schoolid], [intFormatYear], [Subject], [Grade]) VALUES (3296, N'5231', 2018, N'Maths', N'A')
INSERT [dbo].[Grades] ([ResultID], [schoolid], [intFormatYear], [Subject], [Grade]) VALUES (3299, N'5375', 2018, N'Maths', N'A


INSERT [dbo].[Grades] ([ResultID], [schoolid], [intFormatYear], [Subject], [Grade]) VALUES (28726, N'5668', 2018, N'Physics', N'A*')

Open in new window


Many thanks!

Jim
ee-create-populate-table.sql
Russell FoxDatabase DeveloperCommented:
Gotcha, I'd use a CTE:

DECLARE @Grades TABLE (
	[ResultID] [int] NOT NULL,
	[schoolid] [nvarchar](4) NULL,
	[intFormatYear] [int] NULL,
	[Subject] [nvarchar](255) NULL,
	[Grade] [nvarchar](255) NULL
)
 
INSERT @Grades
(
	[ResultID]
  , [schoolid]
  , [intFormatYear]
  , [Subject]
  , [Grade]
)
VALUES (3296, N'5231', 2018, N'Maths', N'A')
,(3299, N'5375', 2018, N'Maths', N'A*')
,(3302, N'5302', 2018, N'Maths', N'C')
,(28044, N'5616', 2018, N'Maths', N'A*')
,(28046, N'5648', 2018, N'Maths', N'C')
,(28047, N'5639', 2018, N'English', N'B')
,(28049, N'5610', 2018, N'Maths', N'C')
,(28053, N'5699', 2018, N'Maths', N'C')
,(28054, N'5614', 2018, N'English', N'C')
,(28055, N'5696', 2018, N'English', N'C')
,(28058, N'5720', 2018, N'Maths', N'A*')
,(28060, N'5790', 2018, N'English', N'B')
,(28063, N'5747', 2018, N'Maths', N'A')
,(28065, N'5739', 2018, N'Maths', N'A*')
,(28067, N'5773', 2018, N'English', N'A')
,(28069, N'5730', 2018, N'Maths', N'A')
,(28071, N'5727', 2018, N'Maths', N'A')
,(28073, N'5727', 2018, N'Maths', N'A')
,(28075, N'5769', 2018, N'Maths', N'C')
,(28076, N'5707', 2018, N'English', N'C')
,(28078, N'5771', 2018, N'Maths', N'C')
,(28080, N'5716', 2018, N'Maths', N'C')
,(28081, N'5750', 2018, N'English', N'B')
,(28083, N'5713', 2018, N'Maths', N'A*')
,(28086, N'5809', 2018, N'Maths', N'B')
,(28089, N'5805', 2018, N'Maths', N'B')
,(28090, N'5851', 2018, N'English', N'D')
,(28092, N'5836', 2018, N'Maths', N'C')
,(28094, N'5823', 2018, N'Maths', N'B')
,(28096, N'5848', 2018, N'Maths', N'C')
,(28098, N'5848', 2018, N'Maths', N'A*')
,(28100, N'5917', 2018, N'Maths', N'A')
,(28102, N'5973', 2018, N'Maths', N'B')
,(28104, N'5947', 2018, N'Maths', N'C')
,(28106, N'5915', 2018, N'Maths', N'C')
,(28108, N'5973', 2018, N'Maths', N'A')
,(28109, N'5967', 2018, N'English', N'B')
,(28111, N'5996', 2018, N'Maths', N'B')
,(28112, N'5922', 2018, N'English', N'A')
,(28114, N'5971', 2018, N'Maths', N'A')
,(28115, N'5992', 2018, N'English', N'C')
,(28117, N'5927', 2018, N'Maths', N'C')
,(28119, N'5994', 2018, N'Maths', N'C')
,(28121, N'5931', 2018, N'Maths', N'A*')
,(28123, N'5919', 2018, N'English', N'B')
,(28125, N'0015', 2018, N'Maths', N'A*')
,(28127, N'0024', 2018, N'Maths', N'A')
,(28129, N'0036', 2018, N'English', N'A')
,(28132, N'0022', 2018, N'Maths', N'A*')
,(28134, N'0057', 2018, N'Maths', N'A')
,(28136, N'0037', 2018, N'Maths', N'A*')
,(28139, N'0067', 2018, N'Maths', N'B')
,(28141, N'0021', 2018, N'Maths', N'B')
,(28142, N'0024', 2018, N'English', N'C')
,(28144, N'0084', 2018, N'Maths', N'A')
,(28146, N'0029', 2018, N'Maths', N'E')
,(28148, N'0071', 2018, N'Maths', N'A')
,(28150, N'0109', 2018, N'Maths', N'A*')
,(28152, N'0100', 2018, N'Maths', N'B')
,(28154, N'0164', 2018, N'Maths', N'A')
,(28155, N'0161', 2018, N'English', N'B')
,(28157, N'0191', 2018, N'Maths', N'A')
,(28158, N'0125', 2018, N'English', N'C')
,(28160, N'0187', 2018, N'Maths', N'A*')
,(28164, N'0163', 2018, N'Maths', N'A')
,(28165, N'0119', 2018, N'English', N'D')
,(28167, N'0119', 2018, N'Maths', N'B')
,(28168, N'0190', 2018, N'English', N'C')
,(28169, N'0183', 2018, N'English', N'A*')
,(28171, N'0259', 2018, N'Maths', N'A')
,(28173, N'0258', 2018, N'Maths', N'A*')
,(28175, N'0205', 2018, N'English', N'C')
,(28177, N'0295', 2018, N'Maths', N'A')
,(28179, N'0200', 2018, N'Maths', N'A')
,(28180, N'5863', 2018, N'English', N'C')
,(28182, N'0159', 2018, N'Maths', N'B')
,(28184, N'1457', 2018, N'Maths', N'A*')
,(28187, N'5978', 2018, N'Maths', N'C')
,(28189, N'1467', 2018, N'Maths', N'D')
,(28190, N'1477', 2018, N'English', N'B')
,(28192, N'1147', 2018, N'Maths', N'A')
,(28194, N'1775', 2018, N'Maths', N'A')
,(28195, N'1775', 2018, N'English', N'A*')
,(28197, N'1527', 2018, N'Maths', N'B')
,(28198, N'1427', 2018, N'English', N'B')
,(28200, N'1367', 2018, N'Maths', N'C')
,(28202, N'1567', 2018, N'Maths', N'E')
,(28203, N'1637', 2018, N'English', N'A')
,(28206, N'1697', 2018, N'Maths', N'A')
,(28208, N'1647', 2018, N'Maths', N'A*')
,(28210, N'1417', 2018, N'Maths', N'A')
,(28212, N'1677', 2018, N'Maths', N'A')
,(28214, N'1687', 2018, N'Maths', N'A*')
,(28216, N'1377', 2018, N'Maths', N'A*')
,(28219, N'1467', 2018, N'Maths', N'A*')
,(28221, N'1467', 2018, N'English', N'A')
,(28223, N'1357', 2018, N'Maths', N'A')
,(28225, N'1657', 2018, N'Maths', N'A*')
,(28227, N'5668', 2018, N'Maths', N'A*')
,(28229, N'1617', 2018, N'English', N'B')
,(28232, N'1348', 2018, N'Maths', N'B')
,(28234, N'1436', 2018, N'Maths', N'A')
,(28236, N'1492', 2018, N'Maths', N'A*')
,(28238, N'1281', 2018, N'Maths', N'A*')
,(28536, N'5648', 2018, N'Physics', N'D')
,(28547, N'5699', 2018, N'Physics', N'B')
,(28572, N'5727', 2018, N'Physics', N'A*')
,(28580, N'5716', 2018, N'Physics', N'D')
,(28584, N'5809', 2018, N'Physics', N'C')
,(28587, N'5836', 2018, N'Physics', N'D')
,(28634, N'0022', 2018, N'Physics', N'A')
,(28637, N'0037', 2018, N'Physics', N'A*')
,(28646, N'0084', 2018, N'Physics', N'B')
,(28657, N'0100', 2018, N'Physics', N'C')
,(28658, N'0164', 2018, N'Physics', N'A')
,(28661, N'0187', 2018, N'Physics', N'A*')
,(28673, N'0258', 2018, N'Physics', N'A')
,(28686, N'1457', 2018, N'Physics', N'A')
,(28689, N'5978', 2018, N'Physics', N'C')
,(28692, N'1467', 2018, N'Physics', N'C')
,(28696, N'1775', 2018, N'Physics', N'B')
,(28719, N'1377', 2018, N'Physics', N'A*')
,(28722, N'1467', 2018, N'Physics', N'A')
,(28726, N'5668', 2018, N'Physics', N'A*')

; WITH [ABTotal]
AS (SELECT [Subject], ABTotal = COUNT(1)
	FROM @Grades
	WHERE [Grade] = 'A' -- WHERE LEFT([Grade], 1) = 'A' --(if A* should be included)
		OR [Grade] = 'B'
	GROUP BY [Subject]
	)
SELECT [g].[ResultID]
	 , [g].[schoolid]
	 , [g].[intFormatYear]
	 , [g].[Subject]
	 , [g].[Grade]
	 , [a].[ABTotal]
FROM @Grades g
	LEFT JOIN [ABTotal] a
		ON [a].[Subject] = [g].[Subject]

Open in new window

ict supportAuthor Commented:
Russell Fox + John Vidmar :

Thank you both for your different but valuable solutions.

Jim
ict supportAuthor Commented:
Hello, I closed this prematurely - I should have waited for an answer to the second part of my question :

Now, in the totals row, I want to add an expression in the bottom right box, to show the average of all the percentages. I encapsulated the above ROUND statement in a AVG() function, but got the error "aggregate functions can only be used on report items contained in page headers and footers".

Presumably this is because it is trying to average amounts that have not yet been calculated?

Do you think there is a solution for this please? Sorry for the confusion.

Thanks again!
John_VidmarCommented:
I originally mentioned adding a textbox-format:  Add a percentage-format to the textbox holding the below expression

With the proper format (I think 0%), you don't need round-function, or multiplying by 100.

Please show a picture of the expression location in your matrix.
ict supportAuthor Commented:
Hi John,

Thank you for your continuing support here.

The attached pic shows your proposed expression, and my amended version of that.

This is what is looks like, based on my understanding of your answer.

The top section shows the matrix - the red arrow point shows the textbox which I renamed to "Candidates".

The blue arrow point shows the box where your expression was entered :

=SUM( IIF(Fields!Grade.Value = "A" or Fields!Grade.Value = "B", 1, 0) ) / CDec( ReportItems!Candidates.Value )

Open in new window


… and the middle section of the pic shows the result, with lots of decimal places.

The bottom section of the pic shows the result when I used the amended expression :

= ROUND( SUM ( IIF(Fields!Grade.Value = "A" or Fields!Grade.Value = "B", 1, 0) ) / ( ReportItems!Candidates.Value ) * 100,0)

Open in new window


… and as you can see, it shows whole numbers, and the percentages are correct.

The green arrow point shows where I am trying to create an expression which will give the average of the percentages, in other words, the row percentages are 56, 70, 53 ...so the average would be 60.

This is now what I am trying to achieve.

I attempted this : I encapsulated the above ROUND statement in a AVG() function, but got the error "aggregate functions can only be used on report items contained in page headers and footers".

Presumably this is because it is trying to average amounts that have not yet been calculated?

This is what I am struggling with now.

I hope I have presented everything clearly, so you can spot if I have done something wrong, or misunderstood your original advice.


Thanks.
ee-combined.jpg
John_VidmarCommented:
Alter the expression pointed by the green-arrow to use a simple count-aggregate-function in the denominator instead of referencing a TextBox value (ReportItems):
=SUM( IIF(Fields!Grade.Value = "A" or Fields!Grade.Value = "B", 1, 0) ) / CDec( Count(Fields!ResultID.Value) )

Open in new window

This is the graphical technique to add a Percentage-Format, this resulting-format can then be viewed/altered on the properties-pane, which you can copy/paste to other percentage-textboxes, the below example will display a value of 0.12345 as 12.35% (note the last digit rounding):
SSRS TextBox Percentage Format

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ict supportAuthor Commented:
@John_Vidmar - thank you for your solution. it works just fine.

Jim
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.