Date syntax for queries

[ry1633]

This is a simple question but I can't remember reading it.  How does Cognos RS handle the syntax for dates in queries.  As an example,  I'd like to have a "date" field in my report that only shows this year's data.   And so I'm trying to put a query expression that will do that into a column header for a report.

I've tried it two different ways with and without ()'s

[SOME_DATE_FIELD]>2015-12-31

[SOME_DATE_FIELD]>(2015-12-31)

[bdbits]

Assuming a relational package, try something like: [Some Date] > cast('2015-12-31' as date)

Although, I'd be more likely to build an expression that did not rely on a hard-coded date.

[BigChris]

year([Some Date Field]) = year(current_date)

[ry1633]

Thanks.  What I need to do is have a date column in a report that only lists 2015 dates and leaves out previous years.  So I put in a date column (imported from package) into my report.  In Report Studio, I right-clicked on the entire date column and went to "Add Query Expression".  Is this the best way to do it?

[bdbits]

I see. Assuming relational - and it matters - you would use a filter. Open the report, select View > Queries and then open your query. Drag the column you want to filter to the Detail Filters pane and build your expression, e.g. year([Some Date Field]) = year(current_date) to borrow from BigChris. Only items for which the filter expressions evaluate to "true" will be included in report results.

[ry1633]

I right-clicked on the date column (on the very top, header of that column) of my report -->  Edit Query Expression and did this. 

([DB_Name_here].[view_name_here].[date_field)>2014-12-31

But it didn't work, got this:   An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.  Details UDA-SQL-0358 Line 9: Syntax error near ">"

[bdbits]

Doing what you are doing is not adding a filter - you are altering the query data item itself. Also, it looks like you are trying to use actual physical database names. You need the data item names as specified in the Cognos package, which may or may not bear any resemblance to the physical data source names.

If the above does not mean anything to you, at least look through the Report Studio user guide (see menu item Help > Help in Report Studio), find some online basic tutorials (Youtube should have some), or get some Cognos training. You need at least a basic understanding of how Cognos works to make use of Report Studio.

[ry1633]

I also tried inserting a Query Calculation in the column below the column header as follows

[SOME_DATE]>2015-01-01

[Lynn]

I also tried inserting a Query Calculation in the column below the column header as follows

[SOME_DATE]>2015-01-01

That is not a query calculation. It is a filter expression. Did you read the posts that bdbits wrote? He explained all this already.

Take a look at the user guide for the topic "Focusing Relational Data". It seems like you have a relational source although you didn't specify. There are zillions of online resources that explain how to filter your data, again, as bdbits already told you.

Here is a link to the 10.2.1 guide:
http://public.dhe.ibm.com/software/data/cognos/documentation/docs/en/10.2.1/ug_cr_rptstd.pdf

[ry1633]

ok thanks for your help.

[ry1633]

just had a random thought.... is it possible to do the date query from your data source in Framework Manager - create a new package - and bring it into Report Studio already "pre-made" essentially?

Advantages or Disadvantages to doing a query like this in Framework Manager first instead of in Report Studio?   Remember I've only been working with Cognos less than 5 months and have only taken one course, so if this seems like a rudimentary question, maybe it's because it is.  :)