In cases where you want the data or content in a report to be different for each user, there are a couple different ways to approach this. This will depend on whether you want to return user-specific data or whether you want to use logic to hide and show objects in the report depending on a user’s region. The first technique is to filter rows of data by user within the query. To do this, you will need to store the network user names of all your users in a table within your database. Using an example of users in regions A or B you could store the user name and their region in a UserRegion table like this:
…then you would reference this table in the dataset query and use a parameter to filter records, which would be something like this:
SELECT col1, col2. col3, etc
Sales s INNER JOIN Region r ON s.RegionID = r.RegionID
INNER JOIN UserRegion ur ON r.RegionID = ur.RegionID
WHERE ur.UserID = @UserID
In the dataset properties Parameters page, change the mapping for the UserID query parameter to use the UserID report global object, as in: =Globals!UserID. You can also delete the UserID report parameter that was autogenerated by the dataset.
The other technique is to dynamically hide and show objects, such as a data region, report item, a data region group or an individual table row. You can do this for each user but you’d have to build specific logic into the report for every user. You could also leverage the same database/query technique as above to get the current user’s region and then dynamically hide or show an object by setting its Hidden property to an expression like:
=(FIRST(Fields!Region.Value, “Dataset1”) <> “Region A”)
In the above example, the row or object would be hidden if the user’s region was not Region A.
This question comes up quite often. A report requires a user to be authenticated using their own network user name and password. Perhaps the data source performs row-level filtering for the user or the UserID global user object is used in a query or filter expression to return user-specific data. Whatever the case may be, how can you send scheduled subscriptions and filter the report for that user? This can be a challenge when scheduling a subscription for the report because a subscription requires that credentials be stored in the data source. This is not so much a limitation of Reporting Services as it is a logistical challenge. Why? ?because a subscription is designed to run unattended. The data source credentials must be stored so when the subscription fires in the middle of the night, the report and database query can run without the user logging in and sitting in front of their computer.
There are at least two possible solutions to this quandary . The first is to pass the UserID as a parameter and then filter the data in the query. The data source will still have to use a stored application credential but if user-specific filtering is the goal, this technique should meet the requirement. You can store each user?s UserID, along with any other report parameters and report execution settings (like the rendering format, etc.) in a table and then broadcast the report out to several users with a data-driven subscription.
The other approach is to use a scheduled subscription to email a link to the report, which will then run on-demand when the user clicks the emailed link. To do this:
- Deploy the report you want the user to run on-demand without stored credentials in the data source.
- Create a very simple, small report containing only a textbox to use as a link. Set a URL action on this textbox to open the other report, using the full path to the report on the server.
- Schedule a subscription for this small report and use the option to embed it into the email.
When the user receives the subscription email, they will just see a link, which as actually a report drill-through action that they will use to open the second report. Since that report doesn’t use stored credentials, it will use their own credentials to authenticate.