How to Setup Report Parameters to specific value depends on users

I've created a basic report template, that display some information about a user. Basically it takes a City as parameter (New York,Chicago,Los Angeles)

Then I want to be able to run this report depends on user that access to report

(ex. when user A run report --Parameter city (New York) is only show to user A user B-- only show Chicago user C -- only Los Angeles)

It's actually like: Setup Report Parameters to Default Based On User Credentials

Is this possible? If so can somebody let me know how or point me to a tutorial on how to do it?

Thanks

Solution

The best way to do this, especially if you plan on using the same logic again, would be to create a table that holds the user name and 'allowed' cities.

For example, at it's most basic, you could have a table like this..

UserName	City
User A	New York
User B	Chicago
User C	Los Angeles
User X	New York
User X	Chicago

Next create a dataset in your report called say, dsAllowedCities using the following query.

SELECT City FROM myUserCityMapTable WHERE @pUser

When you have created the dataset, you'll see that a report parameter pUser has been created for you.

Right-click the parameter and select "Parameter properties", then on the Default Values tab, select "Specify Values". Now click add to add a new value and click the [fx] button to open the expression editor. Set the Value to =User!UserID

You can make the parameter hidden once you have the report working so that the user cannot overwrite the name.

Next, add a new parameter called say pCities, go to the properties and set the available values (and default values if you like) to the earlier dataset dsAllowedCities. Make this parameter multi-value if you have a case where a user can choose more than 1 City (as per my sample data above where User X can choose 2)

Finally your main dataset query would look something like

SELECT * FROM myTable WHERE City IN(@pCicites)

That should be it.

If you will only ever have a single city per user than it's much simpler.

Simply join you table to the new usercitymap table and filter based on user, something like..

SELECT a.*
    FROM myTable a 
        INNER JOIN myUserCityMapTable m on a.City = m.City
    WHERE m.UserName = @pUser