Forum Stats

  • 3,852,905 Users
  • 2,264,149 Discussions
  • 7,905,159 Comments

Discussions

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

2

Answers

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,681 Blue Diamond
    edited Mar 28, 2017 6:39PM

    Your use case make much sense now, and it's where we were going...

    You just have to change a bit the order of your actions as it's not exactly how you imagine it:

    3350649 wrote: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

    The idea is that the variable will store the user input. It's quite simple but you can't really do any work on the use input to store something else in a variable. So if your user enter "January" the value of the variable will be "January" and not 01/31/2017.

    Just a note about this (as your example is clearly based on dates): if you have a real time dimensions it would have a column containing the months name, so you make a prompt based on a column which will make things dynamic and avoid issues because of spelling etc. And your time dimension will also have a column with the last day of the month (or a flag identifying by true/false the last day of the month). That would make your situation 100% dynamic and your logic extremely simple.

    If you don't have a proper time dimension (seem to be your case) you will have to make things by hand, with some logic. You can get the current year by using the existing YEAR function in OBIEE, with a CASE WHEN you can translate the month from a name to a number, and for the day you fix the number 1. Why the 1 when you look for the last one? Because every month always start by 1, so your logic will work all the time. You then make some calculations to get the last day of the month. For example for January you get 01/01/2017, you add 1 month (there is an OBIEE function for that) and you get 02/01/2017 and you subtract 1 day (same function used for month) and you get the 01/31/2017.

    You can write all that logic/calculations in a single expression, and that expression will you use variable containing "January" and you use that expression as value for your date in your query.

    It will work just fine, all the functions are normal OBIEE functions. You can try with a empty analysis and just displaying the value of the variable first and adding your logic in the column formula to test it and make sure you get the good one. When it works you can then use it as filter.

  • Eimis Pacheco
    Eimis Pacheco Member Posts: 34
    edited Mar 28, 2017 7:30PM

    Hi Gianni,

    I already applied this logic:

    " with a CASE WHEN you can translate the month from a name to a number, and for the day you fix the number 1. Why the 1 when you look for the last one? Because every month always start by 1, so your logic will work all the time. You then make some calculations to get the last day of the month. For example for January you get 01/01/2017, you add 1 month (there is an OBIEE function for that) and you get 02/01/2017 and you subtract 1 day (same function used for month) and you get the 01/31/2017."

    I have a time dimension, the problem is that the data (the last day of the month) that I need is not in my time dimension, due to this does not matches the information of the other dimensions.

    So I need to look for it in an external table in my database, but I do not have the idea on how to store this last day in a variable to use in my query and then to get the result returned by this query to multiply per the amount.

    Sorry, do you have an example of this? with an external table? I hope I am not asking for too much.

    Thank very much, I would appreciate it a lot.

  • Christian Berg-0racle
    Christian Berg-0racle Everything Analytics And Data Member Posts: 9,674 Gold Crown
    edited Mar 29, 2017 2:43AM

    You can be glad Gianni spends so much time on this thread :-)

    Basically I'd like to echo one of his points: MODEL rather than SQL.

    Your case is in most cases a very direct modeling case where you include a time dimension in order to select attributes (as a filter or prompt) which then drive the join in a model-based fashion.

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,681 Blue Diamond
    edited Mar 29, 2017 3:42AM

    First: does it still make sense to reply to this thread and try to give you hints if you just don't care and open new thread with the same question? How to retrieve a value based on a month selected by the user in Analytics

    I already wrote it once! You can't modify the value selected before to store it in a variable, so you simply use the full formula in your query.

    If your things is in an external table you missed a thing in your modelling because, again, OBIEE is not about writing SQL by hand. So make the required links in the model and then simply add the filter.

    The forum is like finding a food recipe online: you have the list of ingredients and the steps (all you got in this thread), but you are the one who must cook it. Otherwise you are looking for a restaurant and it's a totally different thing...

  • Eimis Pacheco
    Eimis Pacheco Member Posts: 34
    edited Mar 29, 2017 10:52AM

    Hahaha Christian,

    I know this "Basically I'd like to echo one of his points: MODEL rather than SQL"

    But as I said before sometimes, just sometimes this is needed, or not for you?, I just don't want to hear what I already Know, I want to hear what I do not know. From the beginning I always say information that is selected by the user in analytics (a selection), I never said log user information (this is not a selection) to use in a direct query.

    But sadly I do not read any solution to my question.

  • Eimis Pacheco
    Eimis Pacheco Member Posts: 34
    edited Mar 29, 2017 11:45AM

    Hi Gianni,

    As I sad to Christian

    I know this "Basically I'd like to echo one of his points: MODEL rather than SQL"

    But as I said before sometimes, just sometimes this is needed, or not for you?

    I want to listen what I do not know, Not what I already Know.

    This is my first time in this Oracle Community, So I am not sure about the rules, Are you a moderator? I think I am free to save myself if I need to, if I do not listen or read what I need to get the solution as I am formulating it.

    If I'm drowning I'm not going to let myself die, right? I do not want to be criticized, I want a real added-value, because to me this is the real purposes of the forum.

    I have the right to look for more help if I am not satisfied with the answers, or not for you? why? If no one wants to answer they have their right not to do it, but I have my right too. When I do not have anything good to say I would prefer not to say nothing. I always add value.

    I told you that I have converted the month on its last day (previously the user has selected a month), I have repeated over and over that I just want to send or use this last day in a query and then to get and used the value return by this.

    I do not know why is too difficult to understand my request, this is driving crazy.

  • Eimis Pacheco
    Eimis Pacheco Member Posts: 34
    edited Mar 29, 2017 3:51PM

    Hello OBIEE Folk,

    I have not gotten a proper answer or possible solution that meet my specific use case, no guru have been my Hero. They say that is not allowed to rephrase my questions to be better addressed in another discussion, I did not know this, is it right? Does my use case to finish here, without a real solution?

    That's sad ,  I did not know this, I thought that this was to build real solutions, and not to get points. If I get the solution I will share this with you.

    Well, maybe I am just unclear about the rules of the OBIEE community as I am new here and I just used it without reading any rule. I hope that the moderator understands my view. I wanted to rephrase my questions to be understood and be seen by different users and in different time zone, for example, in India.

    If someone wants to add real value is going to be really welcome.

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,681 Blue Diamond
    edited Mar 29, 2017 4:40PM

    If you look for rules they are available easily in the FAQ:

    I would say the main one to check is   and .

    The rule to not double (actually you did a triple) posting is because of everybody does this place will be a useless mess. The idea is to be helpful not for the individual but for the community (and the individual asking is the smallest piece of community supposed to find the thread useful), so single threads per topic is the only way to make it useful for the community. (This one is a generic netiquette rule, even if not written in every single forum online)

    Going back to your question: you missed most of the pieces of the "how to ask questions" thread, so you maybe understand why it's quite difficult to understand what you are exactly asking. It started by how to get a value users input and that was covered by the variables. Then it went to January returning the last day of the month of the current year and that was also covered by the logic and OBIEE formulas.

    But in all your answers you never said a single time where do you want to put your piece of SQL. Do you have an opaque view in your RPD? Do you have a DDR? Do you have something else in mind?

    You continue to say all the answers where things you already know, good for you, but then post details of what you tested and tried and so you will have different answers.

    Let me try to reformulate your need for you: you have an analysis based on a subject area. This analysis retrieve a list of amounts for a given list of "categories / types" of accounts or things like that (sales, discounts etc.). Till here everybody is happy the analysis is easy. Now you must bring in a "conversion rate", an exchange rate between the Costa Rica colon and the USD (or the other way round) at a given date and so you can show your sales amounts in a different currency.

    And you are asking how you can get this exchange rate value to use it in your calculations based on the date the user select (input).

    Solution? That's what OBIEE manage easily, the multi currency thing, by lookup tables in the RPD. Yes, we are back to your model which is supposed to cover your needs. That's how OBIEE works and how OBIEE manage multi currency problems.

    You don't want to hear that? Fine, but if you know how OBIEE works you can easily see the issue! You can't mix a piece of analysis coming from a subject area managed by the RPD and a piece of SQL. If you want SQL then you must write your full query to get amounts and exchange rate in a direct database request.

    Other solutions will need you to open the RPD and add things or change pieces of the model there.

    Now feel free to complain that it's not what you asked and you don't want to hear something you know (but how are we supposed to know what you know?) etc.

    PS: don't open a 4th thread, continue here inside, people from everywhere see this thread as it stay on top of the OBIEE space.

    PS2: for OBIEE questions post directly in Business Intelligence Suite Enterprise Edition (OBIEE) as it avoids the mods to move your questions from the generic BI space to this one.

  • Eimis Pacheco
    Eimis Pacheco Member Posts: 34
    edited Mar 29, 2017 5:36PM

    I must breathe.

    So sorry to spend your time, but I did not understand this from you, I did not understand how to put my last day of the month in a variable after the user selects a month.

    "It started by how to get a value users input and that was covered by the variables"

    Please, could you repeat it? if it is possible for you?

    About this:

    "But in all your answers you never said a single time where do you want to put your piece of SQL"

    you right, I did not tell you where because I don't even know where I can do this according to my use case need. Again, Please let me know where can I put it.?

    About this:

    " If you want SQL then you must write your full query to get amounts and exchange rate in a direct database request."

    I am struggling with this, as I said before I would like to know if it is possible to store the value return by a direct database request, Now I am thinking that is not possible to store it, right?

    "multi currency thing" I did not think of this solution for my use case, why? because I have to convert in a single analysis 2 currencies (NIO and CRC)  to create just one statement for my company. It is not just one currency. That's why I thought of a  direct database request and then to store the value in a variable. Is it possible or not? to not insist in the same.

    Honestly, I did not think of opaque view before, I will look into this, but I read that this should be the last option to be used, but I will try.

    I will be posting about my advances, you are welcome (when you can) to answer all of my question of this exact post. 

    Thanks

    Note: I have read the now Community Posting Etiquette  and How to Ask Questions in OTN Spaces . I think I have not been disrespectful, I have been professionally honest, from my point of view I have not broken any rule, according to what I read. well, I thought my question was easy to be understood not that confusing.  I did not read anything about duplicating threads by rephrasing the question.

  • Pedro F
    Pedro F Member Posts: 161 Bronze Badge
    edited Mar 29, 2017 7:50PM
    Eimis Pacheco wrote:...Note: I have read the now Community Posting Etiquette and How to Ask Questions in OTN Spaces . I think I have not been disrespectful, I have been professionally honest, from my point of view I have not broken any rule, according to what I read. well, I thought my question was easy to be understood not that confusing. I did not read anything about duplicating threads by rephrasing the question.

    Let me just add here something quickly. Yes, you have been disrespectful. If you open multiple threads with the same topic, you're being disrespectful. If someone answers you and gives you suggestions and you simply say "I want to listen what I do not know, Not what I already Know", you're being disrespectful. You do realise that you're getting advice from people with a lot of knowledge for free right? They aren't getting paid for this and if you had to pay for help it wouldn't probably be cheap.

    Bottom line here is that despite eventual language barriers that may exist when you're trying to explain your issue or that the answers don't really give you the solution you're looking for, you can't really demand anything here. I know it can be frustrating not getting an answer to an issue but ultimately, this people don't owe you anything and it's your job to make things work. Have a think about that the next time you post a question here or reply to someone who gave you advice and if you think that the language can be a barrier, ask for some help before replying.

    Gianni CeresaAndrew Fomin.
This discussion has been closed.