how to dynamically change sysdate in database view(source for cognos report)

[nsaxena]

Hi All ,

I had one requirement for which i created the view in database and i am as of now using the same view in cognos for report generation.

Now in that view,we had many calculations which used sysdate...around 10 different column in view are using sysdate.
But now my client wants that sysdate to be dynamic...i.e. when user run the report in cognos..it should ask fro sysdate and then base view will dynamically take that used selected date as sysdate and show data..

Please suggest options to achieve same..i cannot take all calculations in cognos...they need to be calculated in database only for many reasons including performance...

Many thanks in advance!!

[cognos810]

Hello nsaxena,
Can you replace the Database View with a stored procedure instead? The stored procedure would accept a date and return the same results, replacing sysdate with the supplied date. From FM, using the prompt macros pass the date value to the Stored Procedure.


-Cognos810

[nsaxena]

Hi cognos810..

Many thanks for giving advice,i will surly try on this and will let you know...also can you exemplify little more about prompt macros..
suppose we have we created a stored procedure which accepts date as input parameter...now data for the view will refresh every time we execute the stored procedure...so if i get you correctly..now how to pass through macros this value to procedure...

Many thanks again!

[cognos810]

Hello nsaxena,
Clarifying on your comment, "now data for the view will refresh every time we execute the stored procedure"...I did not mean calling the view from stored procedure, but to actually have a totally independent SP with the same logic that the view has in it. The Stored procedure should accept date parameters. Once you have created the SP, pull it into your FM. Within FM, while working with the SP Query subject, you will be able to supply prompt macros as the values for the parameter to accept.
So, lets say your SP parameter is @some_date, this will show up in the FM query subject. In its value, you can insert a macro as such, for example, #prompt('Start_date','Date')#. Publish your package. Then on your report create a date prompt UI and associate it with the 'Start_Date' parameter, which the report will automatically pickup, as long as you reference the SP query subject in the report.
Optionally, you can actually go a step further, by putting logic in the SP, something like, if the @some_date value is '1900-01-01' then use current date, else use the supplied parameter value from the report. The prompt macro would then become #prompt('Start_Date','Date',sq('1900-01-01'))#. In essence, you just made the prompt optional, and on the report when the report user does not supply any value, then the data will be returned with the current_date records, else it will return the record set resulting from the date selected.

Makes sense?

-Cognos810