Need BIRT reports to filter results by user's department

Document ID : KB000111598
Last Modified Date : 17/08/2018
Show Technical Document Details
Issue:
We have a requirement to produce reports that span all projects for a specific department.  The way we implemented this was to make use of the project's "Note" field (HARENVIRONMENT.NOTE) to store the department for each project.  For example, projects for Department A were updated to have "Dept_A" in the HARENVIRONMENT.NOTE field.  Projects for Department B would follow a similar pattern, having "Dept_B" in the HARENVIRONMENT.NOTE field, and so on.

We could then create a "Department A Packages Report" that included the following in the where clause:
WHERE HARENVIRONMENT.NOTE = 'Dept_A' ...

The "Packages Report" for every other department would include the same SQL select statement, and the WHERE clause would follow the same pattern.

The problem with this solution is that all reports for all departments are included in the list of available BIRT reports, and any user with permission to run reports can run a report for any department.  This led to a security concern, since users from any single department should not be able to see any information for the other departments. We need to adjust the report's SQL query so that the user running the report can only see data for their specific department.

Environment:
CA Harvest SCM all versions and platforms
Resolution:
The solution we found was to update the "Note" field for each user (HARUSER.NOTE) to include the department for that user.  For example, users in Department A were updated to have "Dept_A" in the note field (HARUSER.NOTE) and so on.

We then deleted all the individual department-level BIRT reports (Department A Packages Report, Department B Packages Report, etc) and replaced those with a single "Packages Report".  The SQL for this new report is the same as it was for the individual department-level reports, but includes the following in the WHERE clause:

WHERE HARENVIRONMENT.NOTE = (SELECT NOTE FROM HARUSER WHERE USROBJID = ${USER_ID})