Help defining a variable

[boyhardy]

Hi guys, so let me start by saying I do not claim to be an expert at business objects so this might be a very simple fix! It might have also been answered somewhere else but I can't find it.

Basically, I'm working with HR / People data and in a very simple format, I've got 2 columns of data;

- employee ID numbers (used as the merge for a couple of queries)

- manager ID number (one of the 'merged in' fields)

What I want to do, is to add a column call 'is a manager?' which for each emp ID, would tell me if that employee ID is anywhere in the manager ID column (ie - telling me that they are a line manager).

For additional context and to help explain, in excel, you'd do either a =if(isna(vlookup(a2,B:B,1,0)),"No","Yes") or even =if(countif(B:B,A2)>0,"Yes","No").

Can anyone help?

[AlexGourdet]

Thank you for visiting SAP Community to get answers to your questions.

As you're looking to get most out of your community membership, please consider include a profile picture to increase user engagement & additional resources to your reference that can really benefit you:

• Profile https://developers.sap.com/tutorials/community-profile.html
• Tips for Questions: https://community.sap.com/resources/questions-and-answers
• Consider to take our Q&A tutorial at: https://developers.sap.com/tutorials/community-qa.html.

I hope you find this advice useful, and we're happy to have you as part of SAP Community!

All the best,

-Alex

[nscheaffer · Accepted solution]

You can do this by creating another query of your HR data; could be just Employee ID and Manager ID. Then merge Employee ID from your primary query to Manager ID from your second query. Next create a Var Manager Employee ID variable with a Qualification of "Detail", Associated dimension of "Employee ID", and a formula of Employee ID from your second query.

Now you can define a Var Is Manager variable to determine if an employee is a manager or not.

=If(IsNull([Var Manager Employee ID]); "No"; "Yes")

I pulled this sample data from GitHub. I used Convert CSV to SQL to convert it to useable SQL for free-hand SQL queries.

Noel

[boyhardy · Accepted solution]

This is amazing, thanks Noel! Although I'm having a bit of trouble. I'm getting 'Yes' for everybody...

I've report filtered down to just my team (I currently have no direct reports so should be a 'no' but my line manager has a few so should be 'yes').

I've pulled in the Var Manager Employee ID into the report too and it looks like it's bringing everyone back. Possibly I've done the merge wrong? Any thoughts?

Thanks Noel,

Lewis

[nscheaffer · Accepted solution]

Can you provide some screen shots of your queries and data blocking out sensitive or personally identifying information?

BTW, I love using Greenshot for taking screenshots and doing minor editing.

[boyhardy · Accepted solution]

Thanks again Noel.

So here's my queries, I've got 7 - probably don't need that many but wanted to make sure my base data doesn't filter out if we have any null values. The one I've highlighted here is the manager ID. So we've got employee ID (personal reference: People) and the managers ID (Personal Ref: Manager). Filters on current employees only, current reporting manager and current occupancies (not sure if I needed that but better to have and not need than need and not have!)

Then this is the variable, which is where I think I may have joined wrong or something;

If these are no good, let me know and I'll add more. I'm on an iMac so was trying to avoid spending a whole $2 on Greenshot but can if need be ha!

Thanks,

Lewis

[nscheaffer · Accepted solution]

Did you merge Personal Reference:People (Base Data) to Personal Ref:Manager (Manager Details)? To do so...

1. Select both objects
2. Right-click
3. Click "Merge"

Then you want to be sure to base your Var Manager Employee ID variable on the actual merged dimension, not either of the objects being merged.

Does that make a difference?

Noel

[boyhardy · Accepted solution]

Hi Noel, sorry I've taken so long to come back to you. I think that merge is where I'm going wrong - I'd merged Employee ID from both queries rather than merging Emp ID to Mgr ID.

However, when I'm trying to merge them, nothing is happening when I right click. Is this because I've got other queries merged to Emp ID as a merged field already? (Ie, I've got a list of base data, then I've got another query for internal Grade so that I don't drop blanks off).

[nscheaffer · Accepted solution]

Ok. Try adding Mgr ID to your exisitng Emp ID merge. Like this...

Noel

[boyhardy · Accepted solution]

Noel, you are a wizard amongst men, that's done it! Thank you so much for your support & if you ever find yourself in South Yorkshire, I owe you a coffee!