Call a stored procedure from an SSAS action

Hello, I'm just wondering if anyone has every created an action that called a stored procedure. I've been looking into Proprietary actions of statement actions but I cannot find any documentation of examples. Is this possible? One option was to create a dummy ssrs report and then call some custom .Net code. But I want to avoid using a technology just as a work around. Any ideas?

thanks.

To call sproc from action is not a problem - the question is - what do you want to return from this sproc ? If you return a string to be used in URL action - then it will be trivial, just use sproc in the MDX expression. If you want to return rowset, it is a little bit more work, but should be possible too.|||

Actually, I don't need it to return anything. This is SQL Server sproc that I will pass a few parameters and it will execute an SSIS package. The only thing that I have read to accomplish this, is to create a small .net web app that will call this sproc, that way I can simply use a url reporting action and pass the parameters to the web app.

I am wondering if it is possible to call the SQL server sproc directly from a reporting action?

|||OK - I thought you were talking about SSAS stored procedure. There is no direct way to call SQL Server sproc, so either you do it through web app and implement an URL action, or you code SSAS stored procedure which calls SQL Server stored procedure.|||

Hi,

I decided to create an SSAS stored procedure that would call a SQL server stored procedure. I made a call to the sp in a URL action like this:

"Http://localhost/ReportServer/Pages/ReportViewer.aspx?/SampleReport/Report&Param1=" & AssemblyName!StoreProc()

The report would simply say the the procedure has been executed successfully.

This works great but the only problem is that the stored procedure fires as soon as you right click the cell. Is there a setting or something that I need to set so that the stored procedure only fires when the user clicks on the action?

Thanks in advance for your help.

|||

Hi,

Hi after some further reading I figure out what was going on. When right clicking on the cell the server would resolve all actions and return the resulting string to the client, therefore the result was that the ssas stored procedure was getting executed before the action was returned to the client. Solution: I changed the action type to "statement" and simply used a "Call StoredProcedure()" statement in the action expression.