Скачать или смотреть Setting Up Report Parameters Based on User Access in SSRS

Discover how to customize report parameters in SSRS, allowing distinct city data to be shown based on user credentials.
---
This video is based on the question https://stackoverflow.com/q/76367637/ asked by the user 'aziz_h' ( https://stackoverflow.com/u/21987919/ ) and on the answer https://stackoverflow.com/a/76367917/ provided by the user 'Alan Schofield' ( https://stackoverflow.com/u/1775389/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to Setup Report Parameters to specific value depends on users

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Set Up Report Parameters Based on Specific User Values in SSRS

When using SQL Server Reporting Services (SSRS), creating dynamic reports that cater to user-specific data can significantly enhance user experience and data security. One common challenge is how to configure report parameters so users only see data relevant to them.

In this guide, we will address how to set up report parameters such that the displayed information (like cities) is different depending on the user running the report. For instance, User A might access reports showing only "New York," while User B sees only "Chicago," and so forth.

Understanding the Problem

Imagine you've built a report that requires users to select a city from a limited list. Depending on the user's credentials, you want to restrict available options to only those cities they are allowed to view. The goal is to automate this based on who is logged in.

Example Scenario

User A has access to "New York"

User B has access to "Chicago"

User C has access to "Los Angeles"

This functionality can streamline report access and maintain data security by preventing users from viewing information they shouldn’t.

The Solution

Step 1: Create a User-City Mapping Table

Establishing a foundational table is essential. This table will map each user to their permitted cities. Here’s a simple example structure:

UserNameCityUser ANew YorkUser BChicagoUser CLos AngelesUser XNew YorkUser XChicagoStep 2: Create a Dataset for Allowed Cities

Next, you'll need to create a new dataset in your SSRS report. This will be named something like dsAllowedCities. Use the following SQL query to fetch the relevant cities based on the logged-in user:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Set Up the User Parameter

Once the dataset is created, you will notice SSRS has generated a report parameter (pUser) for you.

Right-click on this parameter and select "Parameter properties".

Go to the Default Values tab.

Choose "Specify Values" and click "Add".

Open the expression editor by clicking the [fx] button and set the value to =User!UserID.

This setup binds the parameter's value to the currently logged-in user.

Step 4: Create the City Parameter

Create another parameter called pCities.

Go to the properties of this new parameter.

Set its available values (and default values, if desired) to the dataset dsAllowedCities.

Optionally, make this parameter multi-value to allow users to select more than one city, as in cases where User X can choose both "New York" and "Chicago."

Step 5: Modify Your Main Dataset Query

Finally, adjust your main dataset query to utilize the selected cities. Your SQL should encapsulate something along the lines of:

[[See Video to Reveal this Text or Code Snippet]]

Simplified Approach for Single City Access

If your users will only ever have access to one city, you can simplify your query further:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By following these steps, you will have successfully set up your SSRS reports to ensure users can only view data relevant to their roles or permissions. This not only secures sensitive information but also improves the overall user experience by presenting them with only the options they require.

Implementing such user-specific filtering increases accountability and enhances the reporting functionality of your applications.

If you have questions or need further assistance with setting this up, feel free to reach out or leave a comment below!