Drillthrough Security Conundrum AS 2005

Hi,

I'm looking for some inspiration on how to solve the following issue.

I'm working on a cube that contains crime data. I have a number of reports built using SSRS that show summary totals for different types of crime. In addition, there are a number of linked reports where the user can drill through to the underlying details of the crimes in the fact table fact table. These reports use the DRILLTHROUGH statement via the OLE DB provider for OLAP. So far, so good.

The client now wants to restrict drillthrough on certain types of crime.

Essentially, if a user is a member of Role1, they should be able to see the summary total for Crime A in the main report but they should NOT be able to drillthrough to the details of that summary

If the user is a member of Role2, they should be able to drill through to the detail level.

So far, I cannot see a way to do this with role permissions. My understanding and experience of the role based security thus far is as follows (please correct me if I'm wrong)

1) You can enable or disable drill through at the cube level for a particular role via the Cubes > Drillthrough Access property. This is too restrictive for my needs in this case

2) You can hide certain dimension members via Dimension Data tab but this results in the particular crime types not being displayed in the main report (i.e. you can't see the summary total for that crime)

3) You can restrict access to certain cell values via Cell Data but again this results in a value like N/A being displayed in the main report.

Has anyone faced a similar problem and come up with a way to do this?

Any feedback appreciated

NJDUNNE

The right way to approach this is through dimension security (Dimension Data tab). Drillthrough respects all the dimension security settings. You can restrict access to details, but make sure that Visual Totals=false, therefore you will see the summaries but won't be able to go to details.|||

Hi ,

Do u know if we can create drill through reports in SSRS through SSAS & Sql Server 2005 as the Database . I want to know how this can be done ?

Regards

Rashmi