Date as a dropdown in report filter — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Date as a dropdown in report filter

Received Response
61
Views
8
Comments
Maraprik
Maraprik Rank 4 - Community Specialist
edited Aug 13, 2024 6:03PM in Oracle Analytics Cloud and Server

Hey Gurus,

Customer is interested in having a dropdown for a field which is a date datatype. By default all dates are given a calendar in the filter by obiee.

Converting date to CHAR would give us the dropdown option instead of the calendar. this will lead with issues in filtering.

example of calendar in filter

pastedImage_0.png

instead of the calendar in the filter value they want a drop down like this

pastedImage_2.png

When we try to filter it using this it gives wrong values because of the character datatype. They want this because discoverer does this

O713987.INVOICE_PERIOD = TO_DATE('20130101000000','YYYYMMDDHH24MISS')

it somehow changes the value passed from the prompt to date datatype. Not sure how.

Please tell me if we can do something like this in OBIEE.

Problem

1. they dont want calendar

2. filtering should work

OBIEE 12.2.2.1

DB Oracle 11g R2

PLease let me know

Answers

  • Sherry George
    Sherry George Rank 7 - Analytics Coach

    You may try the double column feature in OBIEE. I haven't used it myself but worth taking a look. More info at Gerardnico site.

    OBIEE 11G - Double column (Descriptor Id) [Gerardnico]

    Others might have better idea.

  • Maraprik
    Maraprik Rank 4 - Community Specialist

    Hey Sherry,

    thanks for the response,

    I tried using a date field as the descriptor and since I use this as the descriptor following happens

    pastedImage_0.png

    filter is still a calendar. not able to work it out into a dropdown.

    Can someone help. confirmation of the possibility ?

    regards

    Kriparam

  • Sherry George
    Sherry George Rank 7 - Analytics Coach

    Hi Kriparam,

    I did a quick test in my local and I see that the Calendar is displayed when the filter operator is 'is equal to/is in', but if you change it to 'begins with' it works as per your requirement.

    pastedImage_0.png

    Edit:

    Adding that, the DESC_ID is a logical column based on the original date column which is CAST as char.

    pastedImage_0.png

    pastedImage_1.png

  • Ahmad Al-Baik
    Ahmad Al-Baik Rank 3 - Community Apprentice

    Dear;

    Please try to do this :

    Make a prompt contains the needed field as presentation variable, but please to make the date field as CHAR:

    pastedImage_2.png

    then use this variable in filtering the Analysis:

    cast("DIM_DATE"."CURRENT_DAY" as char) =('@{DATE_FILTER}')

    This will solve your problem,

    Regards,

    Ahmad...

  • Maraprik
    Maraprik Rank 4 - Community Specialist

    Hey Sherry,

    Appreciate your taking time out.

    pastedImage_0.png

    i used an evaluate on the date field to convert it to char in the RPD and i brought the format to - DD-MON-YYYY. when i use it as descriptor it shows up as the above. not sure why. then i try filtering it, it throws an error. problem is they also want to do a greater than or less than analysis. i have tried other possibilities as well.

    like for timestamp datatype. the filter automatically gives a drop down. but in the filter selection also they want the DD-MON-YYYY like this

    pastedImage_1.png

    For this i have to again convert it to character and the whole thing goes to dumps.

    Please see if you can find something. i shall get back if I find something. I just want to be sure to go back and tell them that it wont work.

  • Maraprik
    Maraprik Rank 4 - Community Specialist

    thanks so much Ahmed for your reply.

    User has ad hoc access. they want to create their own reports. so in answers when they use this field they do not want to see a calendar because the field only contains 2 dates per month. beginning and end. they are comfortable with discoverer doing this for them. They want the same in OBIEE. i am trying to make sure that this is not possible or possible before going back to them.

    thanks again

  • Sherry George
    Sherry George Rank 7 - Analytics Coach

    Why do you have to use Evaluate in rpd, just a CAST <your column> AS CHAR will do the trick. Make sure your date column  datatype is 'DATE'  and not datetime in rpd and give it a try.

    Changing the date picker to a drop down for the filter doesn't seem to be an out of the box option.

  • Ahmad Al-Baik
    Ahmad Al-Baik Rank 3 - Community Apprentice

    Dear;

    In my case no calendar will appear, if you note the shape above its "Chose List" but if they want to make their own Ad hoc, it'll hard,

    Wish you the best,

    Ahmad..