Introduction

A few years back, a client asked me to implement a quick and dirty “security mechanism” to control what data the myriad of users were able to view within their reports. There were numerous tables with multiple columns and all departments (within the enterprise) had their data within these tables.

SQLShack Industries has tasked us with creating a similar quick and dirty “security mechanism”. We shall attack this challenge by creating the necessary stored procedures (to extract the required data) and then utilize these stored procedures to render and consume the data within our reports.

Getting started

Opening SQL Server Management Studio we find our ‘YearlySales1’ table. This table contains the total monthly sales figures for two sales teams (Team1 and Team2). For the first portion of this exercise Team1 is ONLY permitted to view THEIR OWN data and Team2 THEIR OWN data.

The raw data maybe seen below:

Our first task is to construct a small security table called “SQLShackSecurity” (see below).

The client has two department heads and both head major corporate teams (see below).

Now that our infrastructure has been created, we are ready to create the necessary stored procedures to service the corporate reports.

Going forward we shall be working with a Reporting Services system variable User!UserID and it is the value of this variable that will be passed to the stored procedure from the report body. This variable is the key component to ensuring that the pieces fit together and function correctly (see ADDENDA B).

User!UserID contains the Domain and user ID of the individual requesting access to the data.

Let us have a quick look at the contents of the security table.

We note that there are two main users. User “ATRION\ssimon” is entitled to the Team 1 data and user “ATRION\SQLDB” is entitled to view only the Team2 data.

Constructing our stored procedures

Having created the necessary infrastructure, let us look at a small query which is going to return the required data to the appropriate teams.

We see that (as is) the query returns all the data for Team1 as user “ATRION\ssimon” is permitted to see Team1 data.

Changing the user to “ATRION\SQLDB” the following data is returned.

Restricting data columns

After a meeting with SQLShack Industries management, we find that there is additional data that must be locked down.

Many SQLShack Industries reports contain a Total Sales per month field and management feels that Team1 should be able to view all corporate monthly sales, whilst the remaining teams may only view their own results.

The following piece of code will help us achieve the desired results. Once again permissions are based upon the user ID detected upon entry into the Reporting Services / SharePoint Environment.

A note of explanation is required for the following line of the code:

As we see in the table below, we may find the condition where a user has access to the data from more than one team. In this case, the rights are assigned based upon the rights allocated to the lowest team number (i.e. Team1).

Running our query for user ATRION\SQLDB we find that the figure for the Total Sales / Revenue are as follows:

Changing the user to ATRION\ssimon we find the following results.

The astute reader will note that in the case of user ATRION\SQLDB, he or she is only able to see the data for Team2 and the total column reflects that.

User ATRION\ssimon, on the other hand is able to see all the data and therefore his or her total field reflects the summary total of sales for Team1 and Team2. We make one final change to this code and that is to add a new calculated field called “Monthee” which will contain the first three letters of the calendar month name (e.g. ‘Jan’). We utilize the RIGHT() function “RIGHT(YEARMTH,2)”.

Note the usage of the RIGHT function to set the calendar month name (see above).

Data format conversion for matrix based reporting

Financial management at SQLShack Industries has also tasked us with showing these results in the form of a matrix. The thought behind this is, that it makes the results clearer to the decision maker. It should be remembered that the data format that we have just finished dealing with, is probably more conducive for a chart. This said, we are now going to structure a NEW stored procedure that will render the data in a format suitable for a matrix.

We begin by adding twelve variables @Month01 through @Month12. By using the code at the top of the screen dump below, we are able to populate these twelve variables. The complete code listing may be found in ADDENDA A.

The eagle–eyed reader will ask, why declare and populate twelve variable fields, when the year and month already exist within the “YearlySales1” database table.

In approaching the extract in this manner, i.e. utilizing the twelve variables, we are certain of obtaining the current twelve month period (once again, please see the code in ADDENDA A). Remember that the table could contain data from a plethora of years.

Adding the following pivot code to our original code (that we used for the chart), we now have a query that is more conducive to a matrix format.

–– The values for @month01 through @month12 are calculated on the fly. The code to do so
–– may be found in the ADDENDA at the end of this article.

Now executing the query for user ATRION\ssimon we find:

and for user ATRION\SQLDB we find:

These are the results that we would have expected to obtain (see above).

Creating our first production reports

Opening SQL Server Data Tools, we create a new Reporting Services Project and name it “SQLShackReportingSecurity”. Should you be unfamiliar with Reporting Services or how to create a project, please do have a look at my earlier article entitled: “Now you see it, now you don’t”

/now-see-now-dont/

Within this article I describe in great detail how to get started creating a Reporting Services project.

We first create a shared “Data Source” (see below). This data source contains the necessary information to connect to the source data within the SQL Server database.

Creating our first report

Within our project we right click on the report folder, select “Add” and “New Item”.

We add a new report as shown below:

We click “Add” and we are returned to the report drawing surface.

We are now going to create a vertical bar chart graph (see below).

For this exercise we shall be utilizing a column chart (see above).

Creating our dataset

In order for us to access the raw data from the database table (i.e. the data which will be consumed by our bar chart), we must first create a dataset. Should you be unfamiliar with the process or not understand the concept of a “dataset”, then please do have a look at one of my earlier articles “Now you see it, now you don’t”

