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.

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

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

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.

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

Many thanks for your help here.

Your proposed solution, in principle, worked perfectly. It did return a number in the format

old :

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

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

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

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

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

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*')
```

Many thanks!

Jim

ee-create-populate-table.sql

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

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!

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.

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

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

… 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

```
=SUM( IIF(Fields!Grade.Value = "A" or Fields!Grade.Value = "B", 1, 0) ) / CDec( Count(Fields!ResultID.Value) )
```

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):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
SQL

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.