Date as a dropdown in report filter

Maraprik · Apr 5, 2017 3:48PM

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

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

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

Sherry George · Apr 5, 2017 4:38PM

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 · Apr 5, 2017 4:42PM

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

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 · Apr 5, 2017 6:25PM

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.

Edit:

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

Ahmad Al-Baik · Apr 6, 2017 7:37AM

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:

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 · Apr 6, 2017 2:17PM

Hey Sherry,

Appreciate your taking time out.

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

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 · Apr 6, 2017 2:19PM

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 · Apr 6, 2017 3:01PM

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 · Apr 9, 2017 6:39AM

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..

Oracle Analytics Cloud and Server

Categories

Date as a dropdown in report filter

Answers