Author Topic: Detail Filter w/ Hard Coded Start Date to Current_date  (Read 579 times)

Offline Cognos_Jan2017

  • Statesman
  • ******
  • Join Date: Jan 2017
  • Posts: 305
  • Forum Citizenship: +1/-0
Detail Filter w/ Hard Coded Start Date to Current_date
« on: 15 Feb 2018 11:34:16 am »
We have successfully used Date Prompts for "From - To" w/ parameters for many Reports.

But for a new Report we need a Hard Coded Start Date ...
the beginning of our Fiscal Year, IE, FY 2018 began 10-1-2017
... and the "To" Date to be Current_Date (which fails).

Been Google searching MANY articles but getting error whenever validating.

Here's an example of a fail ...
Thought placing a Case When Extracting the Month might work by
hard coding something like ...
[Date Of Incident] between _add_days(current_date,-30) and current_date

Thoughts?  TIA, Bob

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,208
  • Forum Citizenship: +620/-10
  • Cognos Software Muppet
Re: Detail Filter w/ Hard Coded Start Date to Current_date
« Reply #1 on: 15 Feb 2018 11:59:36 am »
We have successfully used Date Prompts for "From - To" w/ parameters for many Reports.

But for a new Report we need a Hard Coded Start Date ...
the beginning of our Fiscal Year, IE, FY 2018 began 10-1-2017
... and the "To" Date to be Current_Date (which fails).

Been Google searching MANY articles but getting error whenever validating.

Here's an example of a fail ...
Thought placing a Case When Extracting the Month might work by
hard coding something like ...
[Date Of Incident] between _add_days(current_date,-30) and current_date

Thoughts?  TIA, Bob

Hi,

Is this a dimensional or relational package? If a relational, is the item in question definitely a date datatype? Can you give us the exact filter syntax you have tried, and any error messages you might get (or an idea of the result it returns if incorrect)?

MF.
Meep!

Offline Cognos_Jan2017

  • Statesman
  • ******
  • Join Date: Jan 2017
  • Posts: 305
  • Forum Citizenship: +1/-0
Re: Detail Filter w/ Hard Coded Start Date to Current_date
« Reply #2 on: 15 Feb 2018 12:53:48 pm »
Thank you MFGF.

Quote
Is this a dimensional or relational package? If a relational, is the item in question definitely a date datatype? Can you give us the exact filter syntax you have tried, and any error messages you might get (or an idea of the result it returns if incorrect)?

Relational, SQL Server DB.  The Field IS a Date Datatype.

Tried, as test, Detail Filters of .....
[Date Of Incident] >= 10/1/2017
[Date Of Incident] <= Current_Date

Tried several others from ...
http://cognosskills.blogspot.com/
... but they all result in similar error messages as below

Get error ...
RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-56'.
UDA-SQL-0115 Inappropriate SQL request.
UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Deferred prepare could not be completed.
UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Statement(s) could not be prepared. (SQLSTATE=42000, SQLERRORCODE=8180)
UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Operand type clash: date is incompatible with float (SQLSTATE=22018, SQLERRORCODE=206)
RSV-SRV-0042 Trace back:
 ... lot more ...

TIA, Bob

Offline Cognos_Jan2017

  • Statesman
  • ******
  • Join Date: Jan 2017
  • Posts: 305
  • Forum Citizenship: +1/-0
Re: Detail Filter w/ Hard Coded Start Date to Current_date
« Reply #3 on: 15 Feb 2018 03:01:21 pm »
Can CONFIRM that removing Detail Filter attempts of ...
[Date Of Incident] >= 10/1/2017
[Date Of Incident] <= Current_Date
... and then View tabular data for the only Query in this Report does NOT error.

Why?  I don't know.  Reminds me of Microsoft Access errors if "References" aren't set in an Access DB.  Is there a similar setting in Cognos Analytics that could cause this?

TIA, Bob

Offline Cognos_Jan2017

  • Statesman
  • ******
  • Join Date: Jan 2017
  • Posts: 305
  • Forum Citizenship: +1/-0
Re: Detail Filter w/ Hard Coded Start Date to Current_date
« Reply #4 on: 15 Feb 2018 03:46:37 pm »
Got this to WORK !!!

[Date Of Incident] between Cast('2017-10-1', date) and current_date
... which WORKS !!!

Offline Cognos_Jan2017

  • Statesman
  • ******
  • Join Date: Jan 2017
  • Posts: 305
  • Forum Citizenship: +1/-0
Re: Detail Filter w/ Hard Coded Start Date to Current_date
« Reply #5 on: 15 Feb 2018 03:50:37 pm »
The above Detail Filter ...
It validates, but doesn't return any data.  Will examine more tomorrow.

Offline Cognos_Jan2017

  • Statesman
  • ******
  • Join Date: Jan 2017
  • Posts: 305
  • Forum Citizenship: +1/-0
Re: Detail Filter w/ Hard Coded Start Date to Current_date
« Reply #6 on: 15 Feb 2018 03:54:35 pm »
It didn't work because hard coded Site had NO data.

DID WORK for a Site with data !!!!

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,208
  • Forum Citizenship: +620/-10
  • Cognos Software Muppet
Re: Detail Filter w/ Hard Coded Start Date to Current_date
« Reply #7 on: 16 Feb 2018 03:30:07 am »
It didn't work because hard coded Site had NO data.

DID WORK for a Site with data !!!!

Hi,

It ought to work without the cast, too. I suspect the issue is that you are using a date format the database doesn't understand. Honestly these US/UK date formats confuse me too - is 10/1/2017 the 10th of January or the 1st of October? ;)

You could try with 

[Date Of Incident] between 2017-10-01 and current_date (where the format of the date is YYYY-MM-DD so this would be from 1st October 2017)

If this works, it ought to be more efficient than using a cast() function

Cheers!

MF.
Meep!

Offline Cognos_Jan2017

  • Statesman
  • ******
  • Join Date: Jan 2017
  • Posts: 305
  • Forum Citizenship: +1/-0
Re: Detail Filter w/ Hard Coded Start Date to Current_date
« Reply #8 on: 16 Feb 2018 09:24:26 am »
Thank you MFGF.

Just tried
Quote
[Date Of Incident] between 2017-10-01 and current_date
which DOES work.

Revisited yesterday's prob;em.  This was the bad Detail Filter ...
[Date Of Incident]>=10/1/2017

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,208
  • Forum Citizenship: +620/-10
  • Cognos Software Muppet
Re: Detail Filter w/ Hard Coded Start Date to Current_date
« Reply #9 on: 16 Feb 2018 09:49:45 am »
Thank you MFGF.

Just triedwhich DOES work.

Revisited yesterday's prob;em.  This was the bad Detail Filter ...
[Date Of Incident]>=10/1/2017

Yep. 10/1/2017 is what is causing the hissy fit. You could replace it with [Date Of Incident] >= 2017-10-01 and that would work, I'll wager.

Reminds me of a pompous English teacher I had when I started High School. He was very particular about how we represented dates. I remember him ranting at one of my classmates for referring to Christmas Day as December 25th. "December the 25th 1977? What does that mean? It's meaningless. It's the 25th of December not December the 25th. December the 25th is nonsense. December the 25th what? It's the 25th of December 1977, which is an abbreviation of the 25th day of the month of December in the year 1977. Don't ever use that meaningless nonsense in my class again!"

We all wanted to send him to the USA and watch him explode in a fireball of fury... :)

MF.

Meep!

Offline Lynn

  • Statesman
  • ******
  • Join Date: Apr 2008
  • Posts: 2,598
  • Forum Citizenship: +336/-1
Re: Detail Filter w/ Hard Coded Start Date to Current_date
« Reply #10 on: 16 Feb 2018 09:55:46 am »
As long as you didn't send him on the 4th of July which is rarely referred to as July 4th (at least in my experience anyway)
 ;)

Offline Cognos_Jan2017

  • Statesman
  • ******
  • Join Date: Jan 2017
  • Posts: 305
  • Forum Citizenship: +1/-0
Re: Detail Filter w/ Hard Coded Start Date to Current_date
« Reply #11 on: 16 Feb 2018 10:10:51 am »
Healthy discussion so we can utilize Cognos features to produce Excellent Reports.

Thanks guys, Bob

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,208
  • Forum Citizenship: +620/-10
  • Cognos Software Muppet
Re: Detail Filter w/ Hard Coded Start Date to Current_date
« Reply #12 on: 16 Feb 2018 10:17:19 am »
As long as you didn't send him on the 4th of July which is rarely referred to as July 4th (at least in my experience anyway)
 ;)

Weird! On a random Summer's day, too. ;)
Meep!

Offline cognos4321

  • Community Leader
  • *****
  • Join Date: Sep 2013
  • Posts: 101
  • Forum Citizenship: +0/-0
Re: Detail Filter w/ Hard Coded Start Date to Current_date
« Reply #13 on: 26 Mar 2018 05:34:06 pm »
We have successfully used Date Prompts for "From - To" w/ parameters for many Reports.

Hi Bob,

Sorry to bug you on an old post.
I need to create a date filter on an existing report using a parameter which users can select,"Reporting Date"
So, the data should be filtered as in:
( ([Sales BeginDate]<= ?Reportign Date?) 
and
([Sales End Date]>=?Reportign Date?))

where [Sales BeginDate] and [Sales End Date] are the 2 existing 'Date' fields used in the report.

I DONOT see any errors during validation or running the report but the data is not filtered in the result.

I tried a lot using Cast , to_char, to_date functions just to be sure all are in the same format but all in vain.
Existing format for [Sale BeginDate] &[Sales End Date] are of the format mm/dd/yyyy.
I checked the format for parameter ?Reporting Date? by pulling it onto to report page and it was 'yyyy-mm-dd-T00:00:00:000'

I used the expression :
 cast([Reporting date],varchar(10)) and now it's yyyy-mm-dd
When I use to_char(?Reporting date?,'mm/dd/yyy') in order to make mm/dd/yyyy I get this error.
RQP-DEF-0177 An error occurred while performing operation 'sqlOpenResult' status='-9'.
UDA-SQL-0107 A general exception has occurred during the operation "open result".
ORA-01722: invalid number

I am not sure if that could be one of the reasons for report not filtering any data.
Please help me in figuring out where did I go wrong.

Relational Model.

Thank you.


Offline Cognos_Jan2017

  • Statesman
  • ******
  • Join Date: Jan 2017
  • Posts: 305
  • Forum Citizenship: +1/-0
Re: Detail Filter w/ Hard Coded Start Date to Current_date
« Reply #14 on: 26 Mar 2018 09:32:42 pm »
The date format should be yyyy-mm-dd.

An example of a hard-coded starting date (you can use your parameter as long as the date format is yyyy-mm-dd) ...
DateOfInterest between 2017-10-1 and Current_Date

Let us know if this works for you.

TIA, Bob

 


       
Twittear