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:
| UserID | Region |
| YourDomain\FredF | Region A |
| YourDomain\WilmaF | Region A |
| YourDomain\BarneyR | Region B |
| YourDomain\BettyR | Region B |
...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
FROM
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:
<> "Region A")
=(FIRST(Fields!Region.Value, "Dataset1")
In the above example, the row or object would be hidden if the user's region was not
Region A.
Weblog by Paul Turley and SQL Server BI Blog.
