Using @BOUSER to restrict customer list in Prompt usingLOV's

building semantic layer
1

Hello,

I’ve seen numerous posts related to LOV, @BOUSER and Row-Level security but none have exactly addressed my issue.

I need to use @variable(‘BOUSER’) in a LOV SQL definition in conjunction with a database table containing BOUserid and customer number to only show the Customers belonging to that user in a prompted LOV of a report.

When I set this up now, the prompt is showing Customer Number and it looks like in is considering BOUSER as a cascaded prompt. How do I get it to only show Customer Number in this scenario.

Thank you for any assistance!

tmcd :us: (BOB member since 2005-10-02)

2

The LoV SQL should only be returning a single column – the LoV value. It sounds like you have the ID in the SELECT too. Something like:


SELECT 
   boid,the_lov_value
FROM
   table
WHERE
   boid=@variable('BOUSER')

Remove the “boid” from the SELECT and it should work as expected.

Joe

joepeters :us: (BOB member since 2002-08-29)

3

Thanks Joe but here is my code:

SELECT DISTINCT
  vwDimCustomer.CustomerNumber
FROM
  vwDimCustomer,
  vwBOUserCustomerSecurityXREF
WHERE
  ( vwBOUserCustomerSecurityXREF.BOUserID = 
(
select CASE When cnt=0 then 'All' else @Variable('BOUSER') END 
from   
(select count(*) cnt from BOUserCustomerSecurityXREF where vwBOUserCustomerSecurityXREF.BOUserID = @Variable('BOUSER')) a)
  )
  AND  ( vwDimCustomer.CustomerNumber=vwBOUserCustomerSecurityXREF.CustomerNumber )

And here is what comes up in the prompt…

Note the BOUSER under Customer Number which causes LOV Refresh to come up blank.

Thanks
Prompt.png

tmcd :us: (BOB member since 2005-10-02)

4

I just created a test object, although with a simpler SQL, and it behaved as expected.

This thread is about a similar problem. The recommended solution is to not use modified SQL; I did not have the problem even though I used modified SQL, but it might be worth a try for you.

Joe

joepeters :us: (BOB member since 2002-08-29)

5

Thanks Joe.

Did your simplified SQL contain @variable(‘BOUSER’)?

tmcd :us: (BOB member since 2005-10-02)

6

Yes, I tried structuring the SQL a few different ways, even tried @variable vs. @Variable, but nothing I did reproduced your problem.

So it may be a bug in your version – we’re on XI3.1 FP3.1

Joe

joepeters :us: (BOB member since 2002-08-29)

7

Thanks for taking the time to assist. We are on an old XI R2 version and this does appear to be an intermittent bug. It worked before but no longer.

tmcd :us: (BOB member since 2005-10-02)

8

Oh, XIr2. This post mentions a similar problem in XIr2 that was identified as a bug.

joepeters :us: (BOB member since 2002-08-29)

9

why dont you create a security group, assign that user to the group, then use row level security and restrict on that group? It is never best practice to create security profiles for users.

Ruune :us: (BOB member since 2007-08-13)

10

Can that security group be used to restrict data in multiple LOV’s?

tmcd :us: (BOB member since 2005-10-02)

11

I believe so. Basically, it tacks a WHERE clause at the end of any query that any user or group that restriction is assigned to. So, when it does the SELECT DISTINCT statement for the LOV, the where clause is included.

One thing to keep in mind, is that if you are restricting only one table, you need to include that table in “additional tables” for each object that you want restricted, if it’s not directly part of that table.

for example- if you have a fact table, that has a column you’re using for row-level security, then a LOV on an object that’s based on another table, include the fact table in “extra tables” You can find this in the third line of the SQL definition in the objects properties (IDT).

Ruune :us: (BOB member since 2007-08-13)