none
Passing parameters from Excel to SQL stored proc. to analyse resultset in PowerPivot RRS feed

  • Question

  • Hi,

    Not sure if I posted this question at the right forum ...

    I would like to implement the following scenario:
    - Enter parameters @startdate and @enddate in cells in an Excel worksheet (i.e. cell A2 has the value for the startdate parameter; cell B2 has the value for the endate parameter).
    - Pass these parameters to a SQL stored procedure (using MSQuery?). See below.
    - Calling stored procedure in PowerPivot to get the resultset in PowerPivot.

    The SP calls some functions in SQL Server. See below.
    I have read several posts on several forums but I can't get the parameters from Excel (MSQuery?) to the SP.

    What's the best way to have PowerPivot picking up the resultset from the SP?
    How do I get the Excel cells A2 and B2 to the SP below?

    SP:

    USE [Test]
    GO
    
    /****** Object:  StoredProcedure [dbo].[_Pink_SP_CapaciteitTest]    Script Date: 29-7-2014 15:41:04 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[_Pink_SP_CapaciteitTest]
    (
    @startdate DATETIME,
    @enddate DATETIME
    )
    
    AS
    BEGIN
    
    SET NOCOUNT ON
    SET DATEFIRST 1
    
    DECLARE @TempCapacity TABLE
    	(
    	ResourceNo INT,
    	ResourceName NVARCHAR(60),
    	JobCode NVARCHAR(12),
    	JobDescription NVARCHAR(50),
    	CostcenterCode NCHAR(8),
    	CostcenterDescription NVARCHAR(50),
    	CostcenterClass NVARCHAR(30),
    	CostcenterClassDescription NVARCHAR(60),
    	Date DATETIME,
    	Weekday INT,
    	WeekNo INT,
    	Month INT,
    	Year INT,
    	Capacity FLOAT,
    	ConsultancyTot FLOAT,
    	ConsultancyTotReserved FLOAT,
    	Sick FLOAT,
    	Doctor FLOAT,
    	Pregnant FLOAT,
    	Vacation FLOAT,
    	VacationCancellation FLOAT,
    	SpecialLeave FLOAT,
    	CompHours FLOAT,
    	Support FLOAT
    	)
    
    INSERT INTO @TempCapacity
    
    SELECT	h.res_id AS ResourceNo,
    		h.fullname AS ResourceName,
    		h.job_title AS JobCode,
    		j.descr50 AS JobDescription,
    		h.costcenter AS CostcenterCode,
    		cc.oms25_0 AS CostcenterDescription,
    		ccc.CostcenterClassCode AS CostcenterClass,
    		ccc.Description AS CostcenterClassDescription,
    		CONVERT(VARCHAR(10), t.datum, 105) AS [Date],
    		DATEPART(DW, t.datum) AS [Weekday],
    		(SELECT [dbo].[ISOWeekNumber] (t.datum)) AS WeekNo,
    		MONTH(t.datum) AS [Month],
    		YEAR(t.datum) AS [Year],
    		(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datum, t.datum), 2)) AS Capacity,
    		(SELECT ISNULL([dbo].[HRAbsenceHours] (50, h.res_id, t.datum, t.datum + 1), 0)) AS ConsultancyTot,
    		(SELECT ISNULL([dbo].[HRAbsenceHours] (51, h.res_id, t.datum, t.datum + 1), 0)) AS ConsultancyTotReserved,
    		(SELECT ISNULL([dbo].[HRAbsenceHours] (9538, h.res_id, t.datum, t.datum + 1), 0)) AS Sick,
    		(SELECT ISNULL([dbo].[HRAbsenceHours] (8531, h.res_id, t.datum, t.datum + 1), 0)) AS Doctor,
    		(SELECT ISNULL([dbo].[HRAbsenceHours] (9924, h.res_id, t.datum, t.datum + 1), 0)) AS Pregnant,
    		(SELECT ISNULL([dbo].[HRAbsenceHours] (8501, h.res_id, t.datum, t.datum + 1), 0)) AS Vacation,
    		(SELECT ISNULL([dbo].[HRAbsenceHours] (8551, h.res_id, t.datum, t.datum + 1), 0)) AS VacationCancellation,
    		(SELECT ISNULL([dbo].[HRAbsenceHours] (8511, h.res_id, t.datum, t.datum + 1), 0)) AS SpecialLeave,
    		(SELECT ISNULL([dbo].[HRAbsenceHours] (9518, h.res_id, t.datum, t.datum + 1), 0)) AS CompHours,
    		(SELECT ISNULL([dbo].[HRAbsenceHours] (3200, h.res_id, t.datum, t.datum + 1), 0)) AS Support
    			
    FROM	humres h (NOLOCK)
    
    LEFT OUTER JOIN hrjbtl j (NOLOCK) ON h.job_title = j.job_title
    LEFT OUTER JOIN kstpl cc (NOLOCK) ON h.costcenter = cc.kstplcode
    LEFT OUTER JOIN CostcenterClasses ccc (NOLOCK) ON cc.Class_01 = ccc.CostcenterClassCode AND ccc.ClassID = 1
    
    CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t
    
    WHERE	h.ldatindienst <= t.datum
    		AND ISNULL(h.ldatuitdienst, t.datum) >= t.datum
    		AND h.fullname NOT LIKE '%inhuur%'
    		AND h.emp_type IN ('E', 'C')
    		AND h.job_title IN ('F09CONS', 'F09PRIN')
    
    ORDER BY h.fullname, 
    		t.datum
    
    
    SELECT * FROM TempCapacity
    
    
    END

    Thanks!

    Tuesday, July 29, 2014 9:10 PM

Answers

  • Hi,

    According to your description, I think you want to call a store procedure in PowerPivot with the parameters which are stored in the cells of an Excel workbook.

    Are you using the PowerPivot add-in in Excel or PowerPivot in SQL Server?

    This forum is to discuss problems of Office development such as VBA, VSTO, Apps for Office .etc. If you are using Excel PowerPivot, since it is an add-in for Excel and it doesn't publish API for us, we cannot automatically call a stored procedure in Excel. We can get the data from Cells A2 and B2 with code, but it's hard to set it as the parameters of a SP when calling it from PowerPivot. About calling a SP from Excel manually, you could post in Excel IT pro forum for more effective responses.

    If you are using PowerPivot in SQL Server, I'm afraid your issue is more related to the feature of PowerPivot. We can get data from SQL Server database into Excel workbook, but I'm not sure whether we can get data from Excel cells in SQL Server. So I suggest you posting in SQL Server PowerPivot forum for more effective responses.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by bijntjede2e Tuesday, August 12, 2014 7:13 PM
    Wednesday, July 30, 2014 9:02 AM