cancel
Showing results for 
Search instead for 
Did you mean: 

Mandatory Prompts

former_member402770
Participant
0 Kudos

Hi All,

Using:

BO 4.1 IDT

Oracle 11G Database

   I am trying to create an Mandatory prompt on the universe side. I have been reading through some material and below is the prompt where there is no association of value object to select for:

I have values in the table as below for both:

Fiscal Year = 2015

Fiscal Month  = 004

1) Let now do prompt for fiscal year in IDT surprised there is no association of objects.

2) Is there any possibility of combining this Fiscal month and Fiscal Year into one prompt as Fiscal Year/period format say eg: APR 2015

I would want to create this kind of format in IDT as shown in Webi Prompt as combination of fiscal month and fiscal year as below:

Appreciate your help..

Thanks and Regards,

Dinya

Accepted Solutions (1)

Accepted Solutions (1)

amitrathi239
Active Contributor
0 Kudos

Hi,

See the sample.

Calendar Month & Calendar Year are seperate objects and clubbed in the single.

Create one more object Month/Year Object.

to_date(CONCAT(@Select(Date\Calendar Month),@Select(Date\Calendar Year)),'MM-yyyy')

Define the prompt on this.

Amit

former_member402770
Participant
0 Kudos

Hi Amit,

Thanks for the response back.. I also would like to know is this

BI Query Selection String Type:

                                                                 Text Display          Key

   Fiscal Year/Period (Selection Options): APR 2015           (2015004)


Oracle Report Level Query Filter Selection Numeric Type:

  Fiscal Year   : 2015

  Fiscal Month : 4

Merging the above prompt selection of BI and Oracle into One report should display one selection like below:

   Fiscal Year/Period (Selection Options): APR 2015

Appreciate your hep..

Thanks,

Dinya.

amitrathi239
Active Contributor
0 Kudos

Hi,

in this case you need to convert the numeric to string.After that when user enter APR 2015 both will work.

Amit

former_member402770
Participant
0 Kudos

Amit,

  I need some hands on this at the report level step by step.

Appreciate your help..

Thanks,

Dinya.

amitrathi239
Active Contributor
0 Kudos

Hi,

if you see you have single object Fiscal Year/Period (Selection Options): APR 2015    in BEx query.two separate objects for month and year from universe.also data type is different fro BEx and Universe .


First you need to combine the month and year in the single object and values needs to get like APR 2015 in this object.


After that if you will keep the same text prompt text in the BEx query prompt and universe then users will get the one prompt to enter the values.


other option is if you have two objects in the BEx one for month and one for Year and same in the universe then create prompt at webi query level and keep the prompt text same.


Amit







former_member402770
Participant
0 Kudos

Amit,

  I would not like to change anything on the query side or Universe side.

Bex I would not change, let this be Fiscal Year/Period (Selection Options):

In BO Webi, iam trying to take Oracle Selection Fiscal Year and Fiscal Month, combine and show as single prompt as like bex query prompt above. Have challenge on the conversion and make it single object to sync with bex prompt which I am not clear..

Thanks,

Dinya.

amitrathi239
Active Contributor
0 Kudos

HI,

in the universe create one more object based on the month and year object.

year/moth

to_date(CONCAT(@Select(Date\Calendar Month),@Select(Date\Calendar Year)),'MMM yyyy')


after this this values are coming like APR 2015 in this object.


if yes then add the prompt in the webi query on this and keep the same prompt text as BEx query.


Amit

former_member402770
Participant
0 Kudos

Hi Amit,

  Using Oracle 11g as source, Just little trick to make it to work. iam getting this error while populating the newly created object as per your suggestion values:

Error:
Failed to execute: SELECT DISTINCT to_date(CONCAT(( WW.FISCAL_MONTH ),( WW.FISCAL_YEAR )),'MMM yyyy')
FROM
  WW
WHERE
  ( WW.FISCAL_YEAR='2015'  )


Cause of Error
ORA-01821: date format not recognized

Thanks,

Dinya.

amitrathi239
Active Contributor
0 Kudos

Hi,

what is the syntax of object in the Universe? is object parsing in the universe.

try with to_date(CONCAT(@Select(Date\Calendar Month),@Select(Date\Calendar Year)),'Mmm yyyy')

Amit

former_member402770
Participant
0 Kudos

Hi Amit,

As per your code:

:

While Preview the values:

amitrathi239
Active Contributor
0 Kudos

Hi,

select the datatype Date instead of string in the universe and try.

Amit

former_member402770
Participant
0 Kudos

Amit,

Same error i tried changing type to date, datetime all datatype. iam using oracle 11g db.

Thanks,

Dinya

amitrathi239
Active Contributor
0 Kudos

Hi,

use trunc function and see.

trunc(to_date(CONCAT(@Select(Date\Calendar Month),@Select(Date\Calendar Year)),'Mmm yyyy'))


check where you can use trunc function.I don't have system to check now.


Amit

former_member402770
Participant
0 Kudos

Amit,

Tried modifying the sql multiple ways, same error tried even searching the forum cant get an single clue.

Thanks,

Dinya.

amitrathi239
Active Contributor
0 Kudos

Hi,

try with this.

trunc(to_date(CONCAT(@Select(Date\Calendar Month),@Select(Date\Calendar Year)),'Mmm yyyy'),'Mmm yyyy')


Amit

former_member402770
Participant
0 Kudos

Amit tried all the possibilities even changeing date as the error is of date format..

Thanks,

Dinya

Answers (0)