An SSRS expression to calculate percentages

ict support
ict support used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Russell FoxDatabase Developer
Top Expert 2014

Commented:
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.
* 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

Author

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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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 Developer
Top Expert 2014

Commented:
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

Author

Commented:
Russell Fox + John Vidmar :

Thank you both for your different but valuable solutions.

Jim

Author

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!
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.

Author

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

Author

Commented:
@John_Vidmar - thank you for your solution. it works just fine.

Jim

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial