Topics Map > University of Chicago > IT Services > Business Systems > Reporting Systems > Payroll/HR Data Warehousing

Business Objects - Webi - Payroll Pay and Benefit Amounts on the Same Report Line

This article explains how to place the payroll pay and benefit amounts on the same line in a Business Objects report table.

Normally when querying Payroll data you can select measurement objects for [Pay Amt], [Benefit Amt] and/or [Pay or Benefit Amt] to display the payroll distribution amounts. The result object; [Pay or Benefit Amt] will have a value depending on type of record (Pay or Benefit) and should be using in conjunction with the [Pay or Benefit Type Cd] result object (value P or F) to show what the type of value for the measurement is (and allow for grouping). The other two fields will only have a value if they are the matching type of record, otherwise they will contain a zero.

So if you are trying to put the two matching amounts (Pay and Benefit) on the same line in a table, you can't have anything that differentiates the separate rows (i.e. sub account number). So in many cases you can't ;put the matching sets of data on the same row in a table, one will always be a zero.

However, you can create a formula from the information of the pay line to calculate the benefit.

    1. Modify your data provider (query) to only include pay records (you will calc the benefit amount). You can leave the benefit records in initial to verify the calculated amounts, but don't forget to remove them later.

    2. Make sure the following dimension objects are in your data provider results objects:

  • [Benefit Level Cd (PAR)]
  • [Pay or Benefit Amt] (or alternately [Pay Amt] change the formula below to match)
  • [Benefit Rate]

    3. Create a variable with the formula:

  • =If ( [Benefit Level Cd (PAR)] ="0" ) Then 0 Else Round( [Pay or Benefit Amt] * [Benefit Rate] ;2)

    4. Add the new variable to the table with your data.

    5. Also consider adding a condition to your query to not pull imputed income (non-cash, but taxes items)

  • [Hrs Indicated Cd] not equal to "N"

Keywords:BO Formula Variable   Doc ID:29244
Owner:James I.Group:University of Chicago
Created:2013-03-27 12:00 CSTUpdated:2016-01-19 13:25 CST
Sites:University of Chicago, University of Chicago - Sandbox
Feedback:  0   0