Could you extend your CASE WHEN statement to include a second check whether the username is in a list of usernames?
For example (I dont know the exact SAP table/column names):
100.0*AVG(CASE WHEN _CEL_P2P_ACTIVITIES.USER_TYPE IN (<%=auto_user_type%>)
THEN 1.0
WHEN "_CEL_P2P_ACTIVITIES"."USERNAME" IN (<%=auto_user_name%> THEN 1.0
ELSE 0.0 END)
Bonus suggestion: you dont need the 100.0* if you set the display format to % (with decimals).
Hope this helps!
Thanks for the fast feedback! However, the extension shall not be based on user names but on a combinantion of PR Creation Indicator (B) and on PO Document Type (EP). Ive tried to update the formula as follows but always receive the error message that tables _CEL_P2P_ACTIVITIES and EBAN do not have a common parent in the schema:
100.0*AVG(
CASE
WHEN _CEL_P2P_ACTIVITIES.ACTIVITY_EN NOT IN (<%=ActivitiesUsedInAutomationRate%>) THEN NULL
WHEN _CEL_P2P_ACTIVITIES.ACTIVITY_EN IN (<%= ActivitiesUsedInAutomationRate %>) AND _CEL_P2P_ACTIVITIES.USER_TYPE IN (<%=auto_user_type%>)
THEN 1.0
WHEN EKKO.BSART = EP AND EBAN.ESTKZ = B
THEN 1.0
ELSE 0.0
END
)
Any idea/suggestion how to solve?
Thanks, Oli
Hey Oli,
I think this problem is connected to the other question I just answered.
To solve your problem, it probably should help to put the EBAN part of your statement into a Pull Function, most likely PU_COUNT should be used so here it goes:
100.0*AVG(
CASE
WHEN _CEL_P2P_ACTIVITIES.ACTIVITY_EN NOT IN (<%=ActivitiesUsedInAutomationRate%>) THEN NULL
WHEN _CEL_P2P_ACTIVITIES.ACTIVITY_EN IN (<%= ActivitiesUsedInAutomationRate %>) AND _CEL_P2P_ACTIVITIES.USER_TYPE IN (<%=auto_user_type%>)
THEN 1.0
WHEN EKKO.BSART = EP AND PU_COUNT(EKPO, EBAN.ESTKZ, EBAN.ESTKZ =
> 0
THEN 1.0
ELSE 0.0
END
)
Best Regards,
Benedict Lang
Great, thanks so much! That solves the problem indeed
