CODEWITHSUNDEEP
sapb1kpi
A.Galilea
A.Galilea

Reputation: 1

my custom KPIs doesnt filter by current user in SAP B1

i'm creating some kpi's and Advanced dashboards in SAP B1 9.2 with Fiori Cockpit. For that purpose, I have created some custom queries like this

SELECT T0."DocNum", T0."CANCELED", T0."DocStatus", T0."DocDate", T0."DocDueDate", T0."CardCode", T0."CardName", T0."DiscPrcnt", T0."DiscSum", T0."TotalExpns", (T0."DocTotal" - T0."VatSum" - T0."DiscSum") "Total con portes", T0."DocCur", T0."DocTotal", T0."SlpCode", T0."Confirmed" 
FROM ODLN T0
UNION ALL
SELECT T0."DocNum", T0."CANCELED", T0."DocStatus", T0."DocDate", T0."DocDueDate", T0."CardCode", T0."CardName", T0."DiscPrcnt", T0."DiscSum", T0."TotalExpns", (-T0."DocTotal" + T0."VatSum" + T0."DiscSum") "Total con portes", T0."DocCur", T0."DocTotal", T0."SlpCode", T0."Confirmed" 
FROM ORIN T0

I want to create a filter that shows only data from the current user (total amount of sales in this example), but it doesn't work. Tried to use ODLN."SlpCode", OHEM."Salesprson" and OHEM."EMPID" as base field for the filter. Searching the web for solutions I've found queries that filter as needed using views instead of tables, but I'm not sure where that data comes from and in some cases none of that views has all the fields I need. Seems that the views that works are stored in a schema named "_SYS_BIC".

So, Is it possible to filter by current user using tables? If not, how do i search SYS_BIC for a view with the data that I need?

UPDATE: I've tried to replicate a previous kpi that works and the copy doesn't work. With the same data source and settings of a kpi that is working perfectly... If I remove the current user filter the copied kpi shows the total value as expected, but with the current user filter the result is 0 no matter what user is using the kpi.

Thanks for your help/advices

Aitor

P.S: i know that i used ODLN (Deliveries) instead of OINV (Invoices) in the query, it is due to some weird practices of the accounting department that i have to do this way.

Upvotes: 0

Views: 905

Answers (1)

enshadowed_
enshadowed_

Reputation: 109

When you say current user i assume you want to log in as manager and have on the fiori dashboard the KPI for manager and when user1 logs in you want the KPI for user1.

Based on the above scenario you can try blending the following query in your existing query and pray it works. It does work with formatted search. I would use the internal_k to link it with the other user / employee related tables. 

    SELECT T0.U_NAME FROM OUSR T0 WHERE INTERNAL_K = $[USER]

Let us know how it works for you.

Upvotes: 0

Related Questions