Pass DATE values from prompt page to Stored Procedures parameters

[sqlhungry]

Hi All,

Preview:
I successfully imported the stored procedure(SP) from Framework Manager to Report Studio 8.4.1. The SP takes startdate and enddate as its 2 input parameters and returns a dataset within that date range. Macro used for input parameters for SP are uspStartDT and uspEndDT.

My report already has a prompt page which (along with other filters) gets date range according to the selection from radio button options.
For example,
current       = first of month to today's date
last month  = 1st day of previous month to last day of previous month
custom       = users can choose startdate and enddate

I created 2 dataitems, viz, pStartDT and pEndDT which calculates the startdate and enddate according to the users choice of current, last month or custom date range.

Problem:
After all the required criterias/filters selected from the prompt page the user clicks the 'Finish' button to retrieve the report.
When 'Finish' is pressed, I need to assign the calculated pStartDT and pEndDT values to the input parameters(uspStartDT and uspEndDT) of the SP and generate the report.
I DONT want to be prompted again for start and end dates (which is caused by the SP.)
I have been trying to use the Javascript for this but it ends up on "Error on Page" but doesnt pin point what/where the error is.

Any help/suggestion is really appreciated.

Thank you very much,

Sincerely,
SQLHungry
teachmesql@gmail.com

[Arsenal]

i don't think this can be done via dataitem to parameter value handoff. When you hit finish, for a SP based model, the report will immediately expect a proper value (in your case, uspStartDT and uspEndDT) for the prompt macros stored within the model.

I think the only ay you can achieve this is to have some sort of a JS calculate the current, last month etc. choices and then push these values into two textboxes whose parameters named the same as what is on your FM model(uspStartDT and uspEndDT in your case).

For your JS troubleshooting error message, install Firefox and then install Firebug addon and that will let you see the exact JS error

[CognosPaul]

All parameters in all versions of Cognos (at least from 8.2, I've never worked with prior versions) are saved in hidden input boxes. You can create your own parameter by creating an HTML item with:
<input type="hidden" name="p_uspStartDT" id="p_uspStartDT">
<input type="hidden" name="p_uspEndDT" id="p_uspEndDT">

In your scenario you'll need to pass the value to that HTML item before the report is processed. Once that input has the value, a submit of the page will have it saved as a parameter value.

In your scenario, I can think of two ways to get the value into the HTML item. First option is to go with your existing query. First, change the finish button to a next button, and rename it to finish. Then create a second prompt page. Put in a singleton associated with your query that calculates the dates, and put the data items in the properties. Next, drag in an HTML item and set the source type to report expression. Use the following expression:
'<input type="hidden" name="p_uspStartDT" id="p_uspStartDT" value="' +[Query1].[Start Date]+ '">
<input type="hidden" name="p_uspEndDT" id="p_uspEndDT" value="' +[Query1].[End Date]+ '">'

Create another HTML item that contains the auto-refresh script (google it, you'll find it on the IBM site). Now once the report has been submitted the dates will be entered into the parameter values.

The other way is the way I'd prefer to do it. First you need to put the HTML item with the inputs somewhere on the page. Then you need to create a js function that calculates the dates. Finally write a script that will overwrite the onclick event for the finish button, replacing it with your own code. Remember to incldue the submit function! This method is faster as the second prompt page doesn't have to render.

[sqlhungry]

Thanks for the reply, now i have a good picture of what needs to be done.

However, I am not a Javascript person. I dont know how to read the values from the prompt page and assign it to input parameters of Stored Procedure.
I managed to create a button in Javascript( I named it 'Finish' which will replace the original Finish button on the prompt page), when its clicked it calls a function 'populatedaterange()'.
In this function, i need to put the logic of retrieving the startDT and endDT and then pass these values to Stored Procedures input paremeters (uspStartDT and uspEndDT).

Some questions:

• How can  I be sure that I wont  be prompted again by  Stored Procedure's prompt for the uspStartDT and uspEndDT macros?
• How about the other filters in the reports prompt page? will they be effective?
• Could anyone with the Javascript knowledge help me with the codes to read the StartDT and EndDT values and assign it to the uspStartDT and uspEndDT of the Stored Procedure.

Thank you very much :)

Sincerely,
SqlHungry