Lesson 2: Creating Attributes for Row-Level Security
One common method of defining row-level security is to use the user's login information to determine which rows to display. In this lesson, you will create an attribute within the Employee entity that filters the data based on the user's login information. You will create a filter and then edit the filter as a formula so that you can use the GetUserID function.
Note
This lesson begins with the expectation that Business Intelligence Development Studio is open on your computer still and the report model is displayed in the window.
To create a filtered attribute that is edited as a formula
In tree view, select the Employee entity.
The Employee attributes are displayed in the list view.
Right-click Employee, point to New, and then click Filter.
The Filter Data dialog box opens.
In the Fields list, double-click Login ID2.
A filter condition using the Login ID2 field you created in Lesson 1 is added to the filter.
In the filter, right-click Login ID2 and select Edit As Formula.
The Define Formula dialog box opens with the formula Login ID2=Empty displayed.
In the Formula box, select the word Empty.
Select the Functions tab and then expand the Information node.
Double-click the GetUserID function.
Empty is replaced with GetUserID().
Click OK.
The Define Formula dialog box closes and the filter clause displays Login ID2=GetUserID.
Click OK again.
The Filter Data dialog box closes and the NewFilter attribute is displayed in the list view. Next, you will rename the attribute.
To rename the attribute
In list view, right-click the NewFilter attribute and select Rename.
Type UserIDFilter.
Next, you will assign the required attribute properties.
To modify the properties of the attribute
In list view, select the UserIDFilter attribute.
In the Properties window, locate the IsFilter property.
Verify that the IsFilter value is True.
Locate the Hidden property.
Click the Hidden down arrow and then select True.
Changing the Hidden property to True prevents model users from seeing this attribute when they create reports using the model. Because you are using the attribute as a filter to assign security, users do not need to see this attribute when creating their own reports.
In the list view, select the Login ID2 attribute.
In the Properties window, locate the Nullable property.
Because the Login ID2 attribute has the Nullable property set to True, the UserIDFilter attribute must have the Nullable property set to True also. However, this is not a required property for use as a security filter.
Click the Nullable down arrow, and then select True.
To assign an attribute to the security filters collection
In tree view, select the Employee entity.
In the Properties window, locate the SecurityFilters property.
Select the SecurityFilters property and then click the ellipsis (…) button.
The Attribute Reference Collection Editor dialog box opens.
Click Add.
The Security Filter Attributes dialog box opens.
In the Entities list, verify that the Employee entity is selected.
In the Fields list, select the UserIDFilter attribute.
Click OK and then click OK again.
Next, you will save the changes in your model and then redeploy the model to the report server.
To save and deploy the report model
On the File menu, click Save All.
In Solution Explorer, right-click the Adventure Works Model project and select Deploy.
The model is deployed to the report server.
Important
To verify that the model was deployed successfully, you can view the Error List tab. If an error occurs, you will need to troubleshoot the problem before continuing.
Next Steps
You have now created an attribute and assigned it to the SecurityFilters collection. Next, you need to enable model item security in SQL Server Management Studio. See Lesson 3: Enabling Row-Level Security in Report Manager.