REDWOOD

Introduction

Oracle Analytics external datasets offer support for role-based filters, but these filters don’t accommodate session variables within their expressions. In this blog post, we will explore a method to use session variables with external datasets to restrict content.

Restrict Data in External Datasets Using Session Variables

To restrict dataset content based on a session variable, follow these steps:

Create a new calculated column in the dataset that stores the value of the session variable. Note that data profiling won’t display the session variable’s value.

Add Session Variable Column

                                                                        Fig. 1: Add session variable column

Toggle the filter icon on the header to enable the role-based filter for the dataset. Then click Add Role and select the role to which you want to apply the data restriction.

Enable Filter

                                                                                         Fig. 2: Enable filter

Apply the application role filter using a condition that references the session variable column created in the dataset.

Add Data Filters

                                                                                         Fig. 3: Add data filters

You can set different conditions based on various roles. For example, in the screenshot below, we restrict data for all authenticated users based on the session variable “Username Session Variable” to filter Manager Usernames in the dataset, but we allow full access to administrators (e.g., ‘1=1’).

Admin Data Filter

                                                                                       Fig. 4: Administer data filter

This setup can also accommodate multiple session variables in the filter expression for more complex requirements. For instance, you can create columns using non-system session variables that store user-specific details like Sales Region or Department. These columns can then be used to dynamically filter the region or department information from the dataset.

Non System Session Variable

                                                                            Fig. 5: Non-system session variable

Advanced Filters

                                                                                         Fig. 6: Advanced filters

Call to Action

Try out this setup on your datasets that need data restrictions based on session variables and thoroughly test your workbooks. You can use both simple and complex expressions with this configuration to meet your specific requirements.

If you have questions, post them in the Oracle Analytics Community and we’ll follow up with answers.

REDWOOD