Make a data provider of a procedure

[saramaia]

Hi!

I would like to know how to make a data provider of a stored procedure. When i make a new data provider he executes the procedure at the moment and that is not what I want...

I want to generate a report and, if the user wants too, he can (with a data provider or not), execute the stored procedure that will update data for that report???

Can anyone help me here??

Thanks

Sara

 

[promero]

When you create a stored-procedure data provider, the procedure is executed - this is a minor issue. Then, by default, the procedure will be executed every time the document is refreshed.

You can change some settings of the data providers you create. Goto to "Data" menu, then "Data providers" or "Data Admin" or something like that (I use the spanish version). Have a look.

If you uncheck the "can be refreshed" check box of the data provider, the procedure will not be executed when the document is refreshed. The only way then to executed it is to invoke
[tt]
DataProviders("SProc&quot.Refresh
[/tt]
from VBA code. So, now you have direct control on when the procedure will be executed.

If you want to ask the user -just before the document is refreshed- if he wants to execute the stored procedure, add the following VBA procedure to the "This Document" module of the document (VBA editor window: ALT+F11):
[tt]
Private Sub Document_BeforeRefresh()
Dim Response As Integer
Response = MsgBox("Do you want to execute the sp?", _
vbQuestion Or vbYesNo, "Stored Procedure&quot
If Response = vbYes Then DataProviders("SProc&quot.Refresh
End Sub
[/tt]
Note: MsgBox only works in the full-client, not in Web Intelligence.

I hope I'm clear now.