Unable to see 2022 using Calendar/Date functions

TKQ5Q

Dear all, 

I am using Oracle Business Intelligence Enterprise Edition 11.1.1.7 (OBI).

I have used Calendar/Date functions in "Edit Formula" to show 2 column

1) Name of Month

2) Year

In the result view, I am able see these 2 columns.

However, when I used them under table prompts section.

There is no option for for month Jan and Year 2022.

Can anyone help me, thank you for your advice in advance.

Gianni Ceresa

Do you have any data for January 2022?

OBIEE doesn't generate rows (except if you really trick it into doing it), it applies functions to your existing data.

And if you have data for 2022, like for everything else logs exists: look at the generated queries and find why 2022 doesn't exist there and fix as needed.

TKQ5Q

Yes, I have data for Jan 2022.

It actually appeared in the result view column as well.

But when I drop it under 'Table prompt', it didn't give any option for Month Jan or Year 2022.

So I was wondering if I did anything wrong.

Gianni Ceresa

But when I drop it under 'Table prompt', it didn't give any option for Month Jan or Year 2022.

So I was wondering if I did anything wrong.

You didn't say a word about how you did it exactly, so difficult to say...

A random guess, because you have some 2022 data is that yes, you did something wrong.

TKQ5Q

Hi, When i view the result, the date column are showing 2021 & 2022 as follow

But when I drag it under layout, table prompt, it does not have the month Jan & 2022

So i am not sure what I did wrong?

[Deleted User]

You put the month first and then the year which doesn't make sense. Hierarchies / rollups with different levels work from largest to smallest / most aggregated to most detailed:

Year -> Month -> Day

Planet -> Continent -> Country -> Region -> Zipcode

A table prompt interprets that this way as well from left to right (LTR) which is the default reading direction for languages like English.

So the table prompt first selects the month as you instructed it to do and then shows the year belonging to that month. Meaning: "Dec" -> 2021 because you have that and nothing else.

The year has to go first because it's hierarchically above the month. Same would go for a Date which is more specific: Choosing 05-01-2022 (today) and then trying to change the table prompt for "Year" would always and only show 2022 because 05-01-2022 is inside 2022.

Or using my other example: If you have already chosen a Zipcode which resides inside a region which resides inside a country and then you want to change the continent? Doesn't work.

Table prompts are in-line objects meaning they are an integral part of the analysis and by default act hierarchically based on the data set which the analysis utilizes.

So go Year -> Month and you will see it.

TKQ5Q

Thank you so much. I re-arranged the order in the table prompt and it work.

😆

[Deleted User]

https://community.oracle.com/tech/apps-infra/discussion/comment/16821154#Comment_16821154

If this answers your question, please close it according to community standards so that other users can benefit from it as well.