Print Page - Filter for Year as of Last Month

Title: Filter for Year as of Last Month
Post by: CogUser16 on 24 Oct 2016 10:42:26 AM

Hi All,

Wondering if you can help. I'm new to Cognos and previously used Business Objects.

I need a filter for the year as per last month. In busines objects I could create filters within the universe as a sub select statement, I don't believe it's possible in Cognos 10.1

I have a Current Month Determinant and basically want to filter on:

YEAR = (Select YEAR from DIM_DATE where CURRENT_MONTH = -1)

This is because users upload data to our tables for the previous month and therefore want to see data for 2016 in Jan 2017.

Preferably this would be a filter within Framework that can be used within report studio.

Thanks,

Thanks,

Title: Re: Filter for Year as of Last Month
Post by: BigChris on 24 Oct 2016 10:53:21 AM

Hi - are you using a cube, or relational data? Assuming you're using relational data you could perhaps use something like:

Code Select


[YourDateField] between _make_timestamp(year(current_date),1,1) and _last_of_month(_add_months(current_date,-1))

Title: Re: Filter for Year as of Last Month
Post by: CogUser16 on 24 Oct 2016 11:12:45 AM

Hi,

Apologies - I'm using relational and the database server is Oracle 10g.

To clarify the requirement, if I were running the report in July 2016 I would require data for January - June 2016. If I were running in January 2017, I would require data for January - December 2016.

Thanks,

Title: Re: Filter for Year as of Last Month
Post by: BigChris on 25 Oct 2016 02:00:33 AM

Ok, in that case you just need an extra bit of code to deal with January:

Code Select

[YourDateField] between 
_make_timestamp(
    if(month(current_date)=1) then (year(current_date)-1) else (year(current_date))
,1,1) and _last_of_month(_add_months(current_date,-1))

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: CogUser16 on 24 Oct 2016 10:42:26 AM