# Totals Problem in Report

• ### Question

• Hi, I have a report which displays how many people per month and year attended a course out of the whole organisation:

2008           2009

Jan             Jan

Word           5               9

Excel          10               5

Access        7                4

Total         22            18

The report only displays the people who attended.  The database I have got has everyone who worked in the organisation.  I need the total number of people who attended the course to be out of the whole organisation total for that year. e.g.  22 people out of 2000 attended a course in 2008 and 18 people out of 1500 attended a course in 2009.

I created another query within the report which gives all employees e.g.

select distinct employeenumber
from emps

I then added this into the matrix, but it gives an overall total for 2008 and 2009 e.g. 3500 instead of individual year totals.

I would like the report to look like:

2008           2009

Jan             Jan

Word                                    5               9

Excel                                   10               5

Access                                  7                4

Total                                   22            18

Total employees for year 2000         1500

% of total                          1.1%          1.2%

I am unsure of how I can achieve this.  The repot is also based on parameters where the user can pick individual months and years e.g. so they can compare Feb 2008 and 2009

Thank you

• Moved by Friday, September 17, 2010 1:43 AM (From:Transact-SQL)
Wednesday, September 15, 2010 7:35 PM

• Hi,

Based on my understaing, you have two datasets in the matrix. One is to contain only the attented employees, and the other is to contain the whole orgnization people and used to cacluate the total number of whole orgnization in the matrix. So, your issue occured. To solve it, i would recommand you rebuild the dataset query string to derive an additional column called AttendedFlag which is used to record who had attended a course, for example, 1 stands for attendance, 0 stands for absence. So, on the matrix,

=Count(IIF(Fields!AttendedFlag.value = 1,1,0)) is use to calculate the number of people attended,

=Count(Fields!Employeeid.value) is used to calcuate the total number of whole orginzation.

=Count(IIF(Fields!AttendedFlag.value = 1,1,0)) / Count(Fields!Employeeid.value) is to calcuate the % of total

thanks,
Jerry

• Marked as answer by Monday, September 27, 2010 1:06 AM
Friday, September 17, 2010 7:17 AM

### All replies

• Better to move to SQL Reporting Services forum.
Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
Wednesday, September 15, 2010 7:49 PM
• Yes, sorry i posted here initally until I seen there was a SQL reporting forum!  I hope someone can help, as I am totally stuck! Many thanks
Wednesday, September 15, 2010 7:56 PM
• Hi,

Based on my understaing, you have two datasets in the matrix. One is to contain only the attented employees, and the other is to contain the whole orgnization people and used to cacluate the total number of whole orgnization in the matrix. So, your issue occured. To solve it, i would recommand you rebuild the dataset query string to derive an additional column called AttendedFlag which is used to record who had attended a course, for example, 1 stands for attendance, 0 stands for absence. So, on the matrix,

=Count(IIF(Fields!AttendedFlag.value = 1,1,0)) is use to calculate the number of people attended,

=Count(Fields!Employeeid.value) is used to calcuate the total number of whole orginzation.

=Count(IIF(Fields!AttendedFlag.value = 1,1,0)) / Count(Fields!Employeeid.value) is to calcuate the % of total

thanks,
Jerry

• Marked as answer by Monday, September 27, 2010 1:06 AM
Friday, September 17, 2010 7:17 AM