Need help in writing a formula in webi 4.1

[Former Member]

Hi,

I need to identify the users who has a role (for ex: HHP)  in ECC but not in BW (same role). I have a 'role' column in the report which has all ECC and BW roles displayed in one column. How do I write a formula to get the desired result.  I appreciate any suggestions.

Thanks,

Charvi.

[Former Member · Accepted solution]

Hi Charvi,

I have done some workaround for you to display the Data Provider ( ECC or BW ) in different Color to identify the ROLE for which User ease.

Step 1: Merge Role from Query1 (TOBE) and Query2 (ASIS)

Step 2: Create a Measure Variable named "Max TOBE Value"

  = Max([TOBE].ANYOBJECT)

Step 3: Drag and place these Merged Column(Role) and MAx Value(Obj) into a Vertical Table

Step 4: Create a Measure Variable names "Query Name"

  =If IsNull([Max TOBE Value]) Then 1 Else 0

Step 5: Create a New Rule by (Menu=> Analysis -> Conditional -> New Rule)

Step 6: In Filtered Object or Cell select "Query Name"

  In Operator select "Equal"

  In Operands type "0"

  Click Format button below and type =Left(DataProvider([TOBE].ROLE);Pos(DataProvider([TOBE].ROLE);" -"))

Step 7: Create another Format by click Add Button at the left cornor

  In Filtered Object or Cell select "Query Name"

  In Operator select "Equal"

  In Operands type "1"

  Click Format button below and type =Left(DataProvider([ASIS].ROLE);Pos(DataProvider([ASIS].ROLE);" -"))

Step 8: Drag this Measure Variable and place it in the Table.

Step 9: Hide the "Max TOBE Value" Column

Now you can able to see from which DataProvider(In ECC / BW) we have the "ROLE" value after Merge also.

---Raji. S

[Former Member · Accepted solution]

Hi Raji,

Thank you for your time and Help. But I am still not sure if you all actually understood my question. I will give you more details. Please see the screen shot below.

In the below screen shot, user 'X' has 2 MSS roles in ECC and "HHP:MSS_HR_DIRECT_MANAGER" in BW. So, We are trying to
build a report of any users that currently have an ‘MSS’ role in ECC but do NOT
have the MSS role in BW. In this case, this user has a 'MSS' role in BW and I do not want this person to be displayed in the report. If this user 'X' has any one of these 2 ECC roles and didn't have 'HHP:MSS_HR_DIRECT_MANAGER" in BW, then I want this user to be displayed in the report. I hope you understand my requirement now. Any help is appreciated.

[Former Member · Accepted solution]

Hi Charvi,

Can you try this-

Create 2 variable at report-

Var1=Sum(If [Role]="HHP MSS_HR_DIRECT_MANAGER" And [Role_Source]="BW" Then 1 Else 0) In ([User])

Var2=Sum(If Pos([Role];"MSS")<>0 And [Role_Source]="ECC" Then 1 Else 0) In ([User])

Create Filter in Var1 and Var2 as below-

Var1 Equal to:0

Var2 Not Equal to: 0

Hope this help.

[Former Member · Accepted solution]

Hi Anuj,

I tried your formulas but getting the below results. Var 2 is bringing 1 & 2's regardless of MSS roles. Please let me know.

[Former Member · Accepted solution]

Hi Charvi,

Var1 should give 1 for all the user in your screenshot. Please check you are writing exactly same role for HHP:MMS_HR_DIRECT_MANAGER in the formula for the Var1.

According to your requirement you don't want to show any of the users which are in your screenshot.Right?

Please put a screenshot of Var1 and Var2 that you have created.

[Former Member · Accepted solution]

Hi Anuj,

My requirement is, I want to display the users who has MSS role in ECC but do not have MSS role in BW. Yes, that's right. I do not want to show any users in my screen shot as they have the MSS role in BW. In the below example, he has MSS role in ECC and BW and I do not want to see this guy.

