FM Stored Procedure Error [Resolved]

[Ravisha]

Hello Cognoise,

I noticed a very strange thing today while I was working on importing a stored proc into FM model.

One of my store proc has a date parameter (optional though) and the data type of it is 'date'. After I imported the store proc into the FM model, I tried to make it parameterized as well as optional by passing the dynamic default value (todays date) into the prompt macros (by implementing the logic below). I've also set the datatype to 'date' in the store proc wizard. Strangely, it throws an error saying that "the store proc except the parameter in CCYYY-MM-DD format". But, when I hard code the default value as '2017-01-24', it likes it and validation is successful. I've played with a couple of other expressions (as mentioned below) but not luck.

I'd really appreciate if anyone can shed some light on it.

#prompt('pDate','date',sq(timestampMask($current_timestamp,'yyyy-mm-dd')))#

#sq(prompt('pDate', 'date', timestampMask($current_timestamp,'yyyy-mm-dd')))#

#prompt ('pDate', 'date','current_date')#

Thanks

[CognosPaul]

Have you tried:

#prompt('pDate','date',timestampMask($current_timestamp,'yyyy-mm-dd'))#

Stored procs in FM usually don't like having quotes in it.

[Ravisha]

Hello Paul,

Thanks a lot for your response.  :)

Yes, I've tried it as well but no luck. I still can't seem to comprehend or connect the dots as to why it likes when I hard code it (like 2017-01-25). Also, have no clue why it throws an error " the store proc expect the parameter in CCYY-MM-DD format ". The parameter's datatype in the store proc is 'date'.

Thanks

[CognosPaul]

What is the database? It's not Oracle is it?

[Ravisha]

I'm really sorry for not mentioning the database vendor. How stupid of me. It is SQL Server 2016.

Thanks

[CognosPaul]

okay, let's do a quick sanity check:
Try without the prompt, does this work:
#timestampMask($current_timestamp,'yyyy-mm-dd')#
or this:
#sq(timestampMask($current_timestamp,'yyyy-mm-dd'))#

[Ravisha]

Hello Paul,

This works ! But, why not in #prompt ()# ?  :o

#timestampMask($current_timestamp,'yyyy-mm-dd')#

Thanks

[Ravisha]

I have tried a different approach and it works. But, my mind is still occupied to solve the challenge which I had.  ;D

I made a copy of the stored proc and changed the data type of the parameter from 'date' to 'int'. I handle the conversion of the data type 'int' to 'date' in the main body of the store proc itself.

CONVERT(date, CONVERT(varchar[8], @ParamDate), 112)

Now, in FM model, in the store proc wizard, I've written the expression mentioned below. This will handle the conversion of 'date' datatype to 'int' whenever the user selects a specific date from the date prompt in the report.

#join ('', split('-', prompt('p_date','date',timestampMask($current_timestamp,'yyyymmdd'))))#

Thanks

[CognosPaul]

Progress!

I believe what is happening is that Cognos is testing the parameter with the date type from the prompt. This *MIGHT* be formatted as 'yyyy-mm-dd', which we've already established the SPs don't like. They want yyyy-mm-dd (without the quotes).

Change the prompt to: #prompt('pDate', 'token', timestampMask($current_timestamp,'yyyy-mm-dd'))#

The date prompts on the page will pass yyyy-mm-dd, which will then be passed as is to the sp, instead of having quotes wrapped around it.

[Ravisha]

Thanks a lot for all your help and suggestions Paul. I very much appreciate it.  :) It's very wonderful to see that you all share knowledge and help others get benefited out of it.