Oracle Business Intelligence

Products Banner

Common months between two years

Received Response
21
Views
5
Comments

Hi,

I have requirement like my report is filtering data for 2 years (current year and previous year).

Using presentation variable to filter the report like this: @{year_pv} and @{year_pv-1}.

I need to further filter the report based on months.

for eg: my current year is 2016 and current month is for eg: Aug. then my report should filter data January to August for both the years (2015 and 2016).

2015 year is having all the months (Jan-Dec). How to filter my report based on this criteria?

Regards,

Ram.

Answers

  • Joel
    Joel ✭✭✭✭✭

    You'll need to hold the current reporting month as an additional presentation variable (in your example, this would be August). You'll then need to include this in your filter:

    Where year between 2015 and 2016 and month between 1 and 8 (assuming your time dimension has month numbers.

    Sent from my iPhone

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Hi,

    In this requirement user selects year only, there is no month filter to use.

    Any other way to achieve this.

    Regards,

    Ram.

  • Joel
    Joel ✭✭✭✭✭

    So in your previous example, where does August come from?

    Sent from my iPhone

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    HI,

    The report runs on a day and month which it belong to. If my report runs today (31 Dec) then month belongs to December (Current Month). Current year it will take from (JAN-DEC) similarly for the previous year it should take (Jan-Dec).

    regards,

    ram.

  • Joel
    Joel ✭✭✭✭✭

    Then you can derive the month using MONTH(CURRENT_DATE) which will give you 12 for December.

    So your filter will be where MONTH BETWEEN 1 AND MONTH(CURRENT_DATE)

    Sent from my iPhone