SQL Server Functions in Report Studio

[neilunderwood]

I need to add a drop down at the top of a report in Report Studio which pulls available dates from the last 12 months for selection.Ã,  To do this I'm creating a filter that makes use of the dateadd function from SQL Server.Ã, 

My filter is coded as follows:

[My Date] > dateadd({m}, -12, getdate())

When I validate this I get the warning "There were errors in other parts of the report not related to this expression.Ã,  To view them, click the validate button on the tool bar".

When I click the validate button on the tool bar I then get the following error message:
"QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-228'. UDA-SQL-0219 The function "dateadd" is not available as an external, database, or built-in function. "

So basically Cognos is contradicting itself.Ã,  Nobody I work with has seen this problem before.Ã,  Has anyone else?Ã,  If so, how did you solve it?

[JoeBass]

ReportNet is finicky about parentheses.  If you try (dateadd({m}, -12, getdate()))  it should work.  Also, that error message is meaningless.  Makes debugging a chore

[neilunderwood]

What is ReportNet not finicky about?!

That did get rid of the pointless error message, but the report still won't validate...

QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-228'. UDA-SQL-0219 The function "dateadd" is not available as an external, database, or built-in function.

[JoeBass]

Are you adding the filter within a  Tabular Model?  It sounds like might be attempting to add the function somewhere out of scope - maybe at the cube level of your query?  Another possibility is that the package could have been published without associating the correct database type.

[neilunderwood]

The filter is within a tabular model, but it needs to use SQL Server functions which are only available there.

There's a filter in another query within the report that also uses the dateadd function and that works fine!

[JoeBass]

If you delete the filter, can you run the report without complaint?

[neilunderwood]

Yeah!  ???

[JoeBass]

With the filter back in place, what does the "generated SQL" look like? 

What happens when you copy and paste it in Query Analyzer (assuming it will give that to you even though it thinks there is an error)

Are there other filters used on this query? 

[JoeBass]

One more thought - you might try the ReportNet function _add_days()  I've used it with SQL server out back and its worked fine.  Can't remember why I had to.

[neilunderwood]

Ok I got into work this morning and read this, went to add the filter again and generate the SQL and it worked.  I used Cognos' current_date function instead of getdate() and even though I swear I tried that yesterday without success, for some reason it's decided to work today!    ;D

I then went to add it in an another report where I need to use the same filter on dates and I got the error message again, even using current_date.  I then tried the _add_months() function as you suggested and it worked.    :D

If anyone has any kind of explanation for this inconsistent behaviour, I'd be really glad to hear it because it's doing my head in!   :-\

[CoginAustin]

sleepyness?