Initializing Stored Procedure Prompt based on user selection

[Balthazar]

Hi,

I have used a Date Prompt (PickerControl) in the report. I want to ignore the selected day and send 5th day of the selected month/year to retrieve data from stored procedure. Please let me know if there is a way to do this.
For E.g. if the user selects 09/22/2015, I want to pass 09/05/2015 to the stored procedure.

Note: I am using Report Studio 10.1. The stored procedure parameter has been defined in Framework Manager (#prompt).

Thanks in Advance.

[Lynn]

This expression extracts the year and month from the selected date and then uses a hard coded 5 to produce a timestamp for the fifth day of the month no matter what date is selected. That whole thing then gets cast to a date data type to remove the time portion.

Code Select Expand


cast ( _make_timestamp ( extract ( year, ?DatePrompt? ), extract ( month, ?DatePrompt? ), 5 ), date )


[Balthazar]

Thank you, Lynn for the expression. I have done a similar thing by using default value in #prompt (5th day of current month) if the user doesn't select a date.
If the user selects a date(?DatePrompt?), do we have to initialize the ?SPDatePrompt? with the expression using javascript?

[Lynn]

Thank you, Lynn for the expression. I have done a similar thing by using default value in #prompt (5th day of current month) if the user doesn't select a date.
If the user selects a date(?DatePrompt?), do we have to initialize the ?SPDatePrompt? with the expression using javascript?

It has been a long time since I worked with stored procedures but I don't see why you'd need javascript. Use a prompt macro or whatever you would otherwise do to pass the value to the SP.