locked
PowerPivot tables and stored procedures RRS feed

  • Question

  • What can I use to dynamically create a PowerPivot table in excel, using a stored procedure as a data source?

    Onyx12

    Tuesday, July 29, 2014 9:24 AM

Answers

  • As far as I know you cant make it dynamic. You need to include command statically as EXEC proc etc inside powerpivot

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by tracycai Friday, August 1, 2014 2:21 AM
    • Marked as answer by Sofiya Li Friday, August 8, 2014 5:52 AM
    Tuesday, July 29, 2014 10:12 AM

All replies

  • Hello,

    Your question is not very clear for me, especially what you mean with "dynamically"?

    Yes, you can use store procedure as a data source for a Power Pivot table.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, July 29, 2014 9:35 AM
  • Hi there

    Thank you for your response.

    Here's what I'm trying to achieve and I stand under correction. I have a stored procedure which I want to mirror it's query result as a Pivot Table in Excel, and have done so by hard coding the execution code and imported it to Excel. But now I'm trying to find out if there is another way to do this, in Excel though. I've been looking at solutions which include building macros, but I'm afraid SSMS does not support them. And even so, I'm not sure if building a macro is an ideal solution in this case. In essence, I'm trying to retrieve data using a stored procedure, through the use of TSQL code in Excel. Please see query I've provided to Excel, using PowerPivot. I hope I've articulated myself thoroughly.

    DECLARE @RC int
    DECLARE @as_at_date datetime
    DECLARE @all_versions bit
    DECLARE @include_motor bit
    DECLARE @include_reserve bit

    -- TODO: Set parameter values here.
    SET @as_at_date = GETDATE()
    SET @all_versions  = 0
    SET @include_motor  = 0
    SET @include_reserve  = 0

    EXECUTE @RC = [dbo].[usp_claims_by_reserve] 
       @as_at_date
      ,@all_versions
      ,@include_motor
      ,@include_reserve


    Onyx12

    Tuesday, July 29, 2014 9:53 AM
  • As far as I know you cant make it dynamic. You need to include command statically as EXEC proc etc inside powerpivot

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by tracycai Friday, August 1, 2014 2:21 AM
    • Marked as answer by Sofiya Li Friday, August 8, 2014 5:52 AM
    Tuesday, July 29, 2014 10:12 AM
  •  Thank you

    Onyx12

    Tuesday, July 29, 2014 11:06 AM