BIPS Data Model - how to handle a date parameter with a text based list of values

Teresa Modesta

BIP Publisher in Fuision Financials - data model problem - so OBIA version 11.1.1.9 as my issue is entirely in the cloud.

I am trying to work around a prebuilt data model which uses a package to populate its data, so there is a lot that I cannot touch / change.

I need to use an existing prompt with LOV to apply an extra date condition on the data model.

The date is not held as a date in the LOV, it is a period selector in the format 'Nov-2008' - so text.

I have read that to reference the existing parameter I just need to use the syntax &MyPeriodSelector - I know I need to convert my text into a date to allow meaningful comparison

The problem I have is when I try this and put in the value Nov-2008 it errors saying the below; -

oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: java.sql.SQLSyntaxErrorException: ORA-00904: "NOV": invalid identifier

and  prd.calendar_period_open_date <=  to_date(&MyPeriodSelector,'Mon-YYYY')

Can anyone tell me the right syntax to use for this please, I have experimented for some time, but each time I just get a subtly different kind of error.

I also tried with ' ' around the parameter name but then it just treats it as a literal.

thank you,

Teresa X.

Teresa Modesta

Anyone provide any help at all on this please?

In brief;

Text financial period passed via parameter with format "Apr-2018"

I need to change that value into a date in a data model.

Can anyone tell me what syntax I need to achieve this; -

to_date(&MyPeriodSelector,'Mon-YYYY')

Teresa Modesta

I use the below and it does not recognise the variable as a variable

and  nvl(prd.calendar_period_close_date,sysdate+1) >=   cast('01-'||'&MyPeriodSelector' as date)

I remove the speech marks around the variable name and it sees the month from the full variable as a name and errors.

and  nvl(prd.calendar_period_close_date,sysdate+1) >=   cast('01-'||&MyPeriodSelector as date)

Teresa Modesta

Anyone please - anything?

My latest; -

and  nvl(prd.calendar_period_close_date,sysdate+1) >=   to_date('01-'||nvl(:P_PERIOD_FROM,'Jan-1951'), 'DD-Mon-YYYY')

I have reviewed the links that talk about the canonical date format, but as this is entirely within SQL and I am not starting with a date parameter and I am using to_date and a date format then this should work??

Teresa Modesta

Anyone at all with even half a clue on this or at least confirmation that it cannot be done - please?

Teresa Modesta

Still hoping there is an answer on text to date in BIPS?

Teresa Modesta

The manual tells me this

{

Working with Date Parameters

Oracle BI Publisher always binds date column or date parameter as a timestamp object.

To avoid timestamp conversion, define the parameter as a string and pass the value with formatting as 'DD-MON-YYYY' to match the RDBMS date format.

}

This being true then if my text in the parameter is Feb-2019 and I prepend '01-'  and then convert this into a date in sql, converting because I need to compare a range, then this should work, but still it does not.

and  nvl(prd.calendar_period_close_date,sysdate+1) >=   cast('01-'||'&MyPeriodSelector' as date)

Anyone tell me why please?!!

elb666

Sorry you have to work with BIP.  I'm not exactly sure what your limitations are based on

I am trying to work around a prebuilt data model which uses a package to populate its data, so there is a lot that I cannot touch / change.

But I'm wondering if you can create a simple BIP data model and get it to bind your parameter correctly.  For example,

select row_wid

from w_day_d

where 1=1

  and calendar_date >= to_date('01-' || :MyPeriodSelector, 'DD-Mon-YYYY')

  and calendar_date <= sysdate

And create the parameter MyPeriodSelector as follows

When I do this, I am able to get sample data returned and I'm able to create a dashboard:

Teresa Modesta

Thanks for your reply sweetie, really very very kind of you.

Completely puzzled as following your set up perfectly but it does not work this way for me.

I can use sysdate to compare - good

I can use text to match to text - good

But the minute I try to convert text to a date - even though it matches the DD-Mon-YYYY using to_date - it errors with the invalid month message. This is the case even when I have to_date('01-Apr-2019','DD-Mon-YYYY').

Think maybe a bug and I will have to go for an SR.

My limitations - I cannot cook too good - but no one has complained about it yet ;-)

Robert Angel

Have you tried turning it on its head?

You have it in text, why not turn it into a number; YYYYMMDD; this will take some case logic or similar in, conjunction with cast or similar.

Then turn your date on the source table to a number using to_char / to_number and see if it works for you comparing number with number.

It might be slow running, but at least if it works you will know for definite that you have some kind of date conversion error.

Teresa Modesta

Your suggestion worked darling - but like you said it does make it take longer.

Thank you

Don't know why when elb666's suggestion works for him and mine is identical it won't work for me.

Teresa X