Stored Procedure in Crystal Reports

 It's normally pretty straightforward.  You make the SP the datasource for the report, run the report, and it runs the SP.  Are you sure that you're entering a valid value for the parameter?  What data type is the parameter?  It might help if you could post your report (the RPT file).

 James

Yes I'm sure.  It uses a datetime and pops up the date picker.

WIP_Balance.rpt

Here is the stored procedure.

ALTER PROCEDURE [DBO].WIPBAL_REPORT

@POSTING_DATE datetime

AS



--DECLARE @POSTING_DATE datetime
--SET @POSTING_DATE = '3/31/2009'

if object_id('dbo.#TMP_WIP_ISS_DIST') is not null
DROP TABLE  dbo.#TMP_WIP_ISS_DIST

SELECT     WIP_ISSUE_DIST.WORKORDER_BASE_ID, WIP_ISSUE_DIST.WORKORDER_LOT_ID, WIP_ISSUE_DIST.WORKORDER_SPLIT_ID,
                      WIP_ISSUE_DIST.WORKORDER_SUB_ID, MAX(WIP_ISSUE_DIST.DIST_NO) AS MAX_DIST
INTO dbo.#TMP_WIP_ISS_DIST
FROM         WIP_ISSUE_DIST INNER JOIN
                      WIP_BALANCE ON WIP_ISSUE_DIST.WORKORDER_TYPE = WIP_BALANCE.WORKORDER_TYPE AND
                      WIP_ISSUE_DIST.WORKORDER_BASE_ID = WIP_BALANCE.WORKORDER_BASE_ID AND
                      WIP_ISSUE_DIST.WORKORDER_LOT_ID = WIP_BALANCE.WORKORDER_LOT_ID AND
                      WIP_ISSUE_DIST.WORKORDER_SPLIT_ID = WIP_BALANCE.WORKORDER_SPLIT_ID AND
                      WIP_ISSUE_DIST.WORKORDER_SUB_ID = WIP_BALANCE.WORKORDER_SUB_ID
WHERE  (WIP_ISSUE_DIST.POSTING_DATE <= @POSTING_DATE) AND WIP_BALANCE.POSTING_DATE =@POSTING_DATE  
GROUP BY WIP_ISSUE_DIST.WORKORDER_BASE_ID, WIP_ISSUE_DIST.WORKORDER_LOT_ID, WIP_ISSUE_DIST.WORKORDER_SPLIT_ID,
                      WIP_ISSUE_DIST.WORKORDER_SUB_ID

if object_id('dbo.#TMP_WIP_REC_DIST') is not null
DROP TABLE  dbo.#TMP_WIP_REC_DIST

SELECT     WIP_RECEIPT_DIST.WORKORDER_BASE_ID, WIP_RECEIPT_DIST.WORKORDER_LOT_ID, WIP_RECEIPT_DIST.WORKORDER_SPLIT_ID,
                      WIP_RECEIPT_DIST.WORKORDER_SUB_ID, MAX(WIP_RECEIPT_DIST.DIST_NO) AS MAX_DIST
INTO dbo.#TMP_WIP_REC_DIST
FROM         WIP_RECEIPT_DIST INNER JOIN
                      WIP_BALANCE ON WIP_RECEIPT_DIST.WORKORDER_TYPE = WIP_BALANCE.WORKORDER_TYPE AND
                      WIP_RECEIPT_DIST.WORKORDER_BASE_ID = WIP_BALANCE.WORKORDER_BASE_ID AND
                      WIP_RECEIPT_DIST.WORKORDER_LOT_ID = WIP_BALANCE.WORKORDER_LOT_ID AND
                      WIP_RECEIPT_DIST.WORKORDER_SPLIT_ID = WIP_BALANCE.WORKORDER_SPLIT_ID AND
                      WIP_RECEIPT_DIST.WORKORDER_SUB_ID = WIP_BALANCE.WORKORDER_SUB_ID
WHERE      ( WIP_RECEIPT_DIST.POSTING_DATE<=  @POSTING_DATE)  AND WIP_BALANCE.POSTING_DATE = @POSTING_DATE
GROUP BY WIP_RECEIPT_DIST.WORKORDER_BASE_ID, WIP_RECEIPT_DIST.WORKORDER_LOT_ID, WIP_RECEIPT_DIST.WORKORDER_SPLIT_ID,
                      WIP_RECEIPT_DIST.WORKORDER_SUB_ID

if object_id('dbo.#TMP_WIP_ALL') is not null
DROP TABLE  dbo.#TMP_WIP_ALL

SELECT     dbo.#TMP_WIP_ISS_DIST.WORKORDER_BASE_ID, dbo.#TMP_WIP_ISS_DIST.WORKORDER_LOT_ID,
                      dbo.#TMP_WIP_ISS_DIST.WORKORDER_SPLIT_ID, WIP_PAYB_DETAIL.VOUCHER_ID, WIP_PAYB_DETAIL.VOUCHER_LINE_NO,
                      WIP_PAYB_DETAIL.ACT_MATERIAL_COST, WIP_PAYB_DETAIL.ACT_LABOR_COST, WIP_PAYB_DETAIL.ACT_BURDEN_COST,
                      WIP_PAYB_DETAIL.ACT_SERVICE_COST, PAYABLE_LINE.REFERENCE, PAYABLE.VENDOR_ID, PAYABLE.INVOICE_DATE,
                 PAYABLE.POSTING_DATE, Cast('Vouchers' as  varchar(50)) AS Group_Type, Cast(null as int) as TRANSACTION_ID, Cast(null as varchar(30)) as PART_ID,
                cast(QTY as decimal(14,4)) as QTY, cast(null as varchar(1)) as TYPE,cast(null as varchar(1)) as  CLASS, cast(null as datetime) as TRANSACTION_DATE,
                     cast(null as varchar(15)) as CUST_ORDER_ID, cast(null as int) as CUST_ORDER_LINE_NO
INTO dbo.#TMP_WIP_ALL
FROM         WIP_PAYB_DETAIL INNER JOIN
                      dbo.#TMP_WIP_ISS_DIST ON WIP_PAYB_DETAIL.WORKORDER_BASE_ID = dbo.#TMP_WIP_ISS_DIST.WORKORDER_BASE_ID AND
                      WIP_PAYB_DETAIL.WORKORDER_LOT_ID = dbo.#TMP_WIP_ISS_DIST.WORKORDER_LOT_ID AND
                      WIP_PAYB_DETAIL.WORKORDER_SPLIT_ID = dbo.#TMP_WIP_ISS_DIST.WORKORDER_SPLIT_ID AND
                      WIP_PAYB_DETAIL.WORKORDER_SUB_ID = dbo.#TMP_WIP_ISS_DIST.WORKORDER_SUB_ID AND
                      WIP_PAYB_DETAIL.DIST_NO = dbo.#TMP_WIP_ISS_DIST.MAX_DIST INNER JOIN
                      PAYABLE_LINE ON WIP_PAYB_DETAIL.VOUCHER_ID = PAYABLE_LINE.VOUCHER_ID AND
                      WIP_PAYB_DETAIL.VOUCHER_LINE_NO = PAYABLE_LINE.LINE_NO INNER JOIN
                      PAYABLE ON PAYABLE_LINE.VOUCHER_ID = PAYABLE.VOUCHER_ID AND PAYABLE_LINE.VOUCHER_ID = PAYABLE.VOUCHER_ID
WHERE WIP_PAYB_DETAIL.CURRENCY_ID = 'DEF'
ORDER BY  dbo.#TMP_WIP_ISS_DIST.WORKORDER_BASE_ID, dbo.#TMP_WIP_ISS_DIST.WORKORDER_LOT_ID,
                      dbo.#TMP_WIP_ISS_DIST.WORKORDER_SPLIT_ID,PAYABLE.POSTING_DATE

ALTER TABLE dbo.#TMP_WIP_ALL ALTER COLUMN POSTING_DATE datetime NULL
ALTER TABLE dbo.#TMP_WIP_ALL ALTER COLUMN INVOICE_DATE datetime NULL
ALTER TABLE dbo.#TMP_WIP_ALL ALTER COLUMN VOUCHER_LINE_NO int NULL
ALTER TABLE dbo.#TMP_WIP_ALL ALTER COLUMN VOUCHER_ID varchar(15) NULL

--Labor Tickets
INSERT INTO dbo.#TMP_WIP_ALL(WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID,ACT_MATERIAL_COST,ACT_LABOR_COST,
                               ACT_BURDEN_COST,ACT_SERVICE_COST,TRANSACTION_ID,QTY,TRANSACTION_DATE,Group_Type)
SELECT     WIP_LABOR_DETAIL.WORKORDER_BASE_ID, WIP_LABOR_DETAIL.WORKORDER_LOT_ID, WIP_LABOR_DETAIL.WORKORDER_SPLIT_ID,
                      WIP_LABOR_DETAIL.ACT_MATERIAL_COST, WIP_LABOR_DETAIL.ACT_LABOR_COST, WIP_LABOR_DETAIL.ACT_BURDEN_COST,
                      WIP_LABOR_DETAIL.ACT_SERVICE_COST, WIP_LABOR_DETAIL.TRANSACTION_ID, LABOR_TICKET.HOURS_WORKED,LABOR_TICKET.TRANSACTION_DATE,
                  'Labor Tickets' as Group_type
FROM         dbo.#TMP_WIP_ISS_DIST INNER JOIN
                      WIP_LABOR_DETAIL ON dbo.#TMP_WIP_ISS_DIST.WORKORDER_BASE_ID = WIP_LABOR_DETAIL.WORKORDER_BASE_ID AND
                      dbo.#TMP_WIP_ISS_DIST.WORKORDER_LOT_ID = WIP_LABOR_DETAIL.WORKORDER_LOT_ID AND
                      dbo.#TMP_WIP_ISS_DIST.WORKORDER_SPLIT_ID = WIP_LABOR_DETAIL.WORKORDER_SPLIT_ID AND
                      dbo.#TMP_WIP_ISS_DIST.WORKORDER_SUB_ID = WIP_LABOR_DETAIL.WORKORDER_SUB_ID AND
                      dbo.#TMP_WIP_ISS_DIST.MAX_DIST = WIP_LABOR_DETAIL.DIST_NO INNER JOIN
                      LABOR_TICKET ON WIP_LABOR_DETAIL.TRANSACTION_ID = LABOR_TICKET.TRANSACTION_ID
WHERE WIP_LABOR_DETAIL.CURRENCY_ID = 'DEF'

--Material Issues
INSERT INTO dbo.#TMP_WIP_ALL(WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID,ACT_MATERIAL_COST,ACT_LABOR_COST,
                               ACT_BURDEN_COST,ACT_SERVICE_COST,TRANSACTION_ID,PART_ID,QTY,TYPE,CLASS,TRANSACTION_DATE,Group_Type)
SELECT     WIP_ISSUE_DETAIL.WORKORDER_BASE_ID, WIP_ISSUE_DETAIL.WORKORDER_LOT_ID, WIP_ISSUE_DETAIL.WORKORDER_SPLIT_ID,
                      WIP_ISSUE_DETAIL.ACT_MATERIAL_COST, WIP_ISSUE_DETAIL.ACT_LABOR_COST, WIP_ISSUE_DETAIL.ACT_BURDEN_COST,
                      WIP_ISSUE_DETAIL.ACT_SERVICE_COST,  WIP_ISSUE_DETAIL.TRANSACTION_ID, WIP_ISSUE_DETAIL.PART_ID,
                       INVENTORY_TRANS.QTY, INVENTORY_TRANS.TYPE, INVENTORY_TRANS.CLASS, INVENTORY_TRANS.TRANSACTION_DATE,
                      'Material Issues' AS Group_Type
FROM         WIP_ISSUE_DETAIL INNER JOIN
                      dbo.#TMP_WIP_ISS_DIST ON WIP_ISSUE_DETAIL.WORKORDER_BASE_ID = dbo.#TMP_WIP_ISS_DIST.WORKORDER_BASE_ID AND
                      WIP_ISSUE_DETAIL.WORKORDER_LOT_ID = dbo.#TMP_WIP_ISS_DIST.WORKORDER_LOT_ID AND
                      WIP_ISSUE_DETAIL.WORKORDER_SPLIT_ID = dbo.#TMP_WIP_ISS_DIST.WORKORDER_SPLIT_ID AND
                      WIP_ISSUE_DETAIL.WORKORDER_SUB_ID = dbo.#TMP_WIP_ISS_DIST.WORKORDER_SUB_ID AND
                      WIP_ISSUE_DETAIL.DIST_NO = dbo.#TMP_WIP_ISS_DIST.MAX_DIST INNER JOIN
                      INVENTORY_TRANS ON WIP_ISSUE_DETAIL.TRANSACTION_ID = INVENTORY_TRANS.TRANSACTION_ID
WHERE WIP_ISSUE_DETAIL.CURRENCY_ID = 'DEF'
ORDER BY WIP_ISSUE_DETAIL.WORKORDER_BASE_ID, WIP_ISSUE_DETAIL.WORKORDER_LOT_ID, WIP_ISSUE_DETAIL.WORKORDER_SPLIT_ID,
                      INVENTORY_TRANS.TRANSACTION_DATE

INSERT INTO dbo.#TMP_WIP_ALL(WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID,ACT_MATERIAL_COST,ACT_LABOR_COST,
                               ACT_BURDEN_COST,ACT_SERVICE_COST,TRANSACTION_ID,CUST_ORDER_ID,CUST_ORDER_LINE_NO,QTY,TYPE,CLASS,TRANSACTION_DATE,Group_Type)
SELECT     WIP_RECEIPT_DETAIL.WORKORDER_BASE_ID, WIP_RECEIPT_DETAIL.WORKORDER_LOT_ID, WIP_RECEIPT_DETAIL.WORKORDER_SPLIT_ID,
                      WIP_RECEIPT_DETAIL.ACT_MATERIAL_COST*-1, WIP_RECEIPT_DETAIL.ACT_LABOR_COST*-1, WIP_RECEIPT_DETAIL.ACT_BURDEN_COST*-1, WIP_RECEIPT_DETAIL.ACT_SERVICE_COST*-1,
                      WIP_RECEIPT_DETAIL.TRANSACTION_ID, INVENTORY_TRANS.CUST_ORDER_ID,  INVENTORY_TRANS.CUST_ORDER_LINE_NO,
                      INVENTORY_TRANS.QTY, INVENTORY_TRANS.TYPE, INVENTORY_TRANS.CLASS, INVENTORY_TRANS.TRANSACTION_DATE,
                      'Shipments/Receipts' AS Group_Type
FROM         WIP_RECEIPT_DETAIL INNER JOIN
                      dbo.#TMP_WIP_REC_DIST ON WIP_RECEIPT_DETAIL.WORKORDER_BASE_ID = dbo.#TMP_WIP_REC_DIST.WORKORDER_BASE_ID AND
                      WIP_RECEIPT_DETAIL.WORKORDER_LOT_ID = dbo.#TMP_WIP_REC_DIST.WORKORDER_LOT_ID AND
                      WIP_RECEIPT_DETAIL.WORKORDER_SPLIT_ID = dbo.#TMP_WIP_REC_DIST.WORKORDER_SPLIT_ID AND
                      WIP_RECEIPT_DETAIL.WORKORDER_SUB_ID = dbo.#TMP_WIP_REC_DIST.WORKORDER_SUB_ID AND
                      WIP_RECEIPT_DETAIL.DIST_NO = dbo.#TMP_WIP_REC_DIST.MAX_DIST INNER JOIN
                      INVENTORY_TRANS ON WIP_RECEIPT_DETAIL.TRANSACTION_ID = INVENTORY_TRANS.TRANSACTION_ID
WHERE WIP_RECEIPT_DETAIL.CURRENCY_ID = 'DEF'
ORDER BY WIP_RECEIPT_DETAIL.WORKORDER_BASE_ID, WIP_RECEIPT_DETAIL.WORKORDER_LOT_ID, WIP_RECEIPT_DETAIL.WORKORDER_SPLIT_ID,
                      INVENTORY_TRANS.TRANSACTION_DATE

SELECT  WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, VOUCHER_ID, VOUCHER_LINE_NO, ACT_MATERIAL_COST,
                      ACT_LABOR_COST, ACT_BURDEN_COST, ACT_SERVICE_COST, REFERENCE, VENDOR_ID, INVOICE_DATE, POSTING_DATE, Group_Type,
                      TRANSACTION_ID, PART_ID, QTY, TYPE, CLASS, TRANSACTION_DATE, CUST_ORDER_ID, CUST_ORDER_LINE_NO
  FROM dbo.#TMP_WIP_ALL ORDER BY WORKORDER_BASE_ID,WORKORDER_LOT_ID,WORKORDER_SPLIT_ID,TRANSACTION_ID

DROP TABLE dbo.#TMP_WIP_ISS_DIST
DROP TABLE dbo.#TMP_WIP_REC_DIST
DROP TABLE dbo.#TMP_WIP_ALL

 I would start by removing the DROP statements at the end.  They shouldn't be necessary, and I've had problems with CR using stored procedures that produced messages, like row counts, in addition to the data.  If those DROP statements produce any messages, they may be "confusing" CR.

 Along the same lines, you could try adding

SET ANSI_WARNINGS OFF
SET NOCOUNT ON

 at the beginning of the SP (before you start filling your temp tables).  I had cases where warning and/or row count messages appeared to be causing problems for a report (or, to put it another way, a report wasn't working, and suppressing those messages in the SP fixed it).

 James

Agree with James.

mlmcc

James 0628,

I did as you suggested and it still doesn't pull any information.

 I don't know.  Like I said, it's usually pretty straightforward.

 Shot in the dark, but why do you have the Alter statements in the middle?  That's something that I never used, so I don't know if that could cause problems in CR.  You could try commenting out those statements.

 You could right-click on one of the SP fields in Field Explorer and select Browse Data and see if you get anything (just trying to run the SP in a slightly different way).

 If nothing else works, I would try breaking the SP down into pieces and using those in a report.  For example, fill the first temp table and then use Select on that temp table and see if that data appears in a report, and so on.

 James

Commented out those statements but still nothing.

I did the browse data and no data shows.

I'll try breaking it down.  I didn't write the procedure.  It got it from someone on my ERP forum and have asked them but they have no idea.

I don't have time to review the actual procedure, but if it is working in SSMS and doesn't work when used in Crystal reports try adding "set nocount on" at the beginning of the body of the stored procedure

something like this:

ALTER PROCEDURE [DBO].WIPBAL_REPORT
@POSTING_DATE datetime
AS 

set nocount on

--DECLARE @POSTING_DATE datetime
--SET @POSTING_DATE = '3/31/2009'

if object_id('dbo.#TMP_WIP_ISS_DIST') is not null
DROP TABLE  dbo.#TMP_WIP_ISS_DIST 

I already did that and it still doesn't work.

I've narrowed it down to the @posting_date.  It's using datetime and CR tries to use both date and time.  I just need the @posting_date to be the date only.  How would I do that?