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 @ Tuesday, August 5, 2008 2:46 PM
Print

Comments on this entry:

# re: Cognos 8 date range filter

Left by Alice Green at 10/6/2008 8:48 AM
Gravatar
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
Gravatar
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
Gravatar
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
Gravatar
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
Gravatar
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
Gravatar
[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
Gravatar
[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
Gravatar
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
Gravatar
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
Gravatar
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
Gravatar
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
Gravatar
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
Gravatar
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
Gravatar
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
Gravatar
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
Gravatar
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
Gravatar
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
Gravatar
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
Gravatar
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
Gravatar

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
Gravatar
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
Gravatar
Thanks your logic is good and its working

# re: Cognos 8 date range filter

Left by Jesse Nienke at 3/6/2012 5:36 AM
Gravatar
I need to find all accounts that were opened six months ago, for the whole month, i.e, if the current month is March, I need all of the accounts opened in September. What is the best way to filter this?

# re: Cognos 8 date range filter

Left by Denise Tinsley at 3/6/2012 6:48 AM
Gravatar
Jesse, I would also ask this question on stackoverflow. I have had pretty good luck with it. I no longer work with Cognos but I will forward this quesiton to someone who does.

# re: Cognos 8 date range filter

Left by Tori Foley at 4/19/2012 3:19 AM
Gravatar
I need to be able to prompt the user to enter target quarter and target year then use formulas to pull in the correct information based on the dates they put in. I'm using a Crystal report to base the Cognos report off of and this was done in Crystal so I need it in Cognos. Any ideas on how to do this? I'm lost.

# re: Cognos 8 date range filter

Left by priya at 6/27/2012 6:38 PM
Gravatar
how to filter the data within a date range like coming friday 8pm to monday 6am in cognos

# re: Cognos 8 date range filter

Left by Thanh Hua at 8/27/2012 4:43 AM
Gravatar
How do I create a filter expression to return effective date 1.1.2012 to current date? Current date is when the report is ran, I need to schedule this report.

# re: Cognos 8 date range filter

Left by azbah at 9/2/2012 9:10 PM
Gravatar
can anyone tell me how to solve this problem in cognos? urgent, please help me..huhu

how to create a report that user can select start date, then choose how many days that he wants to be view in the result list.
for example, start date is 3 september, he choose 3 days, then in the result page, it will display data for 3september until 6 september.

can anyone help me to solve this?

# re: Cognos 8 date range filter

Left by Thanh at 10/12/2012 4:57 AM
Gravatar
How do I write a expression > or = to current date?

# re: Cognos 8 date range filter

Left by Revs at 1/11/2013 11:14 PM
Gravatar
I have joined date and left date of staff details. I need to find Recruitment and Resignation rate of all staff for each quarter in single report.
I dont know how to write query for this?

Can anyone help me to solve this problem?

# re: Cognos 8 date range filter

Left by Lei at 4/3/2013 8:57 AM
Gravatar
Hi,
Does anyone know what are the expression if I will be getting the data for the past two months when the report will be run on the current date? Please help.

# re: Cognos 7 date range filter

Left by Nancy Brown at 4/9/2013 4:20 AM
Gravatar
Can anyone provide me with a formula that will calculate the previous month? I don't want to have to update the date range each month for previous month data.

Many thanks.

# re: Cognos 8 date range filter

Left by Denise Tinsley at 4/9/2013 4:42 AM
Gravatar
Nancy, try posting this question on www.stackoverflow.com. I have had very good luck getting questions answered there.

# re: Cognos 8 date range filter

Left by Rosa at 7/1/2013 4:47 PM
Gravatar
Hi I am trying to lists all employees who has not been paid for the last 3 months in cognos. I applied filter to show the maximum pay date for employee, found difference between the current date and the last date. While I am trying to list the employees with the difference greater than 30, it displays all data. Please help. I got stuck.

# re: Cognos 8 date range filter

Left by sangeetha at 7/31/2013 10:18 PM
Gravatar
I need help to extract only month and year from transcation date

#  QTD and YTD Calculation

Left by Sharon at 10/28/2013 1:06 AM
Gravatar
Hi,
I have 'Actual Date' Data Item. Using that query, I want to create QTD and YTD calculations. Please anybody help to figure it out.
Thanks.

# re: Cognos 8 date range filter

Left by Rao at 11/25/2013 3:55 PM
Gravatar
I have an year month prompt. when i run the report and select the year month as 201308, the report should display all the records till 07/31/2013. in the report there is an Year month filter Appreciate your help.

# re: Cognos 8 date range filter

Left by Cameron at 1/28/2014 7:25 AM
Gravatar
I just need the code that would work with DMR and not Dimensional Heirarchy. Please Help!!

# re: Cognos 8 date range filter

Left by dennis at 3/26/2014 2:11 AM
Gravatar
pretty new to cognos... could use some help... have a job setup the needs to run each monday morning around 6am or so and send the excel o/p doc to the recipients via email... here my probelm - the date for the extract needs to be from Saturday to Friday of the previous week... so how do I code my date filter to pull during the time period.. I know I could run the run at 11:58 pm on Fri - but that is just a bit sloppy for my taste... unless I could somehow automated it to run during that time but not send it until monday morning... thanks in advance for your help...

# re: Cognos 8 date range filter

Left by Irene Llanes-Osana at 3/26/2014 4:41 PM
Gravatar
Hi Nancy Brown,

Here's how to get previous month. Even you've run the report in 3rd, 4th, 15th or any day of the current month, it will only captures the previous month range.

trunc([Sales Date]) between _first_of_month( _add_months(trunc({sysdate}),-1)) and _last_of_month(_add_months(trunc({sysdate}),-1))

Hope it helps!
Irene ^_^

# re: Cognos 8 date range filter

Left by IbLearning at 4/3/2014 11:26 AM
Gravatar
Is there a way to add rolling dates on a query report that will be scheduled to run weekly in COGNOS 8.4? The report will need to extract data from previous Monday - Sunday but not be hard-coded. In other words, each week will have new data covering the previous Monday - Sunday without any intervention from the user. The field is called Finished Date and is formatted as: ccyymmdd
Any help is greatly appreciated.

Thank you.

Your comment:



(not displayed)

 
 
 
 
 

Live Comment Preview:

 
«April»
SunMonTueWedThuFriSat
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910