So, I cannot get it to work the way I need it to work.
I am attaching a screen shot.  I have 3 types of employees and I am showing Planned, Actual, and Variance for the headcount.  I need to add a third column to show the Net for each of them.  The Sum options under the Data tab doesn't produce the results I need (it's summing everything).  The formula tab options are grayed out once I have columns grouped.  If I remove the grouping I can't get the correct values to actually be pulled in, and I even tried actually creating all of the columns using the formula =if(HC Type = "BSC Emplyee......) instead of pulling them in from the Ad Hoc Query, but it's not pulling the values.
So, if anybody has any wonderful ideas on how to get Net for these columns that would be wonderful.

@Rhonda Keller

Hi Gordana, 

Could you remove "Employee Type" from the "Columns" section of Ad-Hoc query.

Then, if you go to Data tab, and include a Formula column which calculates the difference between "Internal headcount" and "Planned Headcount"

Then, drag the "Employee Type" into Columns.

Let me know how that goes.

Varghese

I have tried that and it's not working. It doesn't pull the values.

Hi Gordana,

In the Formula, for the Formula column ,refer to the columns as "Table.column", ie

Variance =Labor Master Data. Internal Headcount - Labor Master Data.Planned Headcount

This is what I have done in the sandbox.After doing that, drag "Employee Type" to the "Columns" area.

Thank you.  This doesn't give me what I need to do.  The issue that I have is that I need to show the Net column.

So, I need to show BSC Employee with all the values (planned, internal and variance), Contingent Emplyee and LOA.

Then the 4th group would be Net that displays net of planned, internal and variance.  However, once I add the headcount type to group you can't add formula column anymore. And without having the headcount type, I can't get the values to do the net.  I have tried creating formula columns for each headcount type (i.e. =if (Labor Master Data.HC Type = "BSC Employee", Labor Master Data.Planned Headcount,0) etc ) but that is not bringing in any values for me, just 0.

Hi Gordana,

Okay. Now I understand the requirement. Yes, once you add in that, it will not work. Have you considered changing the format of the report ?

Below is a screenshot: In this example, I used "Position" instead of "Employee Type". I used the Sub-total option to give me the Totals of each Position, spanning across Cost Centers 

Let me know what you think.

Varghese

You may want to try adding the conditional formula columns directly in the Labor Master Data table itself.

For example:

BSC Planned HC = If(Employee Type = "BSC Employee", Planned Headcount, 0)

BSC Internal HC = If(Employee Type = "BSC Employee", Internal Headcount, 0)

BSC HC Variance = BSC Planned HC - BSC Internal HC

...and so on for Contingent and LOA employee types.

This solves the issue of the report table grouping rows in such a way which might be causing your custom inserted table columns (containing If() functions) to fail.

The example above assumes Planned Headcount and Internal Headcount are table column names, not metric names.