Jiwa

How can I restrict the data to show in a report to specific users? e.g. We have debtor groups for regions and want only some users to be able to see specific regions. I was considering check boxes in HR_CustomSetting but am not sure how to link this in a crystal report.

Well, if you create an empty formula called "Pass_User" in your report, it will get filled with the username of the user running the report at execution time. You can then leverage this in a number of ways:

1. As part of the record selection formula to limit the result set
2. As a parameter value to pass through to a stored procedure (which would then limit the result set it returns in some way)
3. As a lookup for a formula that hard-codes some suppression values

Thanks, Scott. I omitted to reply some time ago.

Good morning, Scott.
I have not managed to get this to work as planned and an other client has a similar requirement. I have created a custom field (which holds the SY_BranchID) in debtors. I want the data in the report to be based on the PASS_User, such that in the Crystal Record Selection we will have something like the following
IF {@PASS_User} = "Admin"
THEN
{usp_BB_InvoiceListing_01;1.BranchID} = "7a229196f28249f797ca"
ELSE
IF {@PASS_User} = "AaronP"
THEN {usp_BB_InvoiceListing_01;1.DB_Classification} = "9a9c17ecb477450d8288"
ELSE
{usp_BB_InvoiceListing_01;1.DB_Classification} = 'ZZZZZZZZZZ0000000000'
//OR
//{usp_BB_InvoiceListing_01;1.DB_Classification} = '7a229196f28249f797ca'
OR
{usp_BB_InvoiceListing_01;1.DB_Classification} = '1fc7657fa21b443b8b6b'
OR
{usp_BB_InvoiceListing_01;1.DB_Classification} = '4fbedf4b311449eeb898'

I am not getting any meaningful data with various logins.

Can you assist, please?

Demo report and sp are attached.

I reckon your problems are caused by lack of brackets. Try this selection formula:

Code: Select all

IF {@PASS_User} = "Admin" THEN
(
    {usp_BB_InvoiceListing_01;1.BranchID} = "7a229196f28249f797ca"
)
ELSE
(
    IF {@PASS_User} = "AaronP" THEN
    (
        {usp_BB_InvoiceListing_01;1.DB_Classification} = "9a9c17ecb477450d8288"
    )
    ELSE
    (
        {usp_BB_InvoiceListing_01;1.DB_Classification} = 'ZZZZZZZZZZ0000000000' OR {usp_BB_InvoiceListing_01;1.DB_Classification} = '1fc7657fa21b443b8b6b' OR {usp_BB_InvoiceListing_01;1.DB_Classification} = '4fbedf4b311449eeb898'
    )
)

and also make sure you create a formula called "Pass_User" in the report!

Thanks, Scott.
Have tried that and it fails to deliver any records. The selection also fails if I use a CASE statement. I don't think it is a syntax issue.
With no restrictions in the record selection, as expected I get all the data delivered from the sp.
Any selection results in a report with no records. The sp is evidently running but Crystal removes the results.
Is there an alternative approach that could be used. I suspect the requirement to limit the data based on the user is not unique.

Well here is my test report and test stored procedure (both based upon yours). I run it in demo data. It seems to be doing as I expect based on the selection formula, and the hard-coded data I return from the stored procedure:

Hi Scott.
Thanks. All is working just nicely, now.
However, using the CASE tidies up the selection

Cheers
John I