Filter a Model Table?

I am using RS 2005. I am setting up a Model for use within Report Builder so our clients can write their own reports.

A 2 part question, simple question first:

1. How can I filter the records in a Model Entity? I thought this would be possible from a Perspective but it is not.

2. How can I filter the records in a Model Entity based on the locale of the person who is logged on? And also based on their permissions?

TIA

You are correct that this is not possible with Perspectives. Note that Perspectives do not secure your data in any way.

You can use Model Item Security and Security Filters to expose different records to different users. A security filter could include a formula filter condition that uses the GETUSERCULTURE function. However, this seems a little odd, since culture is trivial to “spoof”, so it doesn’t really secure anything.

--Bob

|||Thanks for the reply Bob.

So I am in SQL Server Management Studio, I have double clicked on the model and gone to Model Item Security. But, I cannot see where to set up a Security Filter? Also, when I seach on SQL Server 2005 BOL for "Security Filter" it finds nothing.

|||Hi, can someone please help me with this ... please. People keep talking about Security Filter Scripts but I cannot find them anywhere ...|||Ummm, well can someone then please just tell me if this is a difficult question? I have seen a few other posts on the same question and the answer is never posted. Can someone at MSFT please just put me out of my misery and tell me either how to use this facility or, even better, refer me to the associated documentation or, not so good, that the feature is a figment of our collective imaginations. :)|||SecurityFilters is a collection property of a model entity. You "turn on" security filters by adding at least one filter attribute to this collection (i.e. if this collection is empty, security filters are "off" and all users with permission to the entity will be able to see all rows). Each filter in the collection defines a set of rows to which a user or group may be granted access. You can grant access to a specific user or group by giving them permission to see the filter attribute using Model Item Security. Users will have access to the UNION of all rows exposed by the security filters for which they have permission. Note 1: Filter attributes are typically used only for security filters, so the Hidden property is usually set to true. Note 2: Model entities also have a DefaultSecurityFilter property which can be used to grant access to some set of rows for users that do not have access to any of the filter attributes in the SecurityFilters collection.|||I am afraid this is getting very frustrating for me. Your answer here sounds very nice Bob - but it does not tell me how to do it. Also if I search google and microsoft for SecurityFilters or look for books I find no further help. I have a book on Report Services 2000, but obviously it does not cover of Report Models (a 2005 feature).

I am using v8.0.50215.44 of Visual Studio 2005. And I am using v9.00.1187.00 of Microsoft SQL Server Management Studio. Maybe these versions are too old?

If I open up the model designer and click on a model entity to see it's properties, then where do I add in collection properties for the model entity? Especially, how can I say it is a filter attribute? I can see no such properties.

TIA|||I'm sorry this has been frustrating. Let me try to fill in some of the gaps here.
SQL 2005 documentation ("SQL Books Online") is not available on the web yet. It sounds like you have the July CTP build installed; I'm not sure what state the documentation was in back then. Even if you can find the SecurityFilters property, it may have just been stub docs at that point.
If you want, you can download the September CTP docs here . RTM bits (including docs) are now available to MSDN subscribers, and will be publicly available after the launch next week.
SecurityFilters is a property on a model entity. You should see it near the end of the list in the property grid in Model Designer. Like several other model entity properties (e.g. IdentifyingAttributes, DefaultDetailAttributes, SortAttributes), it contains a set of references to model attributes you have previously defined. The only constraint on this particular collection is that all attributes referenced by it must have DataType=Boolean and IsFilter=True. The easiest way to create such an attribute is to select the entity on which it will be defined, right-click on an empty area of the attribute list, and choose New->Filter.
Hopefully this is enough to help you get started.|||Many thanks Bob! Yes, the problem has been that I have had the July CTP build. That info looks perfect to get me started - I appreciate your help.

I'll go get the Septmeber CTP build and doco now and work from there.

I'm looking forward to the release date - I am registered for the one day launch here in NZ - should be grand! :)|||Current books online still has very few things to say about secufity filters on report models. Does anyone know of any sites or articles that would greatly explain how to do this?|||

Hi Bob,

Your article on http://msdn2.microsoft.com/en-us/library/ms365343.aspx is very helpful, but when I use SQL Server Management Studio to set Model Item Security for differnt security filters, it seems "Permissions" property surpass "Model Item Security" property.

For example, in "Permissions" property of the model, if I checked "Use these roles for each group or user account" without setting any user or group, no matter what users I added to "Model Item Security" with "Secure individual model items independently for this model" checked, NO one user can see the model on report manager and report builder;

in above situation, if I added "user1" and gave role such as "Browser" role to "user1" in "Permissions" property, if I checked "Secure individual model items independently for this model" in "Model Item Security" property, even I did NOT grant "user1" to root model and any entities under the model, the "user1" is able to access the model and all entities in report builder.

My question is on the same report model, how to set "AdminFilter" (empty security filter) for administrator permissions and set "GeneralFilter" (filtered on UserID) for general user based on their UserID?

I posted my issue on http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1905805&SiteID=17, I attach my post here:

"

I read the following on article http://msdn2.microsoft.com/en-us/library/ms365343.aspx:

"Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows."

So I defined 2 filters "GeneralFilter" and "AdminFilter" for "Staff" entity for my report model "SSRSModel", I expect after I deployed the report model, the administrator users use report builder to build reports with all rows available, and the non-admin users can only see rows based on their UserID.

I can only get one result at a time but not both:

either the rows are filtered or not filtered at all, no matter how I set the "SecurityFilter" for the entity: I tried setting both "AdminFilter" and "GeneralFilter" for SecurityFilter at the same time, combination of "DefaultSecurityFilter" and "SecurityFilter", or one at a time.

Anybody please please help me? Thank you!

"

Report server is using Custom Authentication.

Thank you for your help.

Temple1