How to use @Prompt() in Select clause of a dimension object

[Former Member]

Hi Experts,

Can somebody please suggest how a workable dimension object could be created with a @Prompt() syntax?

I am converting a Deski report into Webi in BO XI 3.1 SP3 and Oracle 11i environment.

The datasource for the Deski report is a hand-written SQL query that I need to convert into corresponding universe objects.

Some of the columns are based on user prompt selection like:

Add_Months(To_Date(@variable('Enter Date (mm/dd/yyyy)','mm/dd/yyyy'),-3) as Past3MonthsDate,

To_Date (@variable('Enter Date (mm/dd/yyyy)','mm/dd/yyyy') as CurrentDate

etc.

I'm trying to get the CurrentDate object to 'parse' and work correctly in Webi report.

However, I am  facing various errors like:

-  not a valid month,

- Ora-01830: date format picture ends before converting entire input string

- the from clause or where clause of this object cannot be genertaed --- I used another object along with CurrentDate in query panel to get rid of this.

I created a 'Date' type dimension and tested the following:

1. In Select clause:

to_date(@Prompt('Enter Date','A','Class_Name\Date-Object',mono,constrained),'mm/dd/yyyy')

2. In Select clause:

add_months(to_date(@Prompt('Enter Date','D','Class_Name\Date_object',mono,constrained),'mm/dd/yyyy'),0)

3. In Select clause:

Select to_date(@Prompt('Enter Date','D','Class_name\Date_object',mono,constrained),'mm/dd/yyyy hh24:mi:ss') from dual

4. In Select Clause:

add_months(to_date(@Prompt('Enter Date','D','Class_name\Date_object',mono,constrained),'mm/dd/yyyy'),0)

    In Where Clause:

Class_Name\Date_object= @Prompt('Enter Date','D',Class_name\Date_object',mono,constrained)

5.a Created a Oracle Dummy table- Select * from Dual

5.b. Joined the Table_name.Date_column on Dual.Dummy column

5.c. In CurrentDate dimension object put the SELECT clause as  To-Date(@Prompt()) and WHERE clause as Class_name\Date_object =@Prompt()

5.d. However, when I use the CurrentDate object in Webi report's query panel I get a 'unbale to recognize the object format' error, probably since the Dummy column of Dual table is not compatible with the Date column in the dimension table.

It would be very helpful if someone can provide some pointers.

Thanks

Prathamesh

[swati_patil4]

Hi Prathmesh,

I would suggest you to try to create a simple prompt to see whether you can show this at report level or not.

While working at my end, I've tried with this approach and created Date prompts to use in report. It worked. Please note that at my databse level the dates are stored in DD/MM/YYYY format.

Below is the example I could give here:

todate(@Prompt('Enter Start Date(in DD/MM/YYYY - Format)','A',,,,,{'DD/MM/YYYY'}),'DD/MM/YYYY')

Type of this object can be either Character or Date. If we use as Date then it will be easy for any calculation at report level.

If I use this prompt and enter as 31/01/2013 its value,it will display me the same value at report level.

Once you will be able to get the working prompt you can use different datbase functions as per your requirement.

Please let me know if it helps.

Thanks,

Swati

[Former Member]

Thank you for replying, Swati.

I've already tried that as the very first option given in my post. Somehow, it fails to 'parse' with a 'Ora-DBD exception' error.

I know that sometimes even if the object fails to parse it still seems to work at the Webi-end.

But this one is giving a 'not a valid month' error in Webi report.

I'm suspecting that this has to do something with the Oracle's Date setting in Oracle 11i or some changes at the backend and middleware drivers are affecting the behavior.

-Prathamesh

[Former Member]

Hi Prathmesh,

First have you tried converting the Deski report using RCT, it should automcatically create objects in select clause of SQL in the universe.

Also AS CurrentDate is an alias defined which will not work in the Select clause of dimension.

Try creating a Derived table of the SQL vode of Deski , it would work like that .

Thanks,

Avinash

[Former Member]

Hi Avinash,

RCT is not an option since the database and tables are changed in the new system.

I am mocking the CurrentDate-aliased column logic with a dimension object and trying to put the @Prompt() logic in the 'Select' clause to replace @variable() used in the actual SQL.

Derived table is a good option but its preferred to create the required schema and establish context paths between the tables, so as to take care of the future reporting needs.

Thank you for the valuable suggestions.

-Prathamesh