Problem Building Pivot Chart Based on Stored Procedure

[Andro Dgebuadze]

Hi there

I am having a problem building Pivot Chart based on this particular Stored
Procedure, quoted below.
Any Idea why?


I am using Access 2002 and SQL Server 2000.

===========================================

CREATE PROCEDURE dbo.STOCK_INVENTORY_LEVELS
(@item as int)

AS

SET NOCOUNT ON

CREATE TABLE #t (DT datetime)

DECLARE @dt as datetime
DECLARE @dt1 as datetime
DECLARE @dt2 as datetime

SET @dt2 = CONVERT(datetime, ROUND(CONVERT(float, GETDATE()), 0, 1))
SET @dt1 = @dt2-60

SET @dt = @dt1

WHILE @dt <= @dt2

BEGIN

INSERT INTO #t VALUES ( @dt )

SET @dt = @dt + 1

END

SELECT ITEM_ID, #t.DT, CAST(SUM(QTY) AS DECIMAL(18,0)) AS QTY FROM Stocks AS
S (NOLOCK)
JOIN #t ON S.TRANS_DATE=(SELECT MAX(SS.TRANS_DATE) FROM Stocks AS SS(NOLOCK)
WHERE
SS.ITEM_ID = S.ITEM_ID AND
SS.AFE_ID = S.AFE_ID AND
SS.LOC_ID = S.LOC_ID AND
SS.COMPANY_ID = S.COMPANY_ID AND
SS.TRANS_DATE <= #t.DT )

WHERE ITEM_ID = @item AND LOC_ID <>1

GROUP BY ITEM_ID, #t.DT

DROP TABLE #t
GO

 

[aaron.kempf]

what's your problem with the pivotChart?

i'd bind the Sproc to a form;
have a control named 'item' on the form and set the value of that
control from a vba function

then you can around most of the ugliness

i wouldn't bother doing stuff directly against the sproc in pivotChart
view-- i just dont think that is robust enough; and that
dt_extendedproperties info is basically impossible to restore; so
putting too much effort into that are might cause you a lot of grief.

just keeping it on a form will keep your eggs on the ADP file instead
of the dt_extendedproperties table or whatever