17 December 2014

SQL Reporting – Custom Permissions versus Linked Reports

Ok… So you are a SQL Reporting developer…
You have a report…
Has a nice design…
It’s fast… uau…
The users like it a lot… double uau!…
But, after a while, a new Functional Requirement comes along:

«For that same report, I need that the group of users “X” sees only Data “X” for input-parameter named “param1” and that group “Y” sees only Data “Y” and “Z” in that same input-parameter. And the data in the report table must be filtered for group “Y”»
Well, it seems that we have to conditionate the user-input with the user who tries to access the report… How ?!
Easy: I copy the current report, two times, then I give one link to each group, so the correct report can be executed!
BAD: If we change some rules in one report we must replicate them to the other and so on. And we must deal with two links: not good!
Easy again: We set the values for those parameters in the URL and we give each URL to the right Group of users! The report has to deal with those values passed in the URL.
BAD AGAIN:: The URL has a limit of characters. Also everybody can see and change the values in the URL! NOT SO GOOD! Dam!

Linked Reports? Did you shout «Linked Reports»?! What is that?
A Linked Report is a report server item that provides an access point to an existing report. Conceptually, it is similar to a program shortcut that you use to run a program or open a file. A linked report is derived from an existing report and retains the original's report definition.
A linked report always inherits report layout and data source properties of the original report. All other properties and settings can be different from those of the original report, including security, parameters, location, subscriptions, and schedules. You can create a linked report when you want to create additional versions of an existing report.
Although linked reports are typically based on parameterized reports, a parameterized report is not required. You can create linked reports whenever you want to deploy an existing report with different settings. Great! So how can I do that?! To create a linked report, choose an existing report on which to base the linked report.
The report that you choose determines the linked report content and layout. If the base report uses parameters, you can set the parameter value in the linked report to a value that is different from that specified in the base report. Not all published reports can be used as a basis for a linked report.
Content managers or report server administrators can prevent specific users or groups from creating linked reports by removing the "Create linked reports" task from the role assignments that are in effect for a given report. When creating a linked report, you can set the following properties and settings that are specific to your linked report:

  • Name and Description. You can define a different name and description to distinguish a linked report from the existing report.

  • Location. You can put the linked report in a folder that is different from the folder that contains the original report.

  • Parameters. You can specify a set of parameter values to use with the linked report. The capability of having different parameter values for the report is typically the reason why you may want to define a linked report in the first place. 

  • Report execution and Report history. You can set report execution and report history properties that vary from those set on the original report.

  • Security. You can define role assignments that control access to the linked report.

  • Subscriptions. The subscriptions defined for a linked report are independent of those defined for the report upon which it is based.

The only properties that you cannot set on a linked report are data source properties. A linked report always uses the data source properties that are defined for the existing report. This is because the existing report provides the report definition.
The report definition includes data source connection information, the query that retrieves report data, and report layout.