/now-see-now-dont/

We click the “New” button (see above). The local data source dialog box is brought up (see below).

We note above that we opt to use as “Shared Data Source”. This is the shared data source that we created at the start of the project. We click “OK” to leave the local data source dialog box and we are returned to the “DataSet Properties” box (see below).

We opt for the “Stored Procedure” as the “Query type” and select “ServiceRevenue01” option as the procedure that we wish to utilize. We click OK to exit the “Dataset Properties” dialog box and we are returned to the report drawing surface (see below).

Note our new dataset appears on the left hand side of the screen shot (see above).

We now resize our chart control and assign the dataset (that we have just created) as the source of data for the chart (see below and to the right).

We also add a title to the chart and call it “Revenue”. We set the charts series, values and category groups (See below).

As we have done in past sessions together, we shall be using the “YearMth” field purely as a sorting field and use the “month names” column, “Monthee” as the ‘labels’ for the X axis. Note the way that we prevent the values of “YearMth” from appearing when the report is run (see below).

Highlighting “YearMth” and selecting the “Category Group Properties” tab (see above) we bring up the “Category Group Properties” dialog box.

We click the expression box for the “Label” option. The expression box opens.

We replace the value “=Fields!Yearmth.Value” with “=Nothing” and Click OK,OK to exit the “Properties” box.

Configuring the necessary parameters

While the chart and the necessary data fields are now in place, we must now complete the necessary “wiring” and obtain the user’s login ID and pass that through to the stored procedure thus ensuring that the correct data is extracted and rendered.

We define a Parameter called UserID (see below)

Note that our parameter is going to be “Hidden”.

On the “Available Values” tab, we leave the “Select from one of the following options:” radio button ‘as is’.

Moving to the “Default Values” tab, we are going to set a default.

We click on the “Specify values” radio button and click the “Add” and click the expressions box to open the “Expression” editor.

The expressions box opens and we add “ =User!UserID” (see above). We click OK and OK to exit the “Report Parameter Properties” box.

Moving to our dataset, we right click on the dataset name and open its properties dialog box.

We click on the “Parameters” tab.

Should our parameter @UserID not be there, then we must select the “Add” button to add the parameter @UserID (see above).

Let us now run our report to see the results.

The reader will note that I inserted a text box above the chart, to display the value of @UserID (see above).

This obviously would NOT be there within the production environment and going forward, I have removed this text box from further screen shots.

“Re-arranging the furniture”

Prior to adding our matrix based report, we should really re-arrange the controls that we currently show upon our screen.

I have taken the liberty of adding a text box above the chart and I have given my report the title “SQLShack Industries Team Revenue Report”.

Further, I have labelled the Y axis as “Revenue” and set the property to currency (see above).

Adding a matrix to the mix

As you will remember, we created two store procedures. The second one was in the format that could be utilized with a matrix. We are now going to add that matrix to our report.

We drag a “Matrix Report Item” onto the drawing surface.

In a similar manner to which we created the chart dataset (above), we create an additional dataset for the matrix. The question that DOES arise is, “Could we have created one data set for both?” The answer is yes HOWEVER, I prefer to keep the processes as simple as possible as this gives you added flexibility to handle ‘change’.

The screen shot above shows the dataset for the Matrix.

Prior to configuring the matrix, we remove the “Column Grouping” as shown above (middle bottom).

Further we are ONLY removing the Grouping and NOT the data (see above).

Note that the left most column of the matrix is the “grouping“column. We are going to use the column “Name” (which contains the Team names) as the grouping field (see above). Further, we add eleven more columns to ensure that we have enough columns for the twelve months of the year.

We are now going change the column headers to something more meaningful. We shall also right orient the revenue fields, in addition to adding fill to the matrices’ cells (see below).

When we run our report for “ATRION\ssimon”, we find the following (see below).

When we run our report for “ATRION\SQLDB”, we find the following (see below).

Conclusions

We have completed the work that SQLShack Industries’ management had requested. The data rendered within the reports reflected the data permissions that each group/team had.

Whilst this technique is aimed for internal business consumption, it is far from the type of security that one would implement for users coming in through the firewall.

As always, should you have any questions or concerns, please feel free to contact me.

In the interim, Happy Programming!!

ADDENDA A

The code sample for the matrix may be seen below:

ADDENDA B

User ATRION\ssimon observes the following when a report is pushed to SharePoint

User ATRION\SQLDB observes the following when a report is pushed to SharePoint

• Author
• Recent Posts

Steve Simon

Steve Simon is a SQL Server MVP and a senior BI Development Engineer with Atrion Networking. He has been involved with database design and analysis for over 29 years.

Steve has presented papers at 8 PASS Summits and one at PASS Europe 2009 and 2010. He has recently presented a Master Data Services presentation at the PASS Amsterdam Rally.

Steve has presented 5 papers at the Information Builders' Summits. He is a PASS regional mentor.

View all posts by Steve Simon

Latest posts by Steve Simon (see all)

• Reporting in SQL Server – Using calculated Expressions within reports - December 19, 2016
• How to use Expressions within SQL Server Reporting Services to create efficient reports - December 9, 2016
• How to use SQL Server Data Quality Services to ensure the correct aggregation of data - November 9, 2016