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

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 5, 2008 2:46 PM | Back to top


Comments on this post: Cognos 8 date range filter

# re: Cognos 8 date range filter
Requesting 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
Left by Alice Green on Oct 06, 2008 8:48 AM

# re: Cognos 8 date range filter
Requesting 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.
Left by technodrone on Oct 07, 2008 2:49 PM

# re: Cognos 8 date range filter
Requesting Gravatar...
How do I do a filter where I want to bring back all records older than a 180 days from the current date?
Left by Tim T on Oct 17, 2008 9:25 AM

# re: Cognos 8 date range filter
Requesting 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
Left by Talam on Nov 19, 2008 11:26 AM

# re: Cognos 8 date range filter
Requesting 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))
Left by technodrone on Nov 19, 2008 11:55 AM

# re: Cognos 8 date range filter-get dates older that 180 days
Requesting Gravatar...
[Business View].[Sales Transaction].[Sales Date] <
_add_days(current_date,-180)
Left by technodrone on Nov 19, 2008 12:22 PM

# re: Cognos 8 date range filter
Requesting Gravatar...
[Business View].[Sales Transaction].[Sales Date] <
_add_days(current_date,-180 day)
Left by Rogerio on Mar 05, 2009 4:37 PM

# re: Cognos 8 date range filter
Requesting 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
Left by yasar on Jun 10, 2009 4:34 PM

# re: Cognos 8 date range filter
Requesting Gravatar...
Does anyone know how to exclude time from the a date field when performing a filter?
Left by bhavish shah on Jul 17, 2009 4:53 AM

# YTD and MTD for this year and YTD , MTD for the same days of last year
Requesting Gravatar...
Please provide a solution for this iam trying from two days
Left by vamsi on Jul 25, 2009 2:35 AM

# re: Cognos 8 date range filter
Requesting Gravatar...
you can use the extract filter.

extract(year, month, day, [date expression])
Left by Ryan on Jul 31, 2009 12:34 PM

# re: Cognos 8 Most Recent Date
Requesting 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
Left by Jan on Dec 23, 2009 7:46 AM

# re: Cognos 8 date range filter
Requesting 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.
Left by denise tinsley on Dec 23, 2009 10:12 AM

# re: Cognos 8 date range filter
Requesting Gravatar...
here is ytd,
date exp. between _make_timestamp(to_char(_add_years(current_date,0),'yyyy'),1,1) and current_date
Left by yasar on Mar 11, 2010 4:32 PM

# Previous week and previous quarter function
Requesting 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
Left by Lucy on Dec 17, 2010 10:03 AM

# re: Cognos 8 date filter
Requesting Gravatar...
anyone knows how to filter dates to be posted except empty ones. Not necessarily within ranges.
Left by Chris on Jan 15, 2011 3:18 AM

# re: Cognos 8 date range filter
Requesting Gravatar...
I try to extract the year out of the current date (today) in cognos. Can anyone help?
Left by christopher on Feb 03, 2011 1:21 AM

# Cognos 8.4Most recent Date from DB
Requesting 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?
Left by srik on Apr 05, 2011 11:51 PM

# re: Cognos 8 date range filter
Requesting 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?



Left by darcy on Aug 30, 2011 2:24 AM

# re: Cognos 8 date range filter
Requesting 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?


Left by darcy on Aug 30, 2011 2:28 AM

# re: Cognos 8 date range filter
Requesting 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
Left by Adil Ali on Sep 11, 2011 3:53 PM

# re: Cognos 8 date range filter
Requesting Gravatar...
Thanks your logic is good and its working
Left by Ravikiran Arun patil on Feb 10, 2012 9:06 PM

# re: Cognos 8 date range filter
Requesting 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?
Left by Jesse Nienke on Mar 06, 2012 5:36 AM

# re: Cognos 8 date range filter
Requesting 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.
Left by Denise Tinsley on Mar 06, 2012 6:48 AM

# re: Cognos 8 date range filter
Requesting 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.
Left by Tori Foley on Apr 19, 2012 3:19 AM

# re: Cognos 8 date range filter
Requesting Gravatar...
how to filter the data within a date range like coming friday 8pm to monday 6am in cognos
Left by priya on Jun 27, 2012 6:38 PM

# re: Cognos 8 date range filter
Requesting 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.
Left by Thanh Hua on Aug 27, 2012 4:43 AM

# re: Cognos 8 date range filter
Requesting 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?
Left by azbah on Sep 02, 2012 9:10 PM

# re: Cognos 8 date range filter
Requesting Gravatar...
How do I write a expression > or = to current date?
Left by Thanh on Oct 12, 2012 4:57 AM

# re: Cognos 8 date range filter
Requesting 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?
Left by Revs on Jan 11, 2013 11:14 PM

# re: Cognos 8 date range filter
Requesting 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.
Left by Lei on Apr 03, 2013 8:57 AM

# re: Cognos 7 date range filter
Requesting 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.
Left by Nancy Brown on Apr 09, 2013 4:20 AM

# re: Cognos 8 date range filter
Requesting Gravatar...
Nancy, try posting this question on www.stackoverflow.com. I have had very good luck getting questions answered there.
Left by Denise Tinsley on Apr 09, 2013 4:42 AM

# re: Cognos 8 date range filter
Requesting 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.
Left by Rosa on Jul 01, 2013 4:47 PM

# re: Cognos 8 date range filter
Requesting Gravatar...
I need help to extract only month and year from transcation date
Left by sangeetha on Jul 31, 2013 10:18 PM

# QTD and YTD Calculation
Requesting 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.
Left by Sharon on Oct 28, 2013 1:06 AM

# re: Cognos 8 date range filter
Requesting 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.
Left by Rao on Nov 25, 2013 3:55 PM

# re: Cognos 8 date range filter
Requesting Gravatar...
I just need the code that would work with DMR and not Dimensional Heirarchy. Please Help!!
Left by Cameron on Jan 28, 2014 7:25 AM

# re: Cognos 8 date range filter
Requesting 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...
Left by dennis on Mar 26, 2014 2:11 AM

# re: Cognos 8 date range filter
Requesting 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 ^_^
Left by Irene Llanes-Osana on Mar 26, 2014 4:41 PM

# re: Cognos 8 date range filter
Requesting 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.
Left by IbLearning on Apr 03, 2014 11:26 AM

# re: Cognos 10 date range filter
Requesting Gravatar...
I'm new to Cognos 10 and I'm just learning how to write reports. I'm have a feild that changing often in the tool that Cognos reports from; Owner Feild. I need to build a filter that shows me the oldest date / time stamp for this Owner feild. I believe I can use TKH Own date, but I'm struggling with the filter expression.

Help

Left by Pbross on Jun 17, 2014 7:26 AM

# re: Cognos 8 date range filter
Requesting Gravatar...
I am new to Cognos 10 and I am needing to set up a report which includes any claims that have been open for 12 or more months from the report date. Can anyone help?
Left by Claudia on Nov 21, 2014 5:24 AM

# Need to Filter Years of Service
Requesting Gravatar...
I have a report that has an expression on the seniority years [YearDiff]> 0, I need to extract certain seniority years such so I added another expression [YearDiff] in ('1','5','10','15','20','25'') however, I keep getting an error. Can someone please help me with this!
Left by Jay River on Jan 20, 2015 6:25 PM

# re: Cognos 8 date range filter
Requesting Gravatar...
How do I create a view filter for previous weeks data? I want the whole week's data not just 7 days ago based on the week number on the system
Left by Bill on Feb 25, 2015 5:16 AM

# re: Cognos 8 date range filter
Requesting Gravatar...
how to display year of last payment
Left by Gopal on May 14, 2015 4:52 PM

Your comment:
 (will show your gravatar)


Copyright © technodrone | Powered by: GeeksWithBlogs.net | Join free