problem with dashboard report controls

[jeanjenkins]

 I'm having a problem adding a report that uses multi-select input controls on a dashboard.  I can add a report that uses multi-select input controls, when the input controls do not limit the rows to only those the user has access to.  However, when add a report that uses multi-select input controls that limit the user to their authorized rows, I get an error: Error executing SQL statement for:region_multi_select.

 

I have no problem adding a report that uses the following input controls.  These controls do not limit the user to their authorized rows.

1. regionnorowlevelsecurity:

select distinct region from aggregatecomment

where region IN ($P!{LoggedInUserAttribute_Regions}) 

order by region

2. marketnorowlevelsecurity:

select distinct market  from aggregatecomment

where $X{IN, region, regionnorowlevelsecurity}

order by market

3. storenorowlevelsecurity:

select store from aggregatecomment

where $X{IN, region, regionnorowlevelsecurity} and 

$X{IN, market, marketnorowlevelsecurity} order by store

Here is the report query:

select region, market, store

from aggregatecomment

where $X{IN, region, regionnorowlevelsecurity}

and $X{IN, market, marketnorowlevelsecurity}

and $X{IN, store, storenorowlevelsecurity}

order by region, market, store

 

However, when I use multi-select input controls that limit the rows to those the user is authorized for, I get an error:  Error executing SQL statement for:region_multi_select.  The report runs fine outside the dashboard.  Here are the input controls:

1. region_multi_select:

select distinct region from aggregatecomment

where region IN ($P!{LoggedInUserAttribute_Regions}) 

order by region

2. market_multi_select:

select distinct market from aggregatecomment

where $X{IN, region, region_multi_select}

and market IN ($P!{LoggedInUserAttribute_Markets})

order by market

3. store_multi_select:

select store from aggregatecomment

where $X{IN, region, region_multi_select} 

and $X{IN, market, market_multi_select} 

and store IN ($P!{LoggedInUserAttribute_Stores}) 

order by store

Here is the report query, which works fine as a report:

select guest_cmnt_typ_ct, region, market, store

from aggregatecomment

where guest_cmnt_typ_ct = $P{comment_type}

and $X{IN, region, region_multi_select}

and $X{IN, market, market_multi_select}

and $X{IN, store, store_multi_select}

and store IN ($P!{LoggedInUserAttribute_Stores})

order by guest_cmnt_typ_ct, region, market, store

Thanks in advance.

 

[mdahlman]

jeanjenkins,

For readers with no background in your reports, that's a lot of reading. Is the very first query correct? It looks like you posted the wrong query.

Can you reduce the problem to a single input control? Is the problem with the input control query or with the report query? You're using cascading input controls... but is this critical to the problem or is that just an unrelated fact?

If you modify the report to simply display the contents of the input control without using it in a query does it work correctly?

Regards,
Matt

[jeanjenkins]

 Matt, thanks for the response.

It appears the problem is with the row level security condition.  I ran a couple tests using one multi-selection query input control.

 

A report that uses one multi-select input control, but does not check for authorized rows, runs fine and can be placed on a dashboard.

The multi-select input query for this report is: select distinct region from aggregatecomment order by region

 

A report that uses one multi-select input control, and does check for the user's authorized rows, runs fine on the server but cannot be placed on the dashboard.

The input query for this report is: select distinct region from aggregatecomment where region IN ($P!{LoggedInUserAttribute_Regions}) 

order by region

 

I am attaching the report queries. Thanks for your help.