Using Formulas to Configure Row Permissions

Version

Function Description

You may need to configure row permissions for multiple scenarios and FineBI allows you to make flexible expansions by inputting formulas.

There are three steps to set the row permissions, namely, selecting the business field, selecting the filtering logic, and setting the permission field.

Business Field Supporting Formulas 

You can use formulas to select the business field.

Restrictions on Formulas

• Common functions are supported except aggregate functions and analysis functions.

• Fields in the formula can originate from the table whose row permissions are to be configured, and the one-end table of that table.

• Fields in the formula can only originate from the same table. Cross-table fields are not supported.

Permission Field Supporting Formulas

Conditions

Two conditions shall be met to support the formula input of the permission field.

1.  Login Username Field has been configured.

2. The dependency field of the filtering logic is set as Login User Info.

Restrictions on Formulas

• Common functions are supported except aggregate functions and analysis functions.

• Fields in the formula can originate from the user table (a table contains login information fields), and associated tables of the user table.

• Fields in the formula can only originate from the same table. Cross-table fields are not supported.

Scenario: If the Region field is empty, users have permission to view sales data of all regions.

Formula introduction: 

CONCATENATE(Business Table . Field A, Business Table. Field B, Business Table. Field C) In (Login User Info) CONCATENATE(User Table. Field A, User Table. Field B, User Table. Field C)or CONCATENATE("", Business Table. Field B, Business Table. Field C) In (Login User Info) CONCATENATE(User Table. Field A, User Table. Field B, User Table. Field C)

or CONCATENATE(Business Table. Field A, "", Business Table. Field C) In (Login User Info) CONCATENATE(User Table. Field A, User Table. Field B, User Table. Field C)

or CONCATENATE(Business Table.Field A, Business Table.Field B, "") In (Login User Info) CONCATENATE(User Table.Field A, User Table.Field B, User Table.Field C)

or CONCATENATE("", "", Business Table. Field C) In (Login User Info) CONCATENATE(User Table. Field A, User Table. Field B, User Table. Field C)

or CONCATENATE("", Business Table. Field B,"") In (Login User Info) CONCATENATE(User Table. Field A, User Table. Field B, User Table. Field C)

or CONCATENATE(BusinessTable. FieldA, "", "") In (Login User Info) CONCATENATE(User Table. Field A, User Table. Field B, User Table. Field C)

or CONCATENATE("", "", "") In (Login User Info) CONCATENATE(User Table. Field A, User Table. Field B, User Table. Field C)

Final effect:

After user1 logs in to the platform, he/she can view the sales volume of A-type products of all models in New York and the sales volume of all types of products of Model B002 in Florida.

Preparation

Add two users "user1" and "user2" on the User Management interface, and both are Salesman in the Sales department.

Add a business table and a user table to Public Data.

You can download and unzip the example data: Example One.zip.

The username in the user table is identical to the usernames of the two users in the previous step.

Setting Login Username Field

Log into FineBI, choose System Management > Permission Management > Common Permission Configuration, and choose All Departments as the permission carrier.

Select Data Permission, and click Please Select after Login Username Field (Spider). Click the Username field in the User Table, which is the login username of the corresponding user. 

Configuring Data Permissions

Log into FineBI, choose System Management > Permission Management > Common Permission Configuration, and choose Sales department > Salesman as the permission carrier.

Click Data Permission to configure the Use permission of the Business Table for salesmen. 

Configuring Row Permissions

Click the permission setting icon to enter the row permission configuration interface.

Add the first formula.CONCATENATE(Business Table. Field A, Business Table. Field B, Business Table. Field C) In (Login User Info) CONCATENATE(User Table. Field A, User Table. Field B, User Table. Field C)

• Click Add Condition(OR).

• Click Edit Formula to set the business field. The formula field comes from the Business Table, and the formula content is CONCATENATE(State, Product Type, Product Model).

• Set the filtering condition to In(Login User Info).

• Click Edit Formula to set the permission field. The formula field comes from the User Table, and the formula content is CONCATENATE(State, Product Type, Product Model).

Add the second formula.

CONCATENATE("", Business Table.Field B, Business Table.Field C) In (Login User Info) CONCATENATE(User Table.Field A, User Table.Field B, User Table.Field C).

• Click Add Condition(OR).

• Click Edit Formula to set the business field. The formula field comes from the Business Table, and the formula content is CONCATENATE("", Product Type, Product Model).

• Set the filtering condition to In(Login User Info).

• Click Edit Formula to set the permission field. The formula field comes from the User Table, and the formula content is CONCATENATE(State, Product Type, Product Model).

By the same token, add all the formulas.

CONCATENATE(State, Product Type, Product Model)  In(Login User Info) (CONCATENATE(State, Product Type, Product Model)or CONCATENATE("", Product Type, Product Model) In(Login User Info) CONCATENATE(State, Product Type, Product Model)

or CONCATENATE(State, "", Product Model) In(Login User Info) CONCATENATE(State, Product Type, Product Model)

or CONCATENATE(State, Product Type, "") In(Login User Info) CONCATENATE(State, Product Type, Product Model)

or CONCATENATE("", "", Product Model) In(Login User Info) CONCATENATE(State, Product Type, Product Model)

or CONCATENATE("", Product Type, "") In(Login User Info) CONCATENATE(State, Product Type, Product Model)

or CONCATENATE(State, "", "") In(Login User Info) CONCATENATE(State, Product Type, Product Model)

or CONCATENATE("", "", "") In (Login User Info) CONCATENATE(State, Product Type, Product Model)

Effect Display

When user1 logs into FineBI and views the business table in Public Data, this user can view the sales volume of A-type products of all models in New York, and the sales volume of all types of products of Model B002 in Florida.

Scenario: Salesman users can view the sales volume of products with specified product types and product models.

Formula introduction: CONCATENATE(Business Table. Field A, Business Table. Field B) In (Login User Info) CONCATENATE(User Table. Field A, User Table. Field B)

Final effect: After logging into FineBI, user1 can view the sales volume of TV products with the product model A001, and the sales volume of air conditioner products with the product model B002.

Preparation

Add two users "user1" and "user2" on the User Management interface, and both are Salesman in the Sales department.

Add a business table and a user table to Public Data.

Click to download and uncompress the example data: Example Two.zip.

The username in the user table is identical to the usernames of the two users in the previous step.

Setting Field of the Login User Name

Log into FineBI, choose System Management > Permission Management > Common Permission Configuration, and choose All Departments as the permission carrier.

Select Data Permission, and click Please Select after Login Username Field (Spider). Click the Username field in the User Table, which is the login username of the corresponding user. 

Configuring Data Permissions

Log into FineBI, choose System Management > Permission Management > Common Permission Configuration, and choose Sales department > Salesman as the permission carrier.

Click Data Permission to configure the Use permission of the Business Table for salesmen. 

Configuring Row Permissions

Click the permission setting icon to enter the row permission configuration interface.

Click Add Condition.

Click Edit Formula to set the business field. The formula field comes from the Business Table, and the formula content is CONCATENATE(Product Type, Product Model).

Set the filtering condition to In(Login User Info).

Click Edit Formula to set the permission field. The formula field comes from the User Table, and the formula content is CONCATENATE(Product Type, Product Model).

Effect Display

When user1 logs into FineBI and views the business table in Public Data, this user can view the sales volume of TV products with the product model A001, and the sales volume of air conditioner products with the product model B002.