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

• 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")

Pavan Kokkula Infosys Technologies Limited.
• Marked as answer by 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")