Crystal Report - Sales order filtered by current logged in user

[gianluca_calloni]

HI to all...

A little suggestion.

I need to build a report for sales order retrieving data according the user currently logged in.

Example:

User A and User B can see only the sales order where they are owner and sales person.

User C can see all sales orders...

My idea is to start from

PrintByUserName@

parameter. From this point, through OUSR -  OHEM - OSLP tables , i can retrieve the slpcode to filter my report...

Any suggestion?

Thank's in advance

--LUCA

[gianluca_calloni]

Hi to all dear  SCN friends.

I post a blog post to explain how to filter data depending the logged in user in Crystal Report.

The link is

Thank's to all for help

--LUCA

[gianluca_calloni]

Mhhh

OK.

I think I find-out a solution.

If test is ok, i'll post a BLOGPOST with some C.R. configuration to share my solution.

Thank's to Gordon and Manish for suggestion and help

have a nice easter.

--LUCA

[gianluca_calloni]

Hi Manish...

Supposing i select the {?TipoCheck}= 1 and modifying the previous where condition in

if ({?TipoCheck}=1) then
(
    {ODLN.DocStatus} = "O" and
    {DLN1.ItemCode} <> "TR" and 
    {OUSR.U_NAME} = {?PrintByUserName@}
)

i'm able to filter the result (Delivery table in this case) based on current logged in user.

But i would like that:

USER C must be able to see all documents (SALES ORDER - Delivery - A/R INVOICE)

and find a standard solution to implement in all my report...

This is my "actual" goal...

Thank's for your time

--LUCA

[Former Member]

Hi Luca,

Try:

1. if ({?TipoCheck}=1 then 
2. if {OUSR.U_NAME}  != 'USER C')
3. ( 
4.     {ODLN.DocStatus} = "O" and 
5.     {DLN1.ItemCode} <> "TR" and  
6.     {OUSR.U_NAME} = {?PrintByUserName@} 
7. ) 
8. ELSE
9. ( 
10.     {ODLN.DocStatus} = "O" and 
11.     {DLN1.ItemCode} <> "TR"
12. )

Thanks,

Gordon

[gianluca_calloni]

Hi Gordon.

Thank you for replay.

I try your suggestion with some modification as:

stringVar array MasterUser := ["Manager","Fabio","Mirko","Luca"];

if ({?TipoCheck}=1) then
(
if {?PrintByUserName@} in MasterUser then
(
    {OUSR.U_NAME} like "*" and 
    {ODLN.DocStatus} = "O" and
    {DLN1.ItemCode} <> "TR"   
)
else
(
    {OUSR.U_NAME} = {?PrintByUserName@}
    {ODLN.DocStatus} = "O" and
    {DLN1.ItemCode} <> "TR"   
)

and work fine..

But for parameter named TipoCheck the user have 6 choices.

and with this structure, i must duplicate all the condition in each TipoCheck choice.

So i try another structure of where condition like:

stringVar array MasterUser := ["Manager","Fabio","Mirko","Luca"];
 
if {?PrintByUserName@} in MasterUser then
(
    {OUSR.U_NAME} like "*" 
)
else
(
    {OUSR.U_NAME} = {?PrintByUserName@}
)
 
and 
 
if ({?TipoCheck}=1) then
(
    {ODLN.DocStatus} = "O" and
    {DLN1.ItemCode} <> "TR"   
)
else if ({?TipoCheck} = 2) then
(
    {ODLN.DocStatus} = "O" and
    {DLN1.ItemCode} <> "TR" and
    ({DLN1.Price} > 0.00 and {DLN1.Price} <>{JDCR_SPP1_UltimiPrezziValidi.Price})
)
else if ({?TipoCheck}=3) then
(
    {ODLN.DocStatus} = "O" and
    {DLN1.ItemCode} <> "TR" and
    ({DLN1.Price} > 0.00 and {DLN1.Price} <>{JDCR_SPP1_UltimiPrezziValidi.Price})and 
    {ORDR.DocDate} in ({JDCR_SPP1_UltimiPrezziValidi.FromDate}) to  ({JDCR_SPP1_UltimiPrezziValidi.ToDate})
)
else if ({?TipoCheck} = 4) then
(
    {ODLN.DocStatus} = "O" and
    {DLN1.ItemCode} <> "TR" and
    {DLN1.Price} = 0.00 
)
else if ({?TipoCheck} = 5) then
(
    {ODLN.DocStatus} = "O" and
    {DLN1.ItemCode} <> "TR" and
    {ORDR.DocDate} > {JDCR_SPP1_UltimiPrezziValidi.ToDate} and 
    {DLN1.Price} > 0.00
)

but seems not recognising the TipoCheck selection... In other words the where condition work until row  12 in the upper example.

I would like to find a way to include "dinamically" the filter on user uotside all other condition dependig the user choice

[former_member184146]

Hi Luca,

try with ordr.slpcode

[gianluca_calloni]

Hi Manish.

Sure.. With ORDR.slpcode, the report is filtered by the current logged user.

But i'm looking for a solution for USER C which must be able to see all orders...

--LUCA

[former_member184146]

try with case

case when user<>'C' printbyusername@ordr.slpcode

when user='c' then by docnum end

[gianluca_calloni]

Sorry Manish..

I'm little bit confused..

Where i must use the case statement?

The paramenter PrintByUserName@ give me the OUSR.U_NAME field..

Through a join to ohem and oslp, i can filter report result by current logged user.

But the manager (USER C) must be able to see all orders...

Obviusly... The report have other parameters for filtering data...

--LUCA

[former_member184146]

can you post your where condition here to have a closer look into the issue???

[gianluca_calloni]

Sure manish..

Following the code...

if ({?TipoCheck}=1) then
(
    {ODLN.DocStatus} = "O" and
    {DLN1.ItemCode} <> "TR"
)
else if ({?TipoCheck} = 2) then
(
    {ODLN.DocStatus} = "O" and
    {DLN1.ItemCode} <> "TR" and
    ({DLN1.Price} > 0.00 and {DLN1.Price} <>{JDCR_SPP1_UltimiPrezziValidi.Price})
)
else if ({?TipoCheck}=3) then
(
    {ODLN.DocStatus} = "O" and
    {DLN1.ItemCode} <> "TR" and
    ({DLN1.Price} > 0.00 and {DLN1.Price} <>{JDCR_SPP1_UltimiPrezziValidi.Price})and 
    {ORDR.DocDate} in ({JDCR_SPP1_UltimiPrezziValidi.FromDate}) to  ({JDCR_SPP1_UltimiPrezziValidi.ToDate})
)
else if ({?TipoCheck} = 4) then
(
    {ODLN.DocStatus} = "O" and
    {DLN1.ItemCode} <> "TR" and
    {DLN1.Price} = 0.00 
)
else if ({?TipoCheck} = 5) then
(
    {ODLN.DocStatus} = "O" and
    {DLN1.ItemCode} <> "TR" and
    {ORDR.DocDate} > {JDCR_SPP1_UltimiPrezziValidi.ToDate} and 
    {DLN1.Price} > 0.00
)

Depending on user selection criteria...

[former_member184146]

Thanks Luca,

can you show the crystal formula by which you are filtering based on current logged in user??