jetlounge

Filtering data in a SQL Server Report Model is rather persnickety, and there is limited instruction available online or in BOL.You use Report Models in Report Builder to develop ad-hoc SQL Reporting Services reports.  This is a followup to the bug I discovered in Model Item Security as posted here and provides a workaround below.

First a brief explanation of Report Model filters and security would be in order:

Filter
 A filter on an entity like Sales where Order Date =2004. This is defined in the model builder.

SecurityFilter
 The filters defined must be attached to the entity in order to be applied. Each is applied in Union. Lockdown or access to each filter can be set by defining Model Item Security in SQL Server Management Studio. Defined in the model builder.

DefaultSecurityFilter
 The default filter to use if no SecurityFilters are defined.

Model Item Security
 Provides access at the attribute level. Access to individual fields or filters (though not explicitly stated is controlled)

A Practical Exercise

To get started, use the Microsoft tutorial called "Applying Security Filters to Model Items." It does a decent job with the basics of security filter but does not cover simple filtering, nor is there a description of how to setup the admin level "all rows" type of override that is mentioned.

Let's assume you have the Tutorial completed and the model built on Adventureworks as described. Delete the security filters they had you add in the Employee role. I'll come back to that. Let's start with something simple.

Simple Data Filters

So lets say you want to limit the output of data in reports built on this model to June 2004. Select the Sales Order entity and in the named attributes section, right click and Add a New Filter.

Drag Order date out to the Filter pad and set it to limit the dates from 6/1/2004 and 6/30/2004.

Click OK, then rename the filter to Filter 2004.

Next, select the Sales Order entity, and in the Properties window find the SecurityFilters property.
Click the elipsis (...) in the SecurityFilters property.
Find the Filter2004 filter and add this to the reference collection. Note that this dalog window is very confusing. You can only select attributes with IsFilter=True (Filters), and while you can select filters from other entities, you may be tied to certain constraints..like only aggregates on joins. Unfortunately, this dialog displays every single entity in your model, so try to remember your filter name. If you happen to choose an item that is not a filter or is an incorrect filter choice, your will only receive an error at the time of model deployment.

Save and Deploy your Model.

I created an abbreviated report based upon the example in the Tutorial. I don't need 300 pages to test out a sample. Below is what it looks like.

So what happened? Basically a SecurityFilter is able to filter the entire entity. As long as a single item is pulled from that entity (in this case, I used the Order Year field), the set will be filtered.

Note that we have not gone to SSMS to make any changes to apply the security. It is not needed for this type of filter.

Model Item Security

In the previous setup, we added a filter that applies to all users. Now we can add one that is conditional based upon the user running the report.
In the Sales Order entity add a new filter, but do not add any filter criteria. Just hit OK and rename it to AllDates. A filter without criteria will always evaluate to true. In this case we want to create an override to the filter for 2004 for an administrative user.

Next, add the filter to the SecurityFilters collection. The dialog showing this is found below.

Next, fire up SQL Server Management Studio (SSMS). We need to assign permissions by user to these filter attributes.
Connect to your Report Server, then browse to Models and find the Adventure Works Model. Double click to open the Properties window.
Select Model Item Security.
Check the box next to "Secure individual model items..." By default, there is no model item security defined. By selecting the box, we claim an override.

Click the Add Group or User button and add the group Everyone. Then click Ok and make sure "Model Item browser is selected.
Go to the Model item Sales Orders, then find AllDates.
Select "Use these roles for each group..." and 'Add Group or User"
Enter your servername\Rachel0 then select Model Item Browser.

We have just provided Rachel0 access to all years of data, and users who are not in this group will be relegated to 2004 informaton.
Click OK to save.

Run your report as two different users. Depending upon your configuration in SSRS you can simply login differently in two browsers. One as a normal user, and another as Rachel0. If you are not prompted for login, right click the IE icon before running and select the Run As... option.

You will notice that 1 user gets to see 2004 data and Rachel0 will see all years.

Some important caveats:

• A Content Manager in SSRS is NOT confined by Model Item Security. This appears to be a bug, that I have reported to the Microsoft Connect site. I have also blogged about it here. So if you test any other user that is a Content Manager, they will have the same access as Rachel0
• Subscriptions CANNOT be made on reports that use Model Item Security.

Since we have two very big challenges as mentioned above, I thought hard on a work around. Being able to subscribe to reports is more important to me than item level security. Besides, model item security was really only being used here to provide conditional access to a filter.

If we could place that conditional user access in the Report Model itselft without having to touch model item security we'd take care of both of those challenges. Here is the quick and dirty:

Go to your "all rows" filter on your model. Rather than leaving that blank, add your condition on user security there. You'll find below that we make TRUE equal to a conditional statement that also evaluates UserID.
Here I happen to use TRUE=IF(FIND(GETUSERID(),"Rachel") > 0, TRUE, FALSE)

Save and redeploy. Then go to SSMS and remove the Item level security.

When you rerun the report only users named Rachel will have access to all data. Others will be limited to data based upon the filter. This is a rather simplistic example, you probably won't name each user, but you can see the potential. Store user or group lookups in  your tables for "managers" or others who need expanded access to data.

Before you fret that your user logic is tied to a database table, understand that it was alread stored there in the tutorial. And rather than individually set user access in SSMS, we are now storing it in a conditional statement in the model (or table if you prefer). So you still have security, we just shifted it's placement.

The benefit of course is not having to use SSMS to apply security, and the ability to now subscribe to reports even if they are filtered by user. Lastly, we bypass the "Content Manager has all access" bug.