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:37 PM

Answers

  • Hello,

    For achieving this you need to check the query in the dataset and the expressions in the report design.

    Query should be like below:

    SELECT A.Course, A.Year, A.Month, A.DetailVal, B.EmpVal
    FROM
    (
    SELECT CourseCol AS Course, YearCol AS Year, MonthCol AS Month, SUM(DetVal) AS DetailVal
    FROM TableName
    GROUP BY CourseCol, YearCol, MonthCol
    ) A
    INNER JOIN
    (
    SELECT YearCol AS Year, SUM(Empolyees) AS EmpVal
    FROM TableName
    GROUP BY YearCol
    ) B
    ON A.Year = B.Year

    In the report:

    1. Add one more group footer row below the sum of the groups and place the expression as below:
    =First(Fields!EmpVal.Value)
    2. Add one more group footer row below the one added in the above step and and place the expression as below:
    =FORMAT(SUM(Fields!Detval.Value)/First(Fields!EmpVal.Value),"P")

    Hope its clear & helpful....


    Pavan Kokkula Infosys Technologies Limited.
    • Marked as answer by Jerry Nee Monday, September 27, 2010 1:07 AM
    Thursday, September 16, 2010 4:00 AM

All replies

  • Hi there,

    I think yo need to modify your query. So all the data is coming from emps table??

    If that's the case.I think

    select distinct employeenumber, Year
    from emps

    Group By employeenumber, Year

    try like thisand update me....If it's not the case

    More importantly you need to adjust your group with the year or month....

    all the best...

    Jeevan Kalyan

     


    Dasari
    Thursday, September 16, 2010 12:16 AM
  • Hello,

    For achieving this you need to check the query in the dataset and the expressions in the report design.

    Query should be like below:

    SELECT A.Course, A.Year, A.Month, A.DetailVal, B.EmpVal
    FROM
    (
    SELECT CourseCol AS Course, YearCol AS Year, MonthCol AS Month, SUM(DetVal) AS DetailVal
    FROM TableName
    GROUP BY CourseCol, YearCol, MonthCol
    ) A
    INNER JOIN
    (
    SELECT YearCol AS Year, SUM(Empolyees) AS EmpVal
    FROM TableName
    GROUP BY YearCol
    ) B
    ON A.Year = B.Year

    In the report:

    1. Add one more group footer row below the sum of the groups and place the expression as below:
    =First(Fields!EmpVal.Value)
    2. Add one more group footer row below the one added in the above step and and place the expression as below:
    =FORMAT(SUM(Fields!Detval.Value)/First(Fields!EmpVal.Value),"P")

    Hope its clear & helpful....


    Pavan Kokkula Infosys Technologies Limited.
    • Marked as answer by Jerry Nee Monday, September 27, 2010 1:07 AM
    Thursday, September 16, 2010 4:00 AM