Cell Level Security and Reporting Services.

hopefully, has figured out:

 

i've implemented , tested cell level security on cube. it's testing level conditions, , returns #n/a (as normal) when user not supposed see cell value. since use .formattedvalue in reports, works fine in report services (and excel , proclarity, etc.)

 

here's problem:

 

when rs parameters encounter situation, parameter dataset "breaks" (the following system error occurred: type mismatch.) happening, because parameter fields (parametervalue, parametercaption, parameterlevel) being replaced #n/a, due cell level security. happening, because these defined members, , hence passing through cell level security.

 

what need find way have these specific members bypass cell level security, parameter datasets still work. (failing that, new way of specifying parameters in msrs.)

 

i've tried following a cell level security rule, doesn't seem work:

[measures].currentmember [measures].[parametervalue] or [measures].currentmember [measures].[parametercaption] or
[measures].currentmember [measures].[parameterlevel] or
[measures].[is visible]

any ideas?

hi dave,

 

you can try rewriting parameter datasets use dimension properties instead of calculated measures, example:

 

select non empty { } on columns,

[date].[calendar year].[calendar year].members

dimension properties [name], unique_name

on rows

from [adventure works] cell properties value

 

unfortunately level_number dimension property doesn't seem appear in using built-in analysis services provider if modify above query request it. if use oledb provider instead, can use query return level_number property:

 

with member measures.dummy null
select measures.dummy on columns,
[date].[calendar year].[calendar year].members
dimension properties [name], unique_name
, level_number on rows
 from [adventure works] cell properties value

 

(cheeky bit of advertising: they also appear if use custom data extension analysis services included  http://www.it-workplace.co.uk/iq.aspx , have declare have financial interest in)

 

hth,

 

chris

SQL Server  >  SQL Server Analysis Services