How to delimit user access on the Reporting Server?

There are different approaches to organizing folders on the MS SQL Server Reporting Services. One of them is to group reports on topics or modules and grant access to all users of iScala. The advantage of this approach is in simplicity, but the disadvantage is that anyone can view any report. In this article I will share a more complex, but much more «advanced» way to organize folders and access not only to the reports in them itself, but also to restrict access to data in these reports.

In the picture below you can see the folders associated with the department numbers (in iScala this is the accounting dimension 1 «Department»):

Every employee has access to the shared folder «Departments», but inside it only in the folder of his own department. In other words, in the folder 1002, for example, only employees of the Front Office department have access, and other users are not allowed to it. The reports themselves (the originals) are in a separate folder where users do not have access, but we can create «Linked Reports» in the departmental folders, which are a kind of shortcut to the main report, but they have their own set of parameters. To create such linked report, you need to do the following:

  1. Choose the original report and click on the item indicating the drop-down menu. Next, select the command «Create Linked Report»:
  2. Copy the name of source report as the name of the linked report and click the button «Change Location»:
  3. Select the folder where you want to place the linked report and click «OK»:
  4. After you return to the linked report creation form, click «OK»:
  5. The report server will generate the associated report. But we still need to change the parameters, for which we need to go into the «Manage» mode. To do this, click on the report title in the navigation line:
  6. After switching to the «Manage» mode, go to the parameters tab:
  7. This picture most interesting 🙂
    In the report there is a parameter with the department code (Accounting dimension 1 in iScala).

    We can set it by default equal to the department code corresponding to the department folder and make it hidden, i.e. the user will not be able to change this setting. The same can be done with the company code parameter:
  8. As a result, users of department 2301 will see only a set of «their» warehouses, and warehouses of other departments will not see:
  9. And for the financial department, the hidden parameter «Dpt» is set to «*» and users of this department will be able to see all warehouses

You can read additional article about automation of creating linked reports in case of complex folder structure (sorry, in Russian only) «Автоматизация создания связанных отчётов при сложной структуре»

If you have any questions, do not hesitate to ask:

    Your Name (mandatory)

    Your e-mail (mandatory)

    Subject

    Message

    Other procedures and articles in English: