Restrict data in a report based on the user logged in  Topic is solved

Support for Crystal Reports within Jiwa.

Restrict data in a report based on the user logged in

Postby Atronics » Mon May 25, 2020 2:52 pm

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.
Atronics
Frequent Contributor
Frequent Contributor
 
Posts: 119
Joined: Fri Feb 29, 2008 4:40 pm
Topics Solved: 10

Re: Restrict data in a report based on the user logged in

Postby Scott.Pearce » Mon May 25, 2020 3:57 pm

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
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221

Re: Restrict data in a report based on the user logged in

Postby Atronics » Thu Sep 10, 2020 1:05 pm

Thanks, Scott. I omitted to reply some time ago.
Atronics
Frequent Contributor
Frequent Contributor
 
Posts: 119
Joined: Fri Feb 29, 2008 4:40 pm
Topics Solved: 10

Re: Restrict data in a report based on the user logged in

Postby Atronics » Thu Jul 21, 2022 10:34 am

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.
Attachments
Report1.zip
Sample report and sp
(21.24 KiB) Downloaded 276 times
Atronics
Frequent Contributor
Frequent Contributor
 
Posts: 119
Joined: Fri Feb 29, 2008 4:40 pm
Topics Solved: 10

Re: Restrict data in a report based on the user logged in

Postby Scott.Pearce » Thu Jul 21, 2022 11:04 am

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!
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221

Re: Restrict data in a report based on the user logged in

Postby Atronics » Thu Jul 21, 2022 12:10 pm

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.
Atronics
Frequent Contributor
Frequent Contributor
 
Posts: 119
Joined: Fri Feb 29, 2008 4:40 pm
Topics Solved: 10

Re: Restrict data in a report based on the user logged in

Postby Scott.Pearce » Thu Jul 21, 2022 12:55 pm

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:

Data per user test.rpt
Report
(64 KiB) Downloaded 298 times


usp_BB_InvoiceListing_01.sql
Stored Procedure
(6.74 KiB) Downloaded 285 times
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221

Re: Restrict data in a report based on the user logged in  Topic is solved

Postby Atronics » Thu Jul 21, 2022 2:47 pm

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

Cheers
John I
Atronics
Frequent Contributor
Frequent Contributor
 
Posts: 119
Joined: Fri Feb 29, 2008 4:40 pm
Topics Solved: 10


Return to Crystal Reports

Who is online

Users browsing this forum: No registered users and 1 guest

cron