Restricting calender data based on Year Filter in Dashboard Prompt

User_JL3CW

Hi all,

In my db prompt i have year and date column (calendar) as filters.

How to restrict calendar data based on year selected.

For eg: If i select 2016 as year then my date column (calendar) should display for 2016 only.

How to restrict this?

Regards,

Ram.

Carina Mendes

Hi

You can do it:

In the year prompt, delcare a varable apresentation:

In the data prompt make this:

Example of querie sintax:

SELECT cast("D01 - Data Apuração"."Data Inicial" as date)

FROM "Evento"

WHERE "D01 - Data Apuração"."Número Ano" = @{vYear}

See the result:

Note:
Unfortunately this is only an workaround, when you to aplicate this solution, the date don't can show in calendar format, only list. But I hope that it help you!

Regards,
Carina Mendes.

#Mayur

Hi,

Create Presentation Variable in The YEAR Dimension and pass it in Default Value Selection of Date Dimensions

Like,

Gianni Ceresa

Hi,

As you saw by yourself when you select the "calendar" view (user input = calendar for the prompted column) you lose many of the standard feature. One of the things you lose is to be able to limit / reduce the list of possible values by setting a start/end date or, like in your case, a year.

There isn't a way to do it: if you select "calendar" you can't limit it's values.

If you select another kind of view (choice list, list box or something else) you have the checkbox "limit values by" which does exactly what you want, but you probably already know it, just not the right visualization for your needs. I would just avoid doing things by hand the way Carolina posted when you have a checkbox taking care of that automatically without need of any variable or code (so if you rename columns, change sources etc. it will still work instead of fail because of hardcoded things).

PS: always useful to add your OBIEE version in questions as sometime 11g and 12c works in a different way. In this case the answer is the same for both versions.

Andrew Fomin.

May I ask what is the purpose of this restriction? From my point of view, calendar control allows choosing of a year and then picking of a date of the month for this particular year. You can extract whatever you want from the selected date. So why would you need a separate year dropdown?

User_JL3CW

Hi,

Yes Fomin, year drop down is not required. I need a year presentation variable (year_pv) which holds year from calendar (for example if i select 01/03/2017 from calendar then presentation variable should hold 2017). How to store year value in a presentation variable without year filter.

I need this year pv for my reporting purpose. I am Using OBIEE 11g.

Regards,

Ram.

Gianni Ceresa

Store the full date in a variable and when you need the year use YEAR( your variable ).

Everywhere you needed to use your year variable you can use the formula to get the year from your date variable.

User_JL3CW

Hi,

I have entered date 01/03/2017 in the calender and presentation variable defined as day_pv. In my report i have written in the column like this

year(cast('@{day_pv}' as date)

It's giving the following error:

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 59140] The evaluation of the index 1 expression on row count -1 failed. [nQSError: 46046] Datetime value 2017-01-03 does not match the specified format. (HY000)

Regards,

Ram

asim cholas

Dear Ram,

Your syntax seems to be the issue. Try like below

Year(Date '@{P_Date}{2016-03-03}')

Thanks

Aj

User_JL3CW

Hi asim,

Request you please describe the above formula how it functions with Date function.. It solved my problem.

Regards,

Ram.

Gianni Ceresa

YEAR() to get the year from a date

Date '....' to tell OBI the '....' is a date

@{P_DATE} the value of the variable

{2016-03-03} the default value if the variable isn't set