Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006
    Posts
    115

    Question Unanswered: Report Server: division between 2 subtotal, can this be done?

    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!

  2. #2
    Join Date
    Feb 2004
    Posts
    193
    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

  3. #3
    Join Date
    Oct 2006
    Posts
    115
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •