locked
PowerPivot for Excel 2013, call execute stored procedure with parameters RRS feed

  • Question

  • Hi, I have a stored procedure in my database with three parameters.  If from Excel use Data>From Other Sources>From Microsoft Query and then use statement like {CALL MyDB.DBO.mySP (?,?,?)} then when I refresh it asks me for the parameters to enter.  I want to see if I can do the same from PowerPivot.  The only option I get is when I get prompted to "Choose How to Import the Data" I select Write a query that will ..... radio check box.  To specify my query the call doesn't work.  The exec works but doesn't like the ? for parameters. 

    I am wondering if this possible or not?

    thanks

    Wednesday, May 15, 2013 4:44 PM

Answers

  • Don't believe you can do it directly in the query definition in a PowerPivot table's properties.  But you can manipulate the connection string with VBA to pass a parameter based on an interaction/cell-value on the Excel side.

    Check out these blog posts for examples:

    Kasper de Jonge's post here

    Chris Webb's post here


    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

    • Proposed as answer by Elvis Long Friday, May 17, 2013 7:30 AM
    • Marked as answer by Elvis Long Monday, May 27, 2013 2:58 AM
    Wednesday, May 15, 2013 10:01 PM
    Answerer