Figure 1 – example of a linked report Hum… I only need the Parameters and Security section! First I create a linked report for group “X”…
(If you want more technical details I recommend you to watch this video: https://www.youtube.com/watch?v=1f7jHrtfhp0 ).

  • I set the Security section for group “X” only;

  • Then I set the parameter named “param1” for the default value X for this group;

  • Then I hide this parameter from the user so that he can’t change it.

Then I create another linked report, but now for group “Y” :

  • I set the Security section for group “Y” only;

  • Then I set the parameter named “param1” for the defaults values Y and Z (multivalued) for this group;

  • Then I hide also this parameter from the user so that he can’t change it.

I give the link for the first linked report to the users of group X. Then I give the link for the second linked report to users of group Y. The business rules in the report are the same and if we change them, the linked report automatically reflects it. Great! But… We have to deal with two links… Not so great but that is not too serious. I think. So we have a solution! Not quite, we forgot the requirement:
« (…) and the data in the report table must be filtered for group “Y”! » Oh no… How can I deal with this now? Think, think, think… (Take two…) It’s easy: we create another parameter for the original report for handling this filter! Of course it becomes hidden from the user view and has, as default value, a blank value. Then I passed it to the main dataset of the report so that the query can handle this filter. Then, in the two linked reports, I set this parameter to the right value! Yes! But wait…That means that I have to change the original report definition because of this new parameter.
So, that means I’m obliged to “refresh” the linked reports again so they can link to the correct definition… Not so good when this solution will be in Production environment… It should work! I’ll implement it right away!
Suddenly my business client shouts, again, to me:
«For that same report, I need another group of users “T”! They will see only Data “X” and “U” and “O” for input-parameter named “param1”! »
Ok… So I need to create another linked report to handle this. But, ok, this isn’t a very scalable solution… It’s a nice mechanism, indeed, but for this type of business situation it has a serious cost… Oh no… So how can we handle this?! How?!
Custom Report Permissions! Why not let the report handle the permissions for that user who tries to access it? We can always identify the user who is trying to access the report. The SQL Reporting has a function for that, it’s quite easy. So… We design a table (in SQL Server) with columns:

  • ReportName (the report)

  • UserName (the user who accesses the report)

  • KeyName (the key, permission)

  • KeyValue (value)

  • ...and, ok, a (technical) Description for this configuration !

Then each key can map to what we want in that report. I mean, we can create keys for the input-parameters and another key to handle the filter for the dataset. And more: we can create more keys to handle, for example, layout considerations (hide, show some objects) related to the user! Fantastic! So for all users of group “X” we must insert in this table a record, using these values (text values):

  • ReportName = report

  • UserName = user from group “X”

  • KeyName = param1

  • KeyValue = X

  • ReportName = report

  • UserName = user from group “X”

  • KeyName = dataset_filter

  • KeyValue = ALL (*)

(*) for example : that means that these users can see all main data. For all users of group “Y” we must insert in this table a record, using these values:

  • ReportName = report

  • UserName = user from group “Y”

  • KeyName = param1

  • KeyValue = Y

  • ReportName = report

  • UserName = user from group “Y”

  • KeyName = param1

  • KeyValue = Z

  • ReportName = report

  • UserName = user from group “Y”

  • KeyName = dataset_filter

  • KeyValue = <filter expression to pass to the dataset query>

These users need two records for “param1” in this table because the functional requirement expresses that. Of course we could optimize and separate them with a comma, for example. But for now we’ll use this strategy. Ok, we have the permissions inserted in this “Custom Permissions Table”… But how can we map these permissions to the report? (Take three…) In the report, we set the parameter named “param1” to get its values from a dataset that reads the permissions for the current user! Let me detail:
Step 1 Create a dataset for this parameter “param1” to read the value key for the current user. We create a SQL query like this (note: of course we can build Stored Procedures and I recommend it but for now, we’ll use this simples queries): SELECT KeyValue FROM WHERE UserName=:PARAMETER_USER AND KeyName=’param1’
Step 2 Then in the DataSet parameter section, we set the value for the parameter “PARAMETER_USER”, using the internal function: [&UserID] (gets the current user name):

Step 3 Then for the parameter “param1” we must define how it gets its values:

And that’s it!! The parameter is populated “automatically” with the values defined for the current user, when we accesses the report!
The report is the same and we only have one link to share with all users! Fantastic! For the other key named “dataset_filter” we can use this same logic. It’s easy! Imagine that, according to the current user, if it belongs to group “X” the report must show a certain image. And for group “Y” (and so on…) it must show another. What can we do? Easy! Using this logic:

  • Create another key in the “Custom Table Permissions” that has, as value, the link to the image…

  • Create another dataset to read the value for this key…

  • Set it to an (hidden) parameter and then use it in the image expression path!

This is the right solution! Thank you for joining me.

.

.

.

.

   Pedro Mesquita
       Consultant