Solved

Access Filter with manipulated User Attribute

  • 7 January 2021
  • 4 replies
  • 273 views

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 [A]

   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)
icon

Best answer by fabio 13 January 2021, 21:45

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 %},’.%’) ;;
  }
}

View original

This topic has been closed for comments

4 replies

Userlevel 6
Badge

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 %},’.%’) ;;
  }
}

Userlevel 6
Badge

Hi @fabio 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?

Userlevel 6
Badge

Hey @IanT . That’s a tough one! I actually thing that “LookML Arcana” would make for a pretty rad article  :thinking:  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.
 

Thanks for the answer,  @fabio.  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.