Detail Filter w/ Hard Coded Start Date to Current_date

[Cognos_Jan2017]

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

[MFGF]

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.

[Cognos_Jan2017]

Thank you MFGF.

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

[Cognos_Jan2017]

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

[Cognos_Jan2017]

Got this to WORK !!!

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

[Cognos_Jan2017]

The above Detail Filter ...
It validates, but doesn't return any data.  Will examine more tomorrow.

[Cognos_Jan2017]

It didn't work because hard coded Site had NO data.

DID WORK for a Site with data !!!!

[MFGF]

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.

[Cognos_Jan2017]

Thank you MFGF.

Just tried

[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

[MFGF]

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.

[Lynn]

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)
;)

[Cognos_Jan2017]

Healthy discussion so we can utilize Cognos features to produce Excellent Reports.

Thanks guys, Bob

[MFGF]

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. ;)

[cognos4321]

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.

[Cognos_Jan2017]

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

[cognos4321]

Thanks a lot for the reply, Bob.

Tried converting date field [Sales Begin Date]  from 'mm/dd/yyyy' to 'yyyy-mm-dd'

to_char([Sales Begin date],'yyyy-mm-dd')

And got 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

[Cognos_Jan2017]

Try ...
cast([Sales Begin date], date)

If anyone else knows how to correct this, please contribute.

Let us know.

TIA, Bob

[cognos4321]

Try ...
cast([Sales Begin date], date)

Now, got 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-01843: not a valid month

[Cognos_Jan2017]

Thank you.

Interesting.  Any idea why it is objecting to "not a valid month"?

[cognos4321]

Not sure,

Ok, let's forget about the report date parameter and tried filtering for Current_Date.

So the date field [Sales Begin date] is of the format mm/dd/yyyy
Pulled Current_date function on the report page to check the format, it says,  'Mar 27,2018' .

So, used the following exp. as detail filter
[Sales Begin date]<=to_char(current_date,'mm/dd/yyyy')

The report validates with NO errors but the data is not filtered correctly.
Means, I could see dates for 2020 also in the [Sales Begin Date] field.

And when generated the SQL, it doesn't show me any filter for current date.
Not sure, what's going wrong

[Cognos_Jan2017]

Look at this older Cognoise topic ...  I know Cast can have many varchars.  See if this helps you.

http://www.cognoise.com/index.php?topic=26521.0