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
    Wednesday, September 15, 2010 7:36 PM

Answers

  • Hi reporter-reporter

    Your query against your emps table has no reference to a date, so fundamentally I cant see how you can come up with your separate figures by year, let alone month? 

    It seems like a SQL issue, not really SSRS.  If you can't get the numbers out in SQL, you are unlikely to be able to present them on your SSRS page...

    I'm imagining you'd need some start & end dates on each emps record, joined to a date calendar-style table at day level, grouped to year or month.  That should produce the numbers you want. 

    Good luck!

    Mike

    • Marked as answer by Jerry Nee Monday, September 27, 2010 1:07 AM
    Thursday, September 16, 2010 2:33 PM