Access Filter with manipulated User Attribute

[nestor1]

Hi there,

Is there a way to manipulate a string from a user_attribute to use with an access filter?

I currently receive a attribute in the form of a simple string ‘1.1.1’.
I need append a ‘.%’ to such value, as exemplified below, so I can use in a LIKE operation.

explorer: B{
   extends

   access_filter: {
      field: employee.hierarchy
     user_attribute: CONCAT(hierarchy, ‘.%’)
  }

}

Things to consider:

1. I can't change the received parameter. Receiving it with ‘.%’ would already solve the problem.
2. I can't use sql_always_where, cause this explorer extends another, which already uses it (can't override)

[fabio1 · Accepted solution]

Hi @nestor!

Option 1: When you want to extend another explore, but still refine a sql_always_where or other string/block declarations, you can use often ${EXTENDED} to interpolate the value from the original declaration.

For example:
sql_always_where: ${EXTENDED} AND <additional condition>

(Non) Option 2: Within the access_filter block, there wouldn’t be a way to apply logic/liquid, as it only accepts a direct reference to a provided user attribute

Option 3: If the field referenced in the access_filter>field is a filter-type declaration, you can use its `sql` declaration to customize how the filter is applied. In your use case, I think you can really only express a single simple filter rule this way (rather than leverage {% condition %} tag)
view: employee {
  filter: heirarchy {
    sql: ${heirarchy_dimension} LIKE CONCAT({% parameter heirarchy %},’.%’) ;;
  }
}

[IanT]

Hi @fabio1 its the first I have heard about SUPER and is something I have needed in the past. Is there any more of these useful yet seemingly secretive lookml things somewhere I can read about...like a full list of lookml parameters?

[fabio1]

Hey @IanT . That’s a tough one! I actually thing that “LookML Arcana” would make for a pretty rad article    I’ll have to think about writing that! In the meantime, one pretty cool resource that you can usually find something within if you comb it closely is our Liquid reference

As a side note, in trying to find resources for this, I discovered that we renamed ${SUPER} to ${EXTENDED}, approximately forever ago, even though SUPER still works for backwards compatibility.
 

[nestor1]

Thanks for the answer,  @fabio1.  I read the documentation on ${SUPER} after your first post and ended up with ${EXTENDED}, which solved my problem. I now use an extended sql_always_where instead of the access_filter.