Cognos 8 date range filter

Business owners wanted a report with a hard coded date range rather than a date range prompt. Customer is always right, especially in an economic downturn. I could not figure out how to do this and after a couple of days of trial and error I got this to work. I hope this saves someone some time.

--get past 30 days
[Business View].[Sales Tracking Transaction].[Sale Date]between _add_days(current_date,-30) and current_date

--get past year
[Business View].[Sales Tracking Transaction].[Sale Date]between _add_Years(current_date,-1) and current_date

posted on Tuesday, August 05, 2008 2:46 PM

Feedback

# re: Cognos 8 date range filter 10/6/2008 8:48 AM Alice Green

Hi I need help! I am trying to add a filter to my report so that it will always pick up the last months figures. I don't want to ever change the date range.

Can you help me?

Alice

# re: Cognos 8 date range filter 10/7/2008 2:49 PM technodrone

Try something like this if you are using sql server:

[Business View].[Referral Tracking Transaction].[Referral Date] between
_first_of_month( _add_months(current_date,-1))
and
_last_of_month(_add_months(current_date,-1))

Thanks to those on the Cognos user group at LinkedIn.

# re: Cognos 8 date range filter 10/17/2008 9:25 AM Tim T

How do I do a filter where I want to bring back all records older than a 180 days from the current date?

# re: Cognos 8 date range filter 11/19/2008 11:26 AM Talam

I need to include a date range in the condition where B.DT_INVOICED should be between
B.BEGIN_DT+7 AND B.END_DT +7

--------------------------
select * from (
select x.*,row_number() over(partition by ACCOUNTING_PERIOD,fiscal_year ) rownumb from
(select distinct A.DT_INVOICED as sfn_month_end_dt, B.ACCOUNTING_PERIOD, B.FISCAL_YEAR

from ksfs.ps_bi_hdr a inner join ksfs.ps_cal_detp_tbl B on
(A.DT_INVOICED BETWEEN B.BEGIN_DT AND B.END_DT)
where (a.bill_status in ('INV', 'FNL'))

Any help will be truely appreciated

# re: Cognos 8 date range filter 11/19/2008 11:55 AM technodrone

Try something like this in a filter. Let me know if it works. This works for sql database, not sure about others.

[AcmeSales].[Sales Transaction].[Sales Date] between
( _add_days(begin_date,7))
and
(_add_days(end_date,7))

# re: Cognos 8 date range filter-get dates older that 180 days 11/19/2008 12:22 PM technodrone

[Business View].[Sales Transaction].[Sales Date] <
_add_days(current_date,-180)

# re: Cognos 8 date range filter 3/5/2009 4:37 PM Rogerio

[Business View].[Sales Transaction].[Sales Date] <
_add_days(current_date,-180 day)

# re: Cognos 8 date range filter 6/10/2009 4:34 PM yasar

hi,
I am trying to figure out a filter for
YTD and MTD for this year and YTD , MTD for the same days of last year
Thanks,
Yasar

# re: Cognos 8 date range filter 7/17/2009 4:53 AM bhavish shah

Does anyone know how to exclude time from the a date field when performing a filter?

# YTD and MTD for this year and YTD , MTD for the same days of last year 7/25/2009 2:35 AM vamsi

Please provide a solution for this iam trying from two days

# re: Cognos 8 date range filter 7/31/2009 12:34 PM Ryan

you can use the extract filter.

extract(year, month, day, [date expression])

Technodrone

News

Article Categories

Archives

Post Categories

Image Galleries

Code hacks

Face Book Profile

LinkedIn Profile

Recommended Products

Feedback