Forum Stats

  • 3,838,291 Users
  • 2,262,351 Discussions
  • 7,900,576 Comments

Discussions

Categories

How to retrieve a value based on a date selected by the user in OBIEE

Eimis Pacheco
Eimis Pacheco Member Posts: 34
edited Mar 30, 2017 2:40PM in Business Intelligence Suite Enterprise Edition (OBIEE)

Hello Oracle Community,

I have been investigating about how to retrieve a value based on a date selected by the user in analytics, for instance, let's say that the user has selected the date 01//31/2017, I would like to know how can I do to retrieve a value based on that date.

I know that I can use a dynamic repository variable, but the thing is that I do not know how can I get the date value that comes from analytics to be used in my dynamic repository variable?

I would really appreciate to know this.

Thank you very much for your help.

Tagged:
Gianni CeresaAndrew Fomin.Eimis PachecoThomas Dodds
· Share on TwitterShare on Facebook
«13

Answers

  • Thomas Dodds
    Thomas Dodds Member Posts: 1,698
    edited Mar 28, 2017 1:27PM

    Your ask is a little unclear - what is the end game / what are you expecting OBIEE to do?

    · Share on TwitterShare on Facebook
  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,640 Blue Diamond
    edited Mar 28, 2017 1:27PM

    Hi,

    Are you looking to use a presentation variable as filter condition in your analysis?

    Not sure why you talk about repository variable as it's probably the last option ...

    You add a prompt on your page, ideally the prompt just select a value from your time dimension so you avoid all the issues of formats etc. and all the analysis filtering on the same column as the prompt will automatically take the value of the prompt.

    If not you can always set the value of the prompt to a presentation variable and use this variable in the filter conditions of your analysis.

    · Share on TwitterShare on Facebook
  • Eimis Pacheco
    Eimis Pacheco Member Posts: 34
    edited Mar 28, 2017 1:47PM

    Hi Gianni,

    Thank you for your kind answer,

    I was talking about Dynamic Repository Variables, because the value that I want to retrieve is not in my time dimension, it is somewhere else.

    I saw this sample, I know that NQ_SESSION.USERID is a session variable, well, I want to do something similar, like NQ_SESSION.DATE,  but I do not know how to get this Date variable selected by the user.

    SELECT NAME, VALUE

    FROM RW_SESSION_VARS

    WHERE USERID='VALUEOF(NQ_SESSION.USERID)'

    Do you understand my need now?

    Thank you once more time

    · Share on TwitterShare on Facebook
  • Eimis Pacheco
    Eimis Pacheco Member Posts: 34
    edited Mar 28, 2017 1:52PM

    Hi Tomas,

    Thank you for your replay,

    I want to retrieve a value that is not included in any of my dimension table, this is outside in my database, so, I thought of Dynamic Repository Variables,

    I saw this sample, I know that NQ_SESSION.USERID is a session variable, well, I want to do something similar, like NQ_SESSION.DATE,  but I do not know how to get this Date variable selected by the user.

    SELECT NAME, VALUE

    FROM RW_SESSION_VARS

    WHERE USERID='VALUEOF(NQ_SESSION.USERID)'

    Do you understand me now, or  need more details ?

    Thank you in advance for your kind help,

    · Share on TwitterShare on Facebook
  • Thomas Dodds
    Thomas Dodds Member Posts: 1,698
    edited Mar 28, 2017 2:17PM

    If I understand correctly, based on logged in user you want to select a date from a table in another database that is associated with that user id?

    If so, then you need a connection in the RPD to this other database and you need to set up an initialization block that fetches the date based on the user id into a session variable.

    Example (so you can tell me if I'm getting it):

    user = DDuck

    DDuck logs into OBIEE and the initialization block queries the HR table to find his hire date:

    SELECT hire_date FROM tbl_hires WHERE user_id = 'DDuck'

    Finds 6/9/1934  (and yes that's when the world got Donald Duck)

    That date gets put into NQ_SESSION.USER_HIRE_DT ... 

    · Share on TwitterShare on Facebook
  • Eimis Pacheco
    Eimis Pacheco Member Posts: 34
    edited Mar 28, 2017 2:44PM

    Hi Tomas,

    I want to something similar, my exact example is the following:

    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'

    in this case, I am going to return just one conversation rate with this query, The problem is that I do not know how can I put a dynamic variable in the place of V_DATE

    in the example that you showed me "SELECT hire_date FROM tbl_hires WHERE user_id = 'DDuck'" there is a system session variable that I can use "USERGUID", but In my case, V_DATE is a filter enter by the user in analytics, so really I do not know how I can replace this value V_DATE that come from analytics to filter my query.

    Thank again for your kind help.

    · Share on TwitterShare on Facebook
  • Christian Berg-0racle
    Christian Berg-0racle Everything Analytics And Data Member Posts: 9,648 Gold Crown
    edited Mar 28, 2017 3:10PM

    First of all you could have said all that in the original question....and more since your question STILL doesn't make sense.

    Your SQL now all of a sudden has ZERO to do with the user anymore. What gives?

    · Share on TwitterShare on Facebook
  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,640 Blue Diamond
    edited Mar 28, 2017 3:57PM

    Hi,

    Again I believe you just missed part of my answer as it still apply ....

    You add a prompt, in your prompt instead of column prompt you select variable prompt and a presentation variable is more than enough.

    Then you have a query: the idea of OBIEE is not to write SQL but to model things and let OBIEE generate the queries. So you build your analysis and use the presentation variable as filter. Done !

    Capture.PNG

    · Share on TwitterShare on Facebook
  • Eimis Pacheco
    Eimis Pacheco Member Posts: 34
    edited Mar 28, 2017 4:48PM

    Right, the value that I need has nothing to do with the user information, as I said at the beginning it is based on a date (conversion date) that this user has selected, I am a little confused as well about why my question is so unclear because despite the fact that OBIEE is not to write SQL, sometimes this is needed, to write direct queries to the database based on selection.

    I have done something similar before (or a least for me is similar), but using session variable in the repository with the user log information, but due to this is not user information, this is a value that is selected I am asking how can I do this?, because this is not a system variable that I have available .

    Maybe I have mixed up terms (I am not a senior), it does not matter, I just need the solution to do this somehow. Do you think prompt variable is the answer?

    Thanks

    · Share on TwitterShare on Facebook
  • Eimis Pacheco
    Eimis Pacheco Member Posts: 34
    edited Mar 28, 2017 5:19PM

    Hi Gianni,

    Thank you very much. I am going to give you more details about what I try to do, to be more exact and not missed relevant information

    for example. when a user select a month, in this case, January = 01/31/2017, or February  = 02/28/2017, internally I want to convert this month to the last day of that month, then I want to use/catch this day somewhere, in a variable (I do not know how and where), then to use this variable in this query:

    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'

    and finally, multiply the amount showed below per the CONVERSION_RATE value. I am praying to be understood, hahahaha,

    I this possible with prompt variable or maybe I am changing the situation.

    pastedImage_0.png

    · Share on TwitterShare on Facebook
This discussion has been closed.