Using the Filter Transform With Tokens

This applies to: Managed Dashboards, Managed Reports

This article shows examples of filtering data using the Filter transform with tokens as filter values, which can include attributes or custom attributes describing the current user for user-dependent filtering.

Note: You can also add parameters to a SQL Select or other select transforms and set them using tokens in a similar way.

Current Display Name token

This walkthrough shows you how to filter data per user by using the Filter transform and the Current Display Name attribute token.

Connect to Data

Create a new data connector to the following SQL Server database table which contains employee and salary information. The goal will be to create a dashboard that lets employees see their own salary only.

Set Up User Accounts

Set up user accounts in Symphony as shown in the figure below. Note that the account display names match the EmployeeName column from the SQL table.

Create a Data Cube

Go to the Explore window and expand the data connector. Locate the Employees table.

Create a new data cube and drag the Employees table to the canvas.

Select the connection link and insert a Filter transform.

Right-click on the filter transform and select Configure.

In the Filter dialog, click the Edit button beside the EmployeeName column.

In the Transform Element dialog, set the Element Operator to Equals.

Instead of hardcoding a filter value, open the Value token menu and select Current Display Name. This token value resolves to the Display Name of the current Symphony user account.

The data cube is now complete and you can check it in.

Create a Metric Set

Next, create a new metric set by dragging the Salary measure and EmployeeName hierarchy from the data cube to the canvas. The resulting table visualization won't display any data though unless you happen to be one of the employee users.

Check in the metric set.

Create a Dashboard

Create a new dashboard by dragging the metric set to the canvas; it will appear as a table visualization without any rows.

Check in the dashboard.

View the Dashboard

Log out of Symphony and log on as one of the employee users. Open the dashboard for viewing and verify the table visualization shows the salary for this user only.

Filtering Out Null Values

There are a couple of ways to filter out Null values from your data using the Filter transform and the Null token.

The first method is to open the Filter configuration dialog, and edit the column containing possible Null values. In the Transform Element dialog, set the Element Operator to Does Not Equal. Then open the Value token menu and set it to Null.

The second method is to set the Element Operator to Equals. Then open the Value token menu and set it to Null and also select the Invert Selection option.

For more information, see:

• Transform: Filter

• Transform: SQL Select

• Managing Tokens

• Use Custom Attributes to Filter Data by User

• Creating Custom Tokens

• Data Cubes

• Create a New Data Cube