Keep processing of parameter values as database only

[RudiHendrix]

I have a pretty complex report that I want run on our Netezza DB machines as much as possible.

In the prompts an end user picks a date. Now I want to transform this date to be (for example) '11-7-2016 6:00:00' rather than '11-7-2016 00:00:00' which is the value that comes from the date picker. (Or perhaps the date picker makes it '11-7-2016')
However, as soon as I start working with the parameter value all further processing is done local. On the complex report that is an issue, because the Cognos server runs out of temp space.

I have tried creating a simple report with nothing more than: a date picker in the prompt page and a data item on the report page displaying the parameter value. That runs well. As soon as I update the data item from ?Paramater1? to to_char(?Parameter1?; 'yyyy-MM-dd') I get the following error message:
The function "to_char" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported

I have been struggeling with this issue for far too long now!

[dougp]

If all you have in the query is a parameter, Cognos will do all of the processing locally.  Cognos needs a reason to use the database server.  Try adding a field from the database to the query.

[RudiHendrix]

Sounds logical. I will test it and get back. Thanks!

[RudiHendrix]

Unfortunately after adding a DB item it still doesn't work. If I pull up the tabular data it says "The function 'to_char' is not available in Function Set Id 'V_MSAccess'. Specify a Function Set Id on the appropriate data source in the model."

[dougp]

I haven't used MS Access for decades.  Make sure the to_char is a valid function in JetSQL.  If not, determine the correct name of the function you are trying to use in the language of the RDBMS you are trying to use and use that.

[RudiHendrix]

The thing is: I'm not using Access either  :-\
I have my dwh on IBM Netezza. The content store is Oracle. I have tried updating the function set of my framework but that doesn't seem to work either.

[RudiHendrix]

I think I have found it...

I have added a data item to the report:
to_timestamp( ([Day]+(?Parameter2?-[Day])) || ' 06:14:53'; 'yyyy-MM-dd hh:mi:ss')

In this example [Day] = Some day level data item from the framework and ?Parameter2?  is the date selected by the end user.
If I do this I can still perform a to_char of the item. If I end up doing some local processing somewhere the to_char function doesn't work anymore.

[Lynn]

Does your database really store date and timestamp information as text?

Seems odd that you would need to do this sort of character conversion at all. Can you explain more about why this is required? There might be other options.

If you simply need to add time to the date selected by the user in the date picker (as you indicated in your original post) you can use the _add_hours, _add_minutes, and/or _add_seconds functions.

If character conversion is necessary, you could try using the cast function rather than a native database function.

[RudiHendrix]

No, the database doesn't store it as text. It stores it as date rather than a timestamp.

I did use (cast(?Stopdatum? as timestamp) + 5 hour + 59 minute + 59 second) at some point. But then I noticed further down the line in the report where to_char was used for something different that it wasn't possible to use that function because it is a database function and by the first statement I have changed everything to local processing.
So, the first thing I did was to update all to_char uses to cast(;varchar()). After doing so, the report crashed, because I ran out of temp space on the Cognos server. And I would really like to continue to use the Netezza machine's power!

In another attempt I used _add_days(?Stopdatum?;21599/86400) but rather than [Selected stopdate] 5:59:59 I saw [Selected stopdate] 5:59:58 most likely some rounding or something. I saw the same thing happening to an item of which I knew it was 1-1-2016 06:00:00. If I performed an _add_days([Item];-1/86400) in order to get it to 1-1-2016 05:59:59 it ended up as 1-1-2016 05:59:58 as well.
Why didn't I use _add_hours, _add_minutes and _add_seconds? I wasn't aware of these functions! I did know about _add_days and adding fractions of a day in that way, but this I wasn't aware of!

I have tried to use it now. But I get the error message:
The function "_add_hours" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.
Let me see where that goes wrong.
If I use these functions, will it continue to run as database processing?

[RudiHendrix]

Ok, tested it more thoroughly. I wasn't aware of the functions _add_hours, _add_minutes and _add_seconds. And although they are of help when working with dates from the database they can't be used on a prompt value. As long as I don't take a DB field in account I cannot use DB functions on it.

[RudiHendrix]

So, what I posted previously could be a solution. Another solution (and what I am currently implementing) is to change the prompt to a date time prompt with a default selection for the correct time.

Thanks for thinking along!

[dougp]

Let's go back to your initial post.  You say that a date prompt is returning '11-7-2016 00:00:00'.  The date prompt should be returning a date, not a timestring or a timestamp.

Rather than a parameter variable (?Parameter1?) What if you use a prompt macro to work with the value?

#prompt ('Parameter1', 'timestamp')#

If you need to manipulate the value somehow, you can use timestampMask and sq (if required).

You can then add the hours you want...

_add_days(#prompt ('Parameter1', 'timestamp')#, 6)

[RudiHendrix]

Agreed. I think it becomes a timestamp when somebody started comparing it to a field containing a timestamp. (I didn't create the initial report)

A prompt macro? I should look into that and I will if I have some time left :)

Thanks a ton!