Filter for Year as of Last Month

[CogUser16]

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,

[BigChris]

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

Code Select Expand


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


[CogUser16]

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,

[BigChris]

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

Code Select Expand

[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))