Skip to Content
0
Jun 11, 2020 at 09:16 AM

Querying current user in Universal Function

147 Views

Hello

I'm trying to make a list of open Delivery Notes based on the logged in user. I'd like to put this in a SQL Report Grid widget on the dashboard but I'm not able to query the user this way.

The following query works fine, but I'd like SlpCode to be the current logged in user:

SELECT T0.DocNum AS DeliveryNote, T0.DocDate AS 'Posted On', 
Convert(numeric, GetDate()) - convert(numeric, T0.DocDate ) as 'Invoicing delay in days',
T0.CardName AS Customer, T0.Comments AS Remarks, ISNULL(T0.NumAtCard, 0) AS CustomerRef FROM ODLN T0
INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry WHERE T0.CANCELED = 'N'
AND T0.DocStatus = 'O'
AND T0.SlpCode = '3'

Alternatively, I've tried to use

AND T0.SlpCode = [%0]

and defining [%0] as %0[OSLP|Sales employee]; using the SQL Variables wizard but I'm met with an error when I load it on the dashboard. This query works when running it though the Query Manager.

I'm building custom dashboards for the whole team so it would be really nice to be able to base the queries on the user but I've not been able to do this successfully.

Thanks in advance!