var1: =Sum(If [Role]="HHP:MSS_HR_DIRECT_MANAGER" And [Role_Source]="BW" Then 1 Else 0) In ([User])

Var 2: =Sum(If Pos([Role];"MSS")<>0 And [Role_Source]="ECC" Then 1 Else 0) In ([User])

[Former Member · Accepted solution]

Hi Charvi,

Have you applied report level filter

Var1 Equal to:0

Var2 Not Equal to: 0

after applying above filter user in screenshot will not display in report.

[Former Member · Accepted solution]

Hi Anuj,

I did apply that filter. When I applied, it is not displaying any users (eventhough I have users who have MSS role in ECC but in BW, they are not displaying either.

Thanks,

Charvi.

[Former Member · Accepted solution]

Hi Charvi,

Let me tell you what I understood from your requirement. Correct me if I wrong-

1) Not to display any user user who have HHP MSS_HR_DIRECT_MANAGER role in BW. Doesn't matter what they have role in ECC.

2) If user have MSS role in ECC and do not have HHP MSS_HR_DIRECT_MANAGER role in BW then only display to report.

~Anuj

[amitrathi239 · Accepted solution]

Hi,

Follow below steps.

Create Var1==Substr(Right([Role];(Length([Role])-Pos([Role];":")));1;3)

Create Var 2==Sum(If [Var 1]="MSS" And [System]="BW" Then 1 Else 0) In ([User])

Create Var3==Sum(If([Var 2])<>0 And [System]="ECC" Then 1 Else 0) In ([User])

Apply the filter on where Var 3 equal to 0.

With above contions you only get the users where no MSS role in BW.

I have tested with dummy data and it is working .

Regrads,

Amit

[Former Member · Accepted solution]

Hi Amit,

Sorry for the delay in getting back to you. Did you merge 'User' dimension and 'Role' dimension? Because, I tried your formulas and somehow when I put 'Sum' infront of my formula, it is giving odd numbers instead of 0 and 1. But, I tried similarly (Please see the screen shot below).

Var 1: =Substr(Right([Role];(Length([Role])-Pos([Role];":")));1;3)

Var 2: =If [Var 1]="MSS" And [Role_Source] = "BW" Then 1 Else 0 In ([User])

Var 3: =If [Var 1]="MSS" And [Role_Source] = "ECC" Then 1 Else 0 In ([User])

Var 4: =Sum (If ([Var 3]>1 And [Var 2] <1) Then 1 Else 0). When I applied the filter on Var 4, I am getting none. Pease see the screen shot below (2nd one). Please let me know what I am missing?

[amitrathi239 · Accepted solution]

Hi,

Variable you have created are slightly different which i have suggested.You have to use Var 2 in the variable var 3 but you have used Var 1 in this.

use these steps:

Create Var1==Substr(Right([Role];(Length([Role])-Pos([Role];":")));1;3)

Create Var 2==Sum(If [Var 1]="MSS" And [Role_Source]="BW" Then 1 Else 0) In ([User])

Create Var3==Sum(If([Var 2])<>0 And [Role_Source]="ECC" Then 1 Else 0) In ([User])

Apply filter on Var 3 where Var 3 is not equal to 0. No need of to create Var 4.

Regards,

Amit

[Former Member · Accepted solution]

Hi Amit,

When I tried the formulas that you suggested above, I am getting the below results (Before applying the filter on Var 3. After I applied the filter, it is not displaying any users.

Var 2:=Sum (If [Var 1]="MSS" And [Role_Source] = "BW" Then 1 Else 0) In ([User])

Var 3: =Sum (If [Var 2]<>0 And [Role_Source] = "ECC" Then 1 Else 0) In ([User])

[amitrathi239 · Accepted solution]

Hi,

If i am not wrong then your requirement is to exclude users who are in BW and ECC with role MSS.

I have checked your screen shot and User A0G0SC9 has MSS in the both ECC and BW.If you applied the var 3 filter it will exclude this user.

share the screen shot of user role where this condition is not matching.

Amit

[Former Member · Accepted solution]

Hi Amit,

No, My requirement is to find the user who has MSS role in ECC but do not have it in BW. If that user has MSS role both in ECC and BW then I don't want to display that user.

However, I really appreciate all your time and effort in helping with the solution. I found solution myself.

Solution: I only merged the 'user' dimension from both queries and created an attribute for BW role and ECC role and have 3 colums 1. User 2. BW role and 3. ECC role.

I wrote an IF statement:
Users Missing BW-MSS Role=If [BW Role] = "HHP:MSS_HR_DIRECT_MANAGER" Then 1 Else 0
=If (Match([ECC Role] ; "HRP:MSS*" ) ;1;0 )

and filtered on the 'Users Missing BW-MSS Role' = 1, which is working as expected.

Thank you,

Charvi.

[Former Member]

Hi Charvi,

Please try the below steps and let me know the result..

Step 1: Merge [Role] and [User] Object's from ECC and BW System

Step 2: Create Measure Variable : Max Val

= Max([Total Sales Value])         //Any Measure Variable you have in BW Query

Step 3: Create Dimension Variable : Query Name

=If IsNull([Max Val]) Then "ECC" Else "BW"

Step 4: Create Dimension Variable : CheckString

=If(Match([Role];"MSS*");"MSS";"Nill")

Step 5: Create Measure Variable : CheckECC

=Sum(If [CheckString]="MSS" And [Query Name]="ECC" Then 1 Else 0) In ([User])

Step 6: Create Measure Variable : CheckBW

=Sum(If [CheckString]="MSS" And [Query Name]="BW" Then 1 Else 0) In ([User])

Step 7: Create Measure Variable : FinalValue

=Sum(If(([CheckECC])+[CheckBW]) <=1 Then 7 Else 9) In ([User])

Step 8: Add Filter to FinalValue Column like FinalValue inList 7

You will get the User you want to display in the Table....

----Raji. S

[Former Member]

Hi Raji,

I didn't understand the Step # 2. Can you help me understand?

Thanks,
Charvi.

[Former Member]

Create a variable named "Max Val" to get max() of any Measure Object you have in the BW query

[Former Member]

Thank you Raji.

[Former Member]

Hi Charvi,

Could you provide some sample data and some more detail about how output you want to see.

~Anuj

[Former Member]

Hi Anuj, I have provided more details below. please go through and let me know if you have any suggestions.

Thanks,

Charvi.

[Former Member]

Hi Charvi,

If you are getting data from ECC and BW from two seperate queries you can use the dimension from ECC in the column to get the roles from ECC only.

Please provide more details to assist further.

[Former Member]

Hi,

Sorry for the confusion. I have two seperate queries from ECC and BW but merged the dimension to get all the roles in one column. Now, I get all ECC and BW roles that a user has in one column 'Role'. But, now, I need to identify the user who has ECC roles for ex:

SRP:SS_DIRECT_MANAGER

SRP:SS_DIR_MGR_NOEX  but do not have the role "SHP:SS_DIRECT_MANAGER" in BW. Please let me know.

Thanks,

Charvi.

[Former Member]

In that case,the above solution I have provided should work.Try that out and let me know in case you still have the issue.

[Former Member]

This document might be of some help to get the issue resolved.

[Former Member]

Hi Charvi,

We can not identify DataProvider name from the merge dimension.

In your case Role is merge dimension and you want to distinguish which role is coming from ECC query and which is coming from BW query.

I would recommend to add one more object at universe/Bex to identify source of the Role.

~Anuj

[former_member201488]

I don't work with BW myself, so I don't know if this mechanism can assist?

HTH

NMG

[Former Member]

Hi Sriharsha,

That didn't help. Could you look at more details that I provided below for a better understanding of my requirement. If you could think of any solution, I really appreciate it.

Thanks,

Charvi.