Get current SAP Business one User as parameter for Crystal Report

[Former Member]

Hi All,

I trying to get Actual logged User in SAP Business One as a Parameter for Crystal Report to filter information base on user

I can get correct user in query in SAP Business One using this query

SELECT Convert(int,$[USER])

I Try to create a Token

User@SELECT Convert(int,$[USER])  And that not working

I try User@Select USERID, U_NAME From OUSR

and i get list of users, So Token is working but not in the combine with $[USER] parameter

Any idea how I can do it?

I Using:

Crystal Report 2013

SAP Business One 9.0 PL11

Thanks in advance

Yoni

[kothandaraman_nagarajan · Accepted solution]

Hi,

Please refer thread

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Interesting, that printbyusername@ parameter is working, but usercode@ not working

Still get prompt for the usercode@ parameter, the printbyusername@ work fine without the prompt

Any idea why is that?

I test it in other environment with the same result.

[kothandaraman_nagarajan · Accepted solution]

Try this query:

Declare @User Int

Declare @PriceList Int

Set @User = ( SELECT T0.UserID FROM OUSR T0 WHERE t0.internal_K= $[user])
Set @PriceList =

Isnull((Select ListNum

  from OUSR X0

  Inner Join OUDG X1 on X0.DfltsGroup = X1.Code

  Inner Join OCRD X2 on X2.CardCode = X1.ICTCard

  Where X0.USERID = @User),3)

Select T0.ItemCode, T0.ItemName,

  (Select OnHand from OITW Where WhsCode = '1' and ItemCode = T0.ItemCode) WHS,

  (Select OnHand from OITW Where WhsCode = '3' and ItemCode = T0.ItemCode) Office,

  T1.Price

from OITM T0

  Inner Join ITM1 T1 on T1.ItemCode = T0.ItemCode and T1.PriceList = @PriceList

Where OnHand > 0

[Former Member]

You're looking for the UserCode@ token.

[Former Member]

I try to use UserCode@ as a token (I see you mention it other tread), but it not working, I get choose from list and it block from choosing.

The idea is that the report auto filter base on this parameter. And I don’t need to choose any option.

Do you know if I need to install something that SAP B1 Token's will be recognize?

[Former Member]

No there shouldn't be anything extra to install. You say you're trying to get the logged in user to filter, but the UserCode@ token provides that.Can you explain a little more what you're trying to accomplish?

[Former Member]

I have a table that give me what price list the user can see when he create report with a list of items.

The SQL Query need the user that is actual logged to select the price list to display.

Declare @User Int

Declare @PriceList Int

Set @User = 0 <-- Here need to go the parameter (i try UserCode@)

Set @PriceList =

Isnull((Select ListNum

  from OUSR X0

  Inner Join OUDG X1 on X0.DfltsGroup = X1.Code

  Inner Join OCRD X2 on X2.CardCode = X1.ICTCard

  Where X0.USERID = @User),3)

Select T0.ItemCode, T0.ItemName,

  (Select OnHand from OITW Where WhsCode = '1' and ItemCode = T0.ItemCode) WHS,

  (Select OnHand from OITW Where WhsCode = '3' and ItemCode = T0.ItemCode) Office,

  T1.Price

from OITM T0

  Inner Join ITM1 T1 on T1.ItemCode = T0.ItemCode and T1.PriceList = @PriceList

Where OnHand > 0

[Former Member]

UserCode@ returns the account name, not the user ID. Try modifying your query to: Set @User = (SELECT USERID FROM OUSR WHERE USER_CODE = UserCode@)

[Former Member]

Thomas,

First thanks for the help

Just for the record the syntax that work is...

Set @User = (SELECT USERID FROM OUSR WHERE USER_CODE = '{?UserCode@}')

Otherwise you get error failed to convert text to int.

Still after using this token I get prompt in SAP to filed the the value, and there is no auto value added as the UserCode.

if I manually write the name of the user the report is running, so it means that if UserCode@ was working the report was needed to generate

So, still UserCode@ don't bring the value of the usercode, and the prompt is not required and still coming up.

[Former Member]

Thomas,

UserCode@ is case sensetive?