One of my customers has a report that he can run for one Sales Rep or all. He now wants the Sales Reps to run the report themselves, but doesn’t want them to be able to run each other’s reports. He wanted the report to be restricted based on the user’s windows login. The term for this is row-level security, because certain users are allowed to access only certain rows of data.

There are a few different ways to do this. Unfortunately, none of them would work in this environment:

1) You can set this up in some databases, but this client doesn’t have access to the database to make changes.
2) You can set this up in some web portals and viewers, but this customer runs all of their reports from an integrated viewer built into their software application.
3) There are even ways to do this using formula functions, but because their integrated viewer is installed at the client level it would have been difficult to deploy and maintain the required dll’s.

So I suggested a method that I will call “poor man’s” row level security, and that is the option the customer chose. Each of his sales reps and their managers got to pick a password specific for this one report. Each time they run the report from the integrated viewer they select their name in the original parameter and enter their password in a new parameter. These values are checked in a formula called {@criteria} that is part of the report’s selection formula. The {@criteria} formula looks something like this:

if {?User} = “Tom”  and  {?pw} = ‘cats’ then true else
if {?User} = “Kelly” and {?pw} = ‘dogs’ then {rep} = [‘Joe’, ‘Sam’] else
if {?User} = “Joe”   and   {?pw} = ‘bugs’ then {rep} = ‘Joe’ else
if {?User} = “Sam”  and  {?pw} = ‘mice’ then {rep} = ‘Sam’ else
false

Tom with his password sees all Sales Reps. Kelly with her password sees the two reps that she supervises. Joe and Sam only have access to their own data.  The selection formula ends with:

……. and {@criteria}

This may not seem very secure but is protected by two factors in their environment.

1. The users don’t have a copy of Crystal
2. The connection to the database is made only from within the application.