Trying to combine pay elements

LTalbott2

Hi

I need to do a report that provides a total of a calculated field based on a specific pay element, and have had to strip this PE out of the input values of the other PE's because the other PE's were showing on a separate row in a pivot table. I've done this by only showing the calculated field and not the input values of the other PE's, but now I need to add in the other PE's in pivot table format so all the recurring PE's attached to a member display on one row.

I think I might have to do a union query but when I've tried to do this, it errors. It also errors in my very basic attempt to do it via logical SQL.

Does anyone have any ideas? I've attached the xml's of both queries showing the calc field (bonus entitlement value) in Summary 1 and I want to add the other PE (car all) in Summary 2 to Summary 1 if that makes sense.

Could you please offer your expertise?

Summary 1.docx

Summary 2.docx

gclampitt

hi could you share a screen shot of the current output, what the issue is with it and then say an excel table of what your desired output is?

It sounds like you only want to display input values for specifc elements?

LT2018

Hi I've attached an excel file of what the summary and drill down are currently doing. I've given you the full report as the one yesterday was a test/limited data.

The summary drills down on selecting the People Manager Name and is correct in the drill down. However, I can't get the Bonus Entitlement Value column to add up in the pivot table (its not a measure) - whereas the bonus element and car allowance (the pay element names/input values) add up.

Ideally, I don't want the Bonus Entitlement % input value to add up but I can live with that as its part of the pay element measures.

The summary, however, calculates the Bonus Entitlement Value correctly - same as the drill down, but the total in the summary is not adding up correctly. So its the total on the summary that is incorrect for some reason and it would be good to get the same field on the drill down to add up as well.

Thank you.

Pay Elements in Summary not totalling correctly.xlsx

gclampitt

hi
i've used your XML and just changed it so it works with our elements/input values and the totals are all correct, so not sure why its not for you.

In your pivot table, try changing the aggregation rule to a different option (eg server complex etc) and see if thats fixes it.
Other suggestion would be to only include columns that you need (so don't need to "exclude" anything) and then maybe the default table view might work.
I don't think you can control which measures sum up via the gui - it will do all of them - Not sure if manually amending the XML might be an option

LTalbott2

Hi

Its still not totalling up so not sure what you did you fix it. I tried the server complex aggregate in the formula but it didn't total up in the pivot table - and in the pivot table design there is no aggregate option on that field.

I think the issue is that both the salary and Bonus Entitlement Value fields are not giving me the option to add a total in the pivot table. I would expect to see totals then I can set the pivot table to sum after the field

It doesn't work when I take out most of the fields either.

Can you send me your xml then I can check if its something in the config here that's causing issues. I notice when I play around with the Bonus Entitlement Value field and make that an attribute column it enables the Actual Salary field to have a total option.

Which seems bizarre as the Actual salary is the Oracle measure from Compensation - Salary Details Real Time. However, the Bonus Entitlement Value is using this measure to calculate the amount.

I'm a bit stumped - especially as you got it working!

gclampitt

Hello, i didn't save my query….
Just recreated and the Bonus total was incorrect this time

So changed the aggreation from "default" to "sum" and its now correct

XML attached

element_xml.txt

LTalbott2

Hello Thank you for sending me the xml. This works for me too but when I add the other pay elements in, the table view works but the pivot doesn't.

It needs to be a pivot I think so the other pay elements automatically go across as element names rather than in a table with duplicate rows per employee.

When I add the pay elements the pivot summarises like this

So, the annualized salary and bonus entitlement value are sum in the aggregation in the formula (although they don't give a total option in the pivot because they aren't measures)

The annualized salary total is correct here but the bonus entitlement should be 435,349.781 (finished report will be 2 decs). It doesn't add up correctly if there is another pay element (so for this example, it is car allowance).

The couple of options I have (I think) are to either create a union query but I don't think that would work (I've tried this but am not very good with unions), or to create a separate report on the dashboard to show the salary and bonus entitlement (which would then add up correctly) - then another report for the other pay elements.

gclampitt

hi yeah sorry not sure what issue is with your data then.

A union could be the answer.

So if your current query is working - just do a union and combine it with the same dimensions/measures for the other elements and see if that gives you the answer…

If there is say a dimension in one union that isn't present in the other, just use say "null" with the same data type.

Eg if Character "Cast (null as char)