Is possible to set a variable in the query used in the dynamic repository variable init block data s

Eimis Pacheco

Hello OBIEE Community,

I have this specific question, Is possible to set a variable in the query used in the dynamic repository variable init block data source with a value coming from Analytics?

I have the following query to be used in a repository variable init block data source

SELECT CONVERSION_RATE

FROM GL_DAILY_RATES

WHERE TRUNC(GDR.CONVERSION_DATE) =to_date(V_DATE, 'YYYY-MM-DD')

AND GDR.FROM_CURRENCY ='CRC'

AND GDR.TO_CURRENCY ='USD'

AND GDR.CONVERSION_TYPE = '1001'

I want to do something like the example in the image. in my case, I want to set the value of V_DATE with a value coming from Analytics, from BI presentation services. How can I achieve this, if this is possible? I do not know how to set V_DATE with a value coming from Analytics, so I would appreciate having an example regarding this, with the parameters that I have given here if this is possible.

Best Regards

Gianni Ceresa

Did you check the documentation about variables? Using Variables in the Oracle BI Repository

A repository variable has a single value at any point in time. There are two types of repository variables: static and dynamic.

The important thing here isn't the static or dynamic but the first one: a repository variable has a single value at any point in time. When is the variable value defined? When your BI server start and then following the refresh interval defined in the schedule part of the init block.

What does it means? The repository variable will refresh its value if you have somebody online or not, so it's logically impossible to use something coming from the front-end to set something which has no link at all with the front-end.

A repository variable is based on things available in the repository itself.

Eimis Pacheco

Hi Gianni,

You definitely want to be our hero (hahaha). Thank again for your reply,

And yes, I checked this documentation, but, once more time, I just wanted to make sure of this and of all my possibilities as I said in the other thread.

One last question for you, if you don't mind and sorry for repeating (just to confirm)  as you said " so it's logically impossible to use something coming from the front-end to set something which has no link at all with the front-end"

Then, There is not any single way to get a value coming from analytics? is it just for value coming from the same repository?

I just wanted to know if I can take advantage of a sort of trick on this.

Thanks a billion,

Have a nice day.

Gianni Ceresa

You can get values from the front-end back in the RPD, but not using a repository variable. It will be a session variable (also called request variable sometime) and you allow everybody to set it's value. In that case the user can assign a value to this session variable and it's available in the RPD (in columns formulas, where conditions etc.).

Eimis Pacheco

Ok Gianni,

I have done something like that with session variables, but with the log user information like using the system variable UserID, but in my case is not a system variable, it would be a non-system variable, right?, but I am struggling because I do not know how to set a  non-system variable with a value set (or selected by user) by the user.

Could you provide me an example or a link with this? I have looked for something similar but I did not find it.

Thank once more time

Gianni Ceresa

Yes, it's not a system one.

You set it from a prompt, that's why I gave the "second name" also : in the prompt they call it a request variable. But it's just a session variable in the RPD: OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ? [Gerardnic…

Eimis Pacheco

Yes, I know I am a little bit crazy, but your answer make me feel much happy now.

Gianni, Do you think that can I set this request Variable showed above with a variable value? I mean, if I can set myself this request Variable with the value that I want to in a columm formula?

I hope yes, If not, it's OK, I can not ask everything from life.

Regards

Gianni Ceresa

You can, in a way, set it using a variable (so using a variable to set a variable), look at this: OBIEE - How and where can I set a Request variable (SET VARIABLE) ? [Gerardnico]

But it's not something like "take the result of this query, select the value of this column in the first row (or unique row) and assign it to the session/request variable".

Eimis Pacheco

Ok I understand.

I took a look at OBIEE - How and where can I set a Request variable (SET VARIABLE) ? [Gerardnico]

and I did not notice if I can set the request Variable in a column formula (as in the screenshot below ) or somewhere where else apart from With a dashboard Prompt, In a OBIEE logical SQL statement and With the Saw Url (This is from Gerardnico).

What do you think?

Gianni Ceresa

Gianni Ceresa wrote:But it's not something like "take the result of this query, select the value of this column in the first row (or unique row) and assign it to the session/request variable".

that's why ...

You can't.

Eimis Pacheco

Ok, I think we are done here!. To confirm you. Now, I know that it's not possible to set request Variable in a column formula.

Thank very much for your time spending on this.

Have a nice day.