CODEWITHSUNDEEP
reporting-servicesssrs-2012
Xander
Xander

Reputation: 9171

Ad Hoc reporting with SSRS and shared customer database

I have a database with many customers inside of it. Each row of the table has a customer ID that denotes which customer the data belongs to.

Is it possible to let the end user use Ad-Hoc reporting to build reports, but only let them see data associated to their customer id?

Upvotes: 0

Views: 805

Answers (1)

Nathan Griffiths
Nathan Griffiths

Reputation: 12756

In a report you can use the Built-in field UserID to return the current user's domain name e.g. MYDOMAIN\Username.

If you can create a table mapping customer Ids to domain names and join this to your main data table then you can add a where clause into your dataset query such as

WHERE MappingTable.DomainUsername = @CurrentUser

In the report, create a hidden parameter called CurrentUser and set the value to Globals!UserID.Value

You need to be using Windows authentication in the report data source, where the report users credentials are being passed through to the data source, for this solution to work.

EDIT: I see I misread your original question, which was actually asking if it's possible to allow users to create ad hoc reports while restricting their view of the data. This scenario precludes any solution that is implemented in the reports themselves, since the users will be designing their own reports. Instead, you will probably need to implement some form of security layer in source database, for example using Views. You can use a similar approach to above if your users are using domain logins. For example your view could contain a reference to SUSER_NAME() to limit that data for that particular user.

Upvotes: 1

Related Questions