locked
Totals Problem in Report RRS feed

  • 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

    Can anyone advise me?

    Thank you

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

Answers

  • 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 Jerry Nee 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 Jerry Nee Monday, September 27, 2010 1:06 AM
    Friday, September 17, 2010 7:17 AM