Variable Filter

building semantic layer
1

I have a little puzzle that I can’t seem to solve. Thought I would pass it by the “experts”.

I have two tables. One table contains my customer activity, the other, which I’ll refer to as the Security table, contains BO ID and which customers info that BO User can see. By joining the two together and screening on the BO User ID via @variable(‘BOUSER’) I can produce a report that only shows the data for that BO User.

The problem is that not all BO Users are restricted as to what data they can see. I would like to create a filter that would apply if the BO User is in the Security table, and would not filter anything if the User is not in the Security table.

I need to generate conditional logic in the SQL Where clause of the report request, but SQL doesn’t lend it self well to conditional logic.

Please don’t suggest that I use the Broadcast Agent Report Bursting/Rows Restrictions feature. Due to other concerns that I won’t go into, we have decided that the best way for us to implement this feature is either in the Universe design, or the report definition.

TIA,

Shaun Funk

Listserv Archives (BOB member since 2002-06-25)

2

I don’t know if you can do something like that in native B.O. One way you can do this is to envoke a Visual Basic application. This isn’t too difficult and should take about 1 day to implement. About 1 ‡ days if you are unfamiliar with VB.

good luck
joe

Shaun Funk shaun.funk@SLKP.COM 01/26/00 11:14AM >>>
I have a little puzzle that I can’t seem to solve. Thought I would pass it by the “experts”.

I have two tables. One table contains my customer activity, the other, which I’ll refer to as the Security table, contains BO ID and which customers info that BO User can see. By joining the two together and screening on the BO User ID via @variable(‘BOUSER’) I can produce a report that only shows the data for that BO User.

The problem is that not all BO Users are restricted as to what data they can see. I would like to create a filter that would apply if the BO User is in the Security table, and would not filter anything if the User is not in the Security table.

I need to generate conditional logic in the SQL Where clause of the report request, but SQL doesn’t lend it self well to conditional logic.

Please don’t suggest that I use the Broadcast Agent Report Bursting/Rows Restrictions feature. Due to other concerns that I won’t go into, we have decided that the best way for us to implement this feature is either in the Universe design, or the report definition.

TIA,

Shaun Funk

Listserv Archives (BOB member since 2002-06-25)

3

At 11:14 AM 1/26/2000 -0500, Shaun Funk wrote:

I have two tables. One table contains my customer activity, the other, which I'll refer to as the Security table, contains BO ID and which customers info that BO User can see. By joining the two together and screening on the BO User ID via @variable('BOUSER') I can produce a report that only shows the data for that BO User.

The problem is that not all BO Users are restricted as to what data they can see. I would like to create a filter that would apply if the BO User is in the Security table, and would not filter anything if the User is not in the Security table.

I need to generate conditional logic in the SQL Where clause of the report request, but SQL doesn’t lend it self well to conditional logic.

Shaun, we have a similar requirement. We handle it by having one non-restricted view of the table (DMT_1990 in the example below), and a second row-restricted view of the table that incorporates the security join (School_DMT_1990 in the example below). The columns of the row-restricted view have the identical column names. For example (we use Sybase, and restrict based on their sybase login name, which we make identical to their BusObj login):

create view School_DMT_1990
as
select base.*

from dss…DMT_1990 base,
dss…security_org sec

where base.org_id = sec.org_id
and sec.user_name = user_name()

We build the universe on the non-row-restricted view (call it Table 1), and include the row-restricted view (call it Table 2) on the universe desktop (not joined to anything, nor used in any of the objects).

In Supervisor, we have two major groups, under which all of our user groups are defined. The first group (call it “A”) has the non-row-restricted users. The second (call it “B”) has the row-restricted users. For the row-restricted users, we go to the Universe Tab, select the universe, and use Table Mapping to substitute the row-restricted view in place of the non-restricted table.

So, for that universe, users in group “A” get the original universe, with Table 1 as its central fact table. Users in group “B” get a modified universe, which has Table 2 (with its security join restrictions), as its central fact table.

Hope this helps,
Anita Craig
Stanford University

Listserv Archives (BOB member since 2002-06-25)

4

What about using the feature in Security for the row restriction? For each Universe you can specify what where clause to use on which table. You can do this for each BO user or for the User’s Group. Simon

Listserv Archives (BOB member since 2002-06-25)