I have a table that have the following fields:
Outlet,EmployeeNumber,WorkingDate,WorkingHour,Ince ntive
when I put it into a matrix report, the Outlet is assiged to Page, the EmployeeNumber is assgined to ROW, the WorkingDate is assigned to Column and both WorkingHour & Incentive is assigned to Data(display in row but not column). There is a subtotal at the extreme right side.
May I know how can I squeeze in another element display at the report showing sum of Incentive / sum of WorkingHour? This new element shall be placed at each row as it's indicating the average incentive of each employee.
Thanks! :)I am not sure what you mean by assigned but in a query:
select Outlet
, EmployeeNumber
, WorkingDate
, WorkingHour
, Incentive
, ((select sum(WorkingHour) from tablename) / (select sum(Incentive) from tablename)) as HrsPerIncentive
from tablename
good luck
ddave|||Hi Dolfandave,
Thanks a lot, I did it the similar way but didn't realize i could just do it like that! there is an important condition for the subqueries as the EmployeeNumber must match each other.
However, this is not my ultimate goal. By doing this way, we are "hardcode"-ing the IncentivePerHr in every single row, which is not the proper way to do it isn't it? Yes, I'm trying to calculate IncentivePerHr not HrPerIncentive.
This is how the report should look like:
................WorkingDates............. ......................Subtotal <-- given by Report Designer in BI Dev Studio
EmployeNumber...............1/1..1/2..1/3..1/4.........1/31
12345..........Incentive......$0...$3...$6... $1..........$5.....$68
................WorkingHour...3hr..5hr..6hr.. 2hr.........4hr.....200hr
45678..........Incentive......$1...$6...$3... $3..........$8.....$100
................WorkingHour...7hr..7hr..3hr.. 3hr.........3hr.....190hr
23456..........Incentive......$6...$7...$6... $2..........$3.....$99
................WorkingHour...4hr..2hr..9hr.. 7hr.........8hr.....140hr
There is a subtotal given by the report designer. How can I just add a column to show the IncentivePerHr by making use of the subtotal results to do a division? There is a potential problem too as some EmployeeNumber has ZERO hours in total thus the parser will complain that we are trying to divide by ZERO.
any idea?
Friday, March 9, 2012
Report Server: division between 2 subtotal, can this be done?
Labels:
database,
division,
employeenumber,
fieldsoutlet,
following,
ince,
matrix,
microsoft,
mysql,
ntivewhen,
oracle,
outlet,
report,
server,
sql,
subtotal,
table,
workingdate,
workinghour
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment