Crystal Reports – Row Level Security

Recently I was asked to take a look at a slow running Crystal Report.  This report makes use of row-level security and only returns records the report executor has been granted permissions to see. I was told this report was originally migrated to production with adequate performance (~2-3 second range), but over the past month users have been complaining of a slowdown (~20 seconds).

I soon found the report query was not being passed the UserId of the report executor.  This caused more records than necessary to be returned to the report and forced Crystal to filter the records at the report level.  A Crystal filter was being performed on 100,000 records to get the necessary 500 records it needed for the report.

The obvious solution to this problem is to pass the id of the report user to the query and perform the filtering at the database level (in this case SQL Server 2005).  However I was surprised to learn that Crystal Reports (delivered via InfoView on Business Objects Enterprise XI 3.1) only provides the ability to retrieve the UserId via the Crystal variable after the report query has been executed:

CurrentCEUserName

Fortunately, this report makes use of a prompt screen delivered from a List of Values in a Business View.   And since this Business View allows for row-level filtering based on the user executing the prompt screen (via the report), I was able to tie those together.

Here is an Example recordset returned by query before the report change (returns ~100,000 records):

Inside Crystal, this recordset was filtered where:

recordset.UserId = CurrentCEUserName (returns ~500 records)

With this amount of data coming into the report, it was causing some high execution times (10s+) for Read Database Records and Format First Page.

This filtering inside Crystal Reports was provided via the Record Selection Formula:

<tablename>.user = CurrentCEUserName

The parameter field for the Crystal Report is coming from a dynamic list of values delivered from a Business View.   The Business View LOV is generating a drop down list containing a list of states accessible by the report user and displayed on the report’s prompt screen. I suspected because the selected value of that DDL was being passed to the query, there must be a way to take the username that’s being used in the business view to pass to the query.

Surely enough, the business view was not making use of the “value” attribute of a drop down list.  Instead, it was passing the state name as the value and description columns of the prompt.  When a user selected a value from the prompt option, the “value” attribute is returned as the Parameter then sent to the query accessible by {? Parameter Name}.

Example drop down list generated code:
<select id="DDL_States">
<option value="Oregon">Oregon</option>
<option value="California">California</option>
</select>

Here is what it looked like Edit Parameter dialog box:

To improve this, I modified the Business View and added a new column called “UserIdState”, which I concatenated together (with a “;” delimiter) in the SELECT statement.

Then I modified the List of Values object to use the UserIdState column as the list of values, with the State set as the Description.

Then when I used this LOV object in Crystal Reports, I was able to show the report user the same list of states applicable to him/her, but on the back-end have it generate code like this:

<select id="DDL_States">
<option value="EmployeeA;Oregon"> Oregon </option>
<option value="EmployeeA;California">California</option>
</select>

When the user selects a state, the value attribute gets passed as a parameter to the query (accessible via {?Parameter Name}), which is then parsed using a simple string parsing functions.

SELECT UserId, [State]
FROM tbl_Data
WHERE
UserId = LEFT( {?UserIdState}, CHARINDEX(';', {?UserIdState}) - 1)
AND
[State] = SUBSTRING({?UserIdState}, CHARINDEX(';', {?UserIdState}) + 1, LEN({?UserIdState}))


Note, when doing this it is important to set the following Value Options of the Dynamic Prompt in Crystal:

• Prompt with Description Only: TRUE
• Sort Order: Ascending by Description

The report now executes in about 1 second:

I hope this helps you in your Crystal development and speeds up some reports. If you have any questions email me.