How to use user profile attribute as query parameter?

Question

I have users worked in different department. User should see only data of his department.

I create Profile Attribute "department"  and can assign it for different user. Now I want use value of this attribute in SQL-query.

How can I refer on it in SQL?

Answer 1

Hi,
I suppose that you are creating a new dataset using the SQL language. If you want to use the profile attribute inside the query you can use this syntax (I'm pasting the text directly from the help that you can get clicking on the magic wand in the dataset administration): 

In a datasets the user can insert parameters or profile attributes, single or multi value.
The correct syntax to insert a parameter is $P{parameter_name}.
The correct syntax to insert a profile attribute is ${attribute_name}.
Remember that for any parameter added to your dataset, you need to add this parameter in the parameters table. 

---

SCRIPTS: Every script must be written using Groovy or Javascript languages. 

The script must return an XML string containing a list of values with the syntax shown below. 
<ROWS>
  <ROW value="value1" ... />
  <ROW value="value2" ... />
  ...
</ROWS>

---

Knowage already fournishes some Groovy and Javascript functions that return the value of a profile attribute:

• returnValue('${attribute_name}'): Returns the unique value of the profile attribute with name: attribute_name 
  Example: returnValue("${single_value_attribute}")

  ---

• getListFromMultiValueProfileAttribute('${attribute_name}'): Returns the list of values of a multi value profile attribute.
  Example: getListFromMultiValueProfileAttribute("${multi_value_attribute}")

  ---

• getMultiValueProfileAttribute('${attribute_name}', 'prefix', 'newSplitter', 'suffix') : Returns list of values of a multi value profile attribute, preceded by a prefix, separated by the new splitter and followed by a suffix 
  Example: getMultiValueProfileAttribute('${multi_value_attribute}', "in (" , "," , ")" )

Answer 2

Hi,

this example is the "where condition" of a dataset created for a postgres table/view. 

    WHERE

fk_sensori_fornitori = '${fk_sensori_fornitori}'
    and 
fk_qmm_man_cat_ogg = $P{ogmi}

as you can see there are 2 type of parameters:

• '${fk_sensori_fornitori}' - this param works with user profile attributes
• $P{ogmi} - this parameter works as a filter that a user can choose through an analytical driver

Hope it help.

M.

Comments

Thats awesome google flights