Cognos 8 date range filter

<< Detailed Error Logging | Home | ASP.NET Validation Controls- Good but sometimes I need more >>

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

Share This Post:

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

Comments on this entry:

# re: Cognos 8 date range filter

Left by Alice Green at 10/6/2008 8:48 AM

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

Left by technodrone at 10/7/2008 2:49 PM

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

Left by Tim T at 10/17/2008 9:25 AM

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

Left by Talam at 11/19/2008 11:26 AM

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

Left by technodrone at 11/19/2008 11:55 AM

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

Left by technodrone at 11/19/2008 12:22 PM

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

# re: Cognos 8 date range filter

Left by Rogerio at 3/5/2009 4:37 PM

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

# re: Cognos 8 date range filter

Left by yasar at 6/10/2009 4:34 PM

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

Left by bhavish shah at 7/17/2009 4:53 AM

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

Left by vamsi at 7/25/2009 2:35 AM

Please provide a solution for this iam trying from two days

# re: Cognos 8 date range filter

Left by Ryan at 7/31/2009 12:34 PM

you can use the extract filter.

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

# re: Cognos 8 Most Recent Date

Left by Jan at 12/23/2009 7:46 AM

I'm trying to filter a report so it will only pick up the most recent date for each group. Can anyone tell me how to do this in Cognos

# re: Cognos 8 date range filter

Left by denise tinsley at 12/23/2009 10:12 AM

I am no longer working in a setting where I have access to the cognos tool so if anyone else know please reply. I know that sometimes the final solution is related to the database vendor. Are you using oracle, sql server...? When you do searches of this question it helps to include the database type.

# re: Cognos 8 date range filter

Left by yasar at 3/11/2010 4:32 PM

here is ytd,
date exp. between _make_timestamp(to_char(_add_years(current_date,0),'yyyy'),1,1) and current_date

# Previous week and previous quarter function

Left by Lucy at 12/17/2010 10:03 AM

I am trying to filter report and i need previous week start and end date - also previous quarter start and end day - please help

# re: Cognos 8 date filter

Left by Chris at 1/15/2011 3:18 AM

anyone knows how to filter dates to be posted except empty ones. Not necessarily within ranges.

# re: Cognos 8 date range filter

Left by christopher at 2/3/2011 1:21 AM

I try to extract the year out of the current date (today) in cognos. Can anyone help?

# Cognos 8.4Most recent Date from DB

Left by srik at 4/5/2011 11:51 PM

I have an issue
Reports that offer “custom date”, i am trying to add a default item called “most recent”.
When “most recent” is selected, the last date on the database should be used.
Otherwise, using the system (especially for demo) is extremely hard. Can anyone help me how to resolve this issue please?

# re: Cognos 8 date range filter

Left by darcy at 8/30/2011 2:24 AM

hi,

i am trying to retrieve the no.of machines shipped from 2008 to months between date range

for ex:
from date: 06/06/2011
to date: 08/08/2011

here the months between date range are jun-2011 , july-2011,aug-2011

i need to retrieve the number of machines shipped from jan-2008 to jun-2011 and jan-2008 to jul-2011 and jan-2008 to aug-2011
o/p:-

jun-2011 jul-2011 aug-2011
(jan,08-jun,11) ( jan,08-jul,11) (jan,08-aug,11)
----- -------- --------
100 120 135

can any one help how to resolve this issue?

# re: Cognos 8 date range filter

Left by darcy at 8/30/2011 2:28 AM

i am trying to retrieve the no.of machines shipped from 2008 to months between date range

for ex:
from date: 06/06/2011
to date: 08/08/2011

here the months between date range are jun-2011 , july-2011,aug-2011

i need to retrieve the number of machines shipped from jan-2008 to jun-2011 and jan-2008 to jul-2011 and jan-2008 to aug-2011
o/p:-

jun-2011 jul-2011 aug-2011
(jan,08-jun,11) ( jan,08-jul,11) (jan,08-aug,11)
------------- -------------- -------------
100 120 135

can any one help how to resolve this issue?

# re: Cognos 8 date range filter

Left by Adil Ali at 9/11/2011 3:53 PM

It is pretty easy. Create 3 data item in your query with if then else statement.

state would look something like this

if (shipment date between jan,08 and jun,11) then
(machine shipped)
else
(null)

Create 2 more data items subsequently for between jan,08 and jul,11
and jan,08-aug,11
and choose total for these data items in property pane.

Hope this helps

# re: Cognos 8 date range filter

Left by Ravikiran Arun patil at 2/10/2012 9:06 PM

Thanks your logic is good and its working

Your comment:

Title:

Name:

Email:

(not displayed)

Website:

Comment:

Remember Me?

Enter the code shown above

Live Comment Preview:

February

Sun

Mon

Tue

Wed

Thu

Fri

Sat

About

Twitter

denisetinsley I am reading 'A Renegade's Guide to God: Finding Life Outside Conventional Christianity' by David Foster via @amazon http://t.co/XntZQupG about 78 days ago

denisetinsley @MidwestBlonde --me too about 80 days ago

denisetinsley I just bought: 'The Prepper's Pocket Guide: 101 Easy Things You Can Do to Ready Your Home for a... via @amazon http://t.co/xfEb1bvl about 81 days ago

denisetinsley @Eric_Bartoszak -- I couldn't wait. Mine is cooking now. Smelling very, very nice. about 81 days ago

denisetinsley @ChuckNellis --a wretched hive of scum and villiianry... we must be cautious about 81 days ago

denisetinsley @TheFlaCracker --baking ham instead. Thanks for the idea butterball. about 81 days ago

denisetinsley A Stanford education (free!): http://t.co/kibFGMau about 84 days ago

denisetinsley Freaked out by my eyesight suddenly getting worse. Just noticed a lens popped out of glasses. Silly Monday. about 104 days ago

denisetinsley Interesting article about alternate networks. http://t.co/d1n3sQDC about 138 days ago

denisetinsley @beesnguns --thks for honoring those who didn't make it that day. about 154 days ago

Technodrone

there's some good in this world, Mr. Frodo... and it's worth fighting for.

Cognos 8 date range filter

Comments on this entry:

# re: Cognos 8 date range filter

# re: Cognos 8 date range filter

# re: Cognos 8 date range filter

# re: Cognos 8 date range filter

# re: Cognos 8 date range filter

# re: Cognos 8 date range filter-get dates older that 180 days

# re: Cognos 8 date range filter

# re: Cognos 8 date range filter

# re: Cognos 8 date range filter

# YTD and MTD for this year and YTD , MTD for the same days of last year

# re: Cognos 8 date range filter

# re: Cognos 8 Most Recent Date

# re: Cognos 8 date range filter

# re: Cognos 8 date range filter

# Previous week and previous quarter function

# re: Cognos 8 date filter

# re: Cognos 8 date range filter

# Cognos 8.4Most recent Date from DB

# re: Cognos 8 date range filter

# re: Cognos 8 date range filter

# re: Cognos 8 date range filter

# re: Cognos 8 date range filter

Your comment:

Live Comment Preview:

About

Twitter

Archives

Post Categories

Image Galleries

agile development

Face Book Profile

LinkedIn Profile

Recommended Products

Stack Overflow Profile

Visual Studio ALM

Syndication: