Data hiding

[rnd]

I am creating reports for a company that has number of branches. The company don't want one branch to see another branch's data on the report. Is there any way to filter the data through the users' windows-login-id or mail-box-id so that if Branch 1 runs the report it can see data (records) pertaining to Branch 1 only and not Branch 2, 3, 4 etc..
DB : SQL Server 2000
CD : 8.5
Please answer.
Thanks.

 

[paulmarr]

rnd,

As far as I know - it is not possible just using Crystal Reports. It is possible, however, by viewing the report through Crystal Enterprise - which supports this feature using the users NT logon username and password. Based on this information - the record selection is dynamically passed to the report as a parameter.

Hope this helps


paulmarr

 

[ColinHuggett]

How about defining "views" at the SQL Server table level? The view could be defined based on the value of the "branch" column.
Then you could use "set location" in your Crystal report to read data from the "branch" view of the table rather than the entire table.
You'd end up with one view & one copy of the report, per branch, but, it has the desired result.

 

[chelseatech]

The big danger is that Branch 1 and Branch 2 can both see their reports by entering their branch number, but can also see each others data by entering the other branch number.

So you add some security to the report. You will need an additional or spare field in the Branch table to store a PIN number or password. Then as well as a paramater for the branch number you also have one for PIN or password.
For Branch 2 to read Branch 1's data, they will need to know both the branch number AND password. Otherwise they get no data in their report.

This technique works on the standard version of Enterprise. The NT security hint mentioned above requires the Pro version and lots of $$$$.
Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[dgillz]

You can prompt for a password with parameter fields. Hard code the passwords with a variable, and if the password entered in the parameter field does not match your variables, select no records for the report. If the password is a valid branch1 password, then select only branch1 records, if it is a branch2 password, select only branch2 records, etc. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[kenhamady]

rnd

If the user is using CR as a standalone tool, the user can undo whatever security you implement.

If you are using an 'off the shelf' client based viewer program, like CRWReporting or the others listed on my "Links" page, then you are relying on the user reentering their ID information, or putting in a password field as described above.

If you decide to use Crystal Enterprise, you would have to upgrade it to Professional, since the free "standard" version doesn't have any user-level security.

If you are you writing an application to launch the reports - ie VB or ASP, you should be able to capture the user's info, and adjust the query accordingly. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Ngolem]

A branch password is the only solution I can think of. I recall this discussion in a contract I had long ago. the company wanted to give distributors of a company access to their data but didn't want one distributor to have access to another's data since they were often competitors. The password setup which restricted the values that were returned was the only solution at that time.

But this was a maintenance nightmare....since distributor sales territories floated back and forth. So past histories were a problem when an account changed hands. Jim Broadbent

 

[rnd]

Thanks a lot guys. All those replies are very very very quick and valuable. I am truly amazed. Those suggestions are really valid and useful. Thanks again.
Actually we are at present handling the situation through a web application in VB.Net where each branch data access is limited to their Branch Code that is tied to their login id. I wanted to know that I am unaware of any possibilities in CR to do the same security. As Ken mentioned probably CE(Prof) would be the only way to go in future for this kind of security needs locally within Crystal.

Thanks - once again.

rnd