Report Field Value result using an inlist of a query

building reports webintelligence
1

Hi all

This is for a download of employee information that has already been set up i am just trying to create an additional column (IsManger) that will tell me from the data if a user manages another member of staff.

I am trying to get the following results in a report for IsManager field

Table Employees
UserId, ManagerId, IsManager
1 Yes
2 1 Yes
3 2 No

The Sql Equivelent would be:

SELECT
UserId,
ManagerId,
CASE
WHEN UserId IN (SELECT ManagerId FROM data) THEN ‘Yes’
ELSE ‘No’
END AS IsManager
FROM data;

I have tried using InList and generate dynamically from a query1 into a variable that could be used in the report to get something like
=If(UserId InList(“1”;“2”) Then “Yes” Else “No”) but could not get vany values into the main report.

I have not done any BO before but used SSRS, not understanding how you use sub queries to work with the main report data.

Any suggestions welcome

2

Hi Paulkid.

I think the best should be to build a self-join on Universe table you are using. Once this is done, then you can mapping a new object dimension, using a case/if else as you need. I don’t know what is your database, but you have to use the sql sintax for your database type. After publishing your new version of the universe, use the new dimension to determine what you need in Webi.