· Nov 20, 2019

DeepSee filter based on $USERNAME or other ObjectScript expression

How do I best create a filter in DeepSee to restrict query results for the current login, that is where the results are filtered by a Dimension compared to the ObjectScript special variable $USERNAME or an ObjectScript expression that restricts what the current user is allowed to see?

Discussion (5)2
Log in or sign up to continue

Hi Kevin,

This can be achieved by applying a dynamic filter spec to your cube/subject area. This can be done within the %OnGetFilterSpec method. Below you will find a sample that can be used against the HOLEFOODS Sample:

ClassMethod %OnGetFilterSpec(pFilterSpec As %String) As %String
Set pFilterSpec=""
If $USERNAME="Peter" {
Set pFilterSpec="[PRODUCT].[P1].[PRODUCT CATEGORY].&[Candy]"
ElseIf $USERNAME="Kevin" {
Set pFilterSpec="[PRODUCT].[P1].[PRODUCT CATEGORY].&[Pasta]"
Quit pFilterSpec

In this sample, when I log in, I will only see data related to Candy sales. When you log in, you will only see data related to Pasta sales. If anyone else logs in, they will see all categories.

Subject Areas are typically used on top of cubes when you want to filter the data like this. You can also do this directly on the cube, but it is easier to create multiple subject areas with different criteria if you stick to only using Subject Areas for this.

The logic and specs can be more complex if needed. I usually go to Analyzer and put together the necessary criteria and use the generated MDX directly or as a guide for my filter spec.