Oracle Business Intelligence

Products Banner

Restricting calender data based on Year Filter in Dashboard Prompt

Received Response
177
Views
12
Comments

Hi all,

In my db prompt i have year and date column (calendar) as filters.

How to restrict calendar data based on year selected.

For eg: If i select 2016 as year then my date column (calendar) should display for 2016 only.

How to restrict this?

pastedImage_0.png

Regards,

Ram.

Answers

  • Hi

    You can do it:

    In the year prompt, delcare a varable apresentation:

    pastedImage_1.png

    In the data prompt make this:

    pastedImage_3.png

    Example of querie sintax:

    SELECT cast("D01 - Data Apuração"."Data Inicial" as date)

    FROM "Evento"

    WHERE "D01 - Data Apuração"."Número Ano" = @{vYear}

    See the result:

    pastedImage_4.png

    Note:
    Unfortunately this is only an workaround, when you to aplicate this solution, the date don't can show in calendar format, only list. But I hope that it help you!

    Regards,
    Carina Mendes.

  • #Mayur
    #Mayur ✭✭✭✭

    Hi,

    Create Presentation Variable in The YEAR Dimension and pass it in Default Value Selection of Date Dimensions

    Like,

    pastedImage_0.png

  • Hi,

    As you saw by yourself when you select the "calendar" view (user input = calendar for the prompted column) you lose many of the standard feature. One of the things you lose is to be able to limit / reduce the list of possible values by setting a start/end date or, like in your case, a year.

    There isn't a way to do it: if you select "calendar" you can't limit it's values.

    If you select another kind of view (choice list, list box or something else) you have the checkbox "limit values by" which does exactly what you want, but you probably already know it, just not the right visualization for your needs. I would just avoid doing things by hand the way Carolina posted when you have a checkbox taking care of that automatically without need of any variable or code (so if you rename columns, change sources etc. it will still work instead of fail because of hardcoded things).

    PS: always useful to add your OBIEE version in questions as sometime 11g and 12c works in a different way. In this case the answer is the same for both versions.

  • Andrew Fomin.
    Andrew Fomin. ✭✭✭✭✭

    May I ask what is the purpose of this restriction? From my point of view, calendar control allows choosing of a year and then picking of a date of the month for this particular year. You can extract whatever you want from the selected date. So why would you need a separate year dropdown?

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Hi,

    Yes Fomin, year drop down is not required. I need a year presentation variable (year_pv) which holds year from calendar (for example if i select 01/03/2017 from calendar then presentation variable should hold 2017). How to store year value in a presentation variable without year filter.

    I need this year pv for my reporting purpose. I am Using OBIEE 11g.

    Regards,

    Ram.

  • Store the full date in a variable and when you need the year use YEAR( your variable ).

    Everywhere you needed to use your year variable you can use the formula to get the year from your date variable.

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Hi,

    I have entered date 01/03/2017 in the calender and presentation variable defined as day_pv. In my report i have written in the column like this

    year(cast('@{day_pv}' as date)

    It's giving the following error:

    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 59140] The evaluation of the index 1 expression on row count -1 failed. [nQSError: 46046] Datetime value 2017-01-03 does not match the specified format. (HY000)

    Regards,

    Ram

  • asim cholas
    asim cholas ✭✭✭✭✭

    Dear Ram,

    Your syntax seems to be the issue. Try like below

    Year(Date '@{P_Date}{2016-03-03}')

    Thanks

    Aj

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Hi asim,

    Request you please describe the above formula how it functions with Date function.. It solved my problem.

    Regards,

    Ram.

  • YEAR() to get the year from a date

    Date '....' to tell OBI the '....' is a date

    @{P_DATE} the value of the variable

    {2016-03-03} the default value if the variable isn't set

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Hi,

    I have asked to describe the formula because of

    1. In dashboard prompt, my selection is in MM/DD/YYYY format, and if i use PV in my report column it is giving correct result i.e in MM/DD/YYYY format.

    When i use same presentation variable in the filter of the report it is giving YYYY-MM-DD format.

    How did this formula has given the correct result?

    Regards,

    Ram.

  • asim cholas
    asim cholas ✭✭✭✭✭

    Dear Ram,

    The value we pass from dashboard prompt will be considered as a text and we need to convert it to date in column formula, For that you need 'Date' Function and which supports only the above syntax. .. May be @Christian Berg Or @gerardnico will be able to explain it further.

    Thanks

    Aj