SSRS Report Not Pulling Data from Stored Procedure

Question

The stored procedure below works. But, it is not displaying data in the SQL Reporting Services report (SSRS report).

It must be a temp table that is not displaying data.

How can I get the SSRS report to pull the data?

The original stored procedure is the top half of the current stored procedure.

We modified the stored procedure by copying "top to bottom" and adding code to the top half's select statement's "where" clause to account for inventory item numbers (ITEMNMBR) that end with either the word "CUSTOM" or "SPECIAL".

We want both halves of the stored procedure to run--loop or no loop--in the sense that, if the item is "CUSTOM" or "SPECIAL", then run the top half of the stored procedure. If the item number is neither of these, then run the bottom portion.

It's akin to an "if-then" statement.

At first, I tried inserting a "UNION ALL" clause in between. But, SQL gave me a syntax error, until I removed it.

Please let me know your thoughts.

Thanks!

John

ALTER procedure [dbo].[SRS_TransferBackOrdersToWareHouseBatch]
as
declare @sourceBatchRecordCount int
declare @destBatchRecordCount int
declare @destBatch varchar(30)
declare @sourceBatch varchar(30)

set @sourceBatch = 'BACKORDER'
set @destBatch = 'WAREHOUSE'

select @sourceBatchRecordCount = COUNT(*) from SOP10100 where bachnumb = @sourceBatch
select @destBatchRecordCount = COUNT(*) from SOP10100 where BACHNUMB = @sourceBatch and SOPNUMBE in (select SOPNUMBE from SOP10200 where 
(ITEMNMBR LIKE '%CUSTOM' AND 
ATYALLOC > 0) OR (ITEMNMBR LIKE '%SPECIAL' AND 
ATYALLOC > 0))
select sopnumbe into #movedBO from SOP10100 where BACHNUMB = @sourceBatch and SOPNUMBE in (select SOPNUMBE from SOP10200 where 
(ITEMNMBR LIKE '%CUSTOM' AND 
ATYALLOC > 0) OR (ITEMNMBR LIKE '%SPECIAL' AND 
ATYALLOC > 0))

--print @sourceBatchRecordCount 
--print @destBatchRecordCount 

if @destBatchRecordCount > 0 
begin

       declare @p30 int
       set @p30=0
       declare @p31 varchar(255)
       set @p31=''
       exec taCreateUpdateBatchHeaderRcd @I_vBACHNUMB=@destBatch,@I_vBCHCOMNT=default,@I_vSERIES=3,@I_vGLPOSTDT=default,@I_vBCHSOURC='Sales Entry    ',
@I_vDOCAMT=0,@I_vORIGIN=1,@I_vNUMOFTRX=0,@I_vCHEKBKID=default,@I_vCNTRLTOT=default,@I_vCNTRLTRX=default,@I_vPOSTTOGL=0,@I_vPmtMethod=default,
@I_vEFTFileFormat=default,@I_vRequesterTrx=default,@I_vBRKDNALL=default,@I_vUSERID=default,@I_vBACHFREQ=default,@I_vRECPSTGS=default,
@I_vMSCBDINC=default,@I_vAPPROVL=default,@I_vAPPRVLDT=default,@I_vAPRVLUSERID=default,@I_vTRXSOURC=default,@I_vUSRDEFND1=default,
@I_vUSRDEFND2=default,@I_vUSRDEFND3=default,@I_vUSRDEFND4=default,@I_vUSRDEFND5=default,@O_iErrorState=@p30 output,@oErrString=@p31 output

       --EMOIS 9/2/2105 Change update to single update statement for each row, this will cause trigger to fire correctly
       DECLARE @sopNumber CHAR(21)
       DECLARE updateSOP CURSOR FOR
              SELECT RTRIM(SOPNUMBE) FROM SOP10100 WHERE BACHNUMB = @sourceBatch and SOPNUMBE in (select SOPNUMBE from SOP10200 where 
                      (ITEMNMBR LIKE '%CUSTOM' AND 
ATYALLOC > 0) OR (ITEMNMBR LIKE '%SPECIAL' AND 
ATYALLOC > 0))

       OPEN updateSOP FETCH NEXT FROM updateSOP INTO @sopNumber
       WHILE @@FETCH_STATUS = 0
       BEGIN
              UPDATE SOP10100
                     SET BACHNUMB = @destBatch --ReqShipDate = CONVERT(date, getDate()) Removed to keep original ReqShipDate
                     FROM SOP10100 WHERE SOPNUMBE = @sopNumber

              FETCH NEXT FROM updateSOP INTO @sopNumber
       END
       CLOSE updateSOP DEALLOCATE updateSOP

       select rtrim(sopnumbe) as 'BackOrderNo' from #movedBO 

       -- reconcile batch headers

       set nocount on
       DECLARE @bachnumb char(25)
       , @numtrx int
       , @batchamount numeric(19,5)

       DECLARE c_sop CURSOR FOR  
       SELECT rtrim(BACHNUMB) FROM SY00500  WHERE (SERIES = 3) AND (BCHSOURC = 'Sales Entry') order by 1

       OPEN c_sop FETCH NEXT FROM c_sop INTO @bachnumb

       WHILE @@FETCH_STATUS = 0
       BEGIN  SELECT @numtrx = ISNULL(COUNT(SOPNUMBE),0)
       , @batchamount = ISNULL(SUM(DOCAMNT),0)  
       FROM SOP10100  WHERE BACHNUMB = @bachnumb

         UPDATE SY00500 SET NUMOFTRX = @numtrx, BCHTOTAL = @batchamount  WHERE BACHNUMB = @bachnumb
       --Print rtrim(@bachnumb) +' ,Transactions = '+COnvert(varchar(100),@numtrx) +' ,BatchTotal = '+COnvert(varchar(100),@batchamount)
         FETCH NEXT FROM c_sop INTO @bachnumb
         END
         CLOSE c_sop DEALLOCATE c_sop
end
else
       select 'No documents.' as 'BackOrderNo'

--declare @sourceBatchRecordCount int
--declare @destBatchRecordCount int
--declare @destBatch varchar(30)
--declare @sourceBatch varchar(30)

--set @sourceBatch = 'BACKORDER'
--set @destBatch = 'WAREHOUSE'


select @sourceBatchRecordCount = COUNT(*) from SOP10100 where bachnumb = @sourceBatch
select @destBatchRecordCount = COUNT(*) from SOP10100 where BACHNUMB = @sourceBatch and SOPNUMBE in (select SOPNUMBE from SOP10200 where 
ATYALLOC > 0)

select sopnumbe into #movedBO2 from SOP10100 where BACHNUMB = @sourceBatch and SOPNUMBE in (select SOPNUMBE from SOP10200 where 
ATYALLOC > 0)

--print @sourceBatchRecordCount 
--print @destBatchRecordCount 

if @destBatchRecordCount > 0 
begin


       set @p30=0
       set @p31=''
       exec taCreateUpdateBatchHeaderRcd @I_vBACHNUMB=@destBatch,@I_vBCHCOMNT=default,@I_vSERIES=3,@I_vGLPOSTDT=default,@I_vBCHSOURC='Sales Entry    ',
@I_vDOCAMT=0,@I_vORIGIN=1,@I_vNUMOFTRX=0,@I_vCHEKBKID=default,@I_vCNTRLTOT=default,@I_vCNTRLTRX=default,@I_vPOSTTOGL=0,@I_vPmtMethod=default,
@I_vEFTFileFormat=default,@I_vRequesterTrx=default,@I_vBRKDNALL=default,@I_vUSERID=default,@I_vBACHFREQ=default,@I_vRECPSTGS=default,
@I_vMSCBDINC=default,@I_vAPPROVL=default,@I_vAPPRVLDT=default,@I_vAPRVLUSERID=default,@I_vTRXSOURC=default,@I_vUSRDEFND1=default,
@I_vUSRDEFND2=default,@I_vUSRDEFND3=default,@I_vUSRDEFND4=default,@I_vUSRDEFND5=default,@O_iErrorState=@p30 output,@oErrString=@p31 output

       --EMOIS 9/2/2105 Change update to single update statement for each row, this will cause trigger to fire correctly
       DECLARE updateSOP CURSOR FOR
              SELECT RTRIM(SOPNUMBE) FROM SOP10100 WHERE BACHNUMB = @sourceBatch and SOPNUMBE in (select SOPNUMBE from SOP10200 where 
ATYALLOC > 0)

       OPEN updateSOP FETCH NEXT FROM updateSOP INTO @sopNumber
       WHILE @@FETCH_STATUS = 0
       BEGIN
              UPDATE SOP10100
                     SET BACHNUMB = @destBatch,  ReqShipDate = getDate() --Removed to keep original ReqShipDate
                     FROM SOP10100 WHERE SOPNUMBE = @sopNumber

                     --UPDATE SOP10100          SET ReqShipDate = CONVERT(date, getDate()) 
                     --            FROM SOP10100 WHERE SOPNUMBE = @sopNumber

              FETCH NEXT FROM updateSOP INTO @sopNumber
       END
       CLOSE updateSOP DEALLOCATE updateSOP

       select rtrim(sopnumbe) as 'BackOrderNo' from #movedBO 

       -- reconcile batch headers

       set nocount on

       DECLARE c_sop CURSOR FOR  
       SELECT rtrim(BACHNUMB) FROM SY00500  WHERE (SERIES = 3) AND (BCHSOURC = 'Sales Entry') order by 1

       OPEN c_sop FETCH NEXT FROM c_sop INTO @bachnumb

       WHILE @@FETCH_STATUS = 0
       BEGIN  SELECT @numtrx = ISNULL(COUNT(SOPNUMBE),0)
       , @batchamount = ISNULL(SUM(DOCAMNT),0)  
       FROM SOP10100  WHERE BACHNUMB = @bachnumb

         UPDATE SY00500 SET NUMOFTRX = @numtrx, BCHTOTAL = @batchamount  WHERE BACHNUMB = @bachnumb
       --Print rtrim(@bachnumb) +' ,Transactions = '+COnvert(varchar(100),@numtrx) +' ,BatchTotal = '+COnvert(varchar(100),@batchamount)
         FETCH NEXT FROM c_sop INTO @bachnumb
         END
         CLOSE c_sop DEALLOCATE c_sop
end
else
       select 'No documents.' as 'BackOrderNo'

R. Richards R. Richards · Accepted Answer · 2017-04-16T12:46:28

If you want to use this stored procedure with an SSRS report, then you are going to have to change some things for that to work. You have some optons.

First option: break up the stored procedure into 2 stored procedures. SSRS reports can have more than one data set. Have one for the original procedure, and another for the new part. You will end up with 2 tables of data in the report, but the end users may be okay with that.

Second option: move the select statements in the procedure to the end of the code and do the union there. You mentioned trying something like that, but it would not have worked if I understand properly what you described.

select rtrim(sopnumbe) as 'BackOrderNo' from #movedBO
UNION ALL
select rtrim(sopnumbe) as 'BackOrderNo' from #movedBO2 -- See BTW below

Then, handle the “No documents” piece in the RDL by checking for the number of records that were returned and showing/hiding that message in a textbox. You can show/hide the table(s) based on the row count, too. You can use the following expression in the in the Hidden property of a control.

=IIf(CountRows("DataSet1") > 0, False, True) ' Show me
=IIf(CountRows("DataSet1") = 0, True, False) ' Hide me

BTW, you create a table named #movedBO2 in the procedure, but you never select from it later. So, double check code.

EDIT:

Updated stored procedure:

CREATE PROCEDURE [dbo].[SRS_TransferBackOrdersToWareHouseBatch]
as
declare @sourceBatchRecordCount int
declare @destBatchRecordCount int
declare @destBatch varchar(30)
declare @sourceBatch varchar(30)

set @sourceBatch = 'BACKORDER'
set @destBatch = 'WAREHOUSE'

select @sourceBatchRecordCount = COUNT(*) from SOP10100 where bachnumb = @sourceBatch
select @destBatchRecordCount = COUNT(*) from SOP10100 where BACHNUMB = @sourceBatch and SOPNUMBE in (select SOPNUMBE from SOP10200 where 
(ITEMNMBR LIKE '%CUSTOM' AND 
ATYALLOC > 0) OR (ITEMNMBR LIKE '%SPECIAL' AND 
ATYALLOC > 0))
select sopnumbe into #movedBO from SOP10100 where BACHNUMB = @sourceBatch and SOPNUMBE in (select SOPNUMBE from SOP10200 where 
(ITEMNMBR LIKE '%CUSTOM' AND 
ATYALLOC > 0) OR (ITEMNMBR LIKE '%SPECIAL' AND 
ATYALLOC > 0))

if @destBatchRecordCount > 0 
begin

       declare @p30 int
       set @p30=0
       declare @p31 varchar(255)
       set @p31=''
       exec taCreateUpdateBatchHeaderRcd @I_vBACHNUMB=@destBatch,@I_vBCHCOMNT=default,@I_vSERIES=3,@I_vGLPOSTDT=default,@I_vBCHSOURC='Sales Entry    ',
        @I_vDOCAMT=0,@I_vORIGIN=1,@I_vNUMOFTRX=0,@I_vCHEKBKID=default,@I_vCNTRLTOT=default,@I_vCNTRLTRX=default,@I_vPOSTTOGL=0,@I_vPmtMethod=default,
        @I_vEFTFileFormat=default,@I_vRequesterTrx=default,@I_vBRKDNALL=default,@I_vUSERID=default,@I_vBACHFREQ=default,@I_vRECPSTGS=default,
        @I_vMSCBDINC=default,@I_vAPPROVL=default,@I_vAPPRVLDT=default,@I_vAPRVLUSERID=default,@I_vTRXSOURC=default,@I_vUSRDEFND1=default,
        @I_vUSRDEFND2=default,@I_vUSRDEFND3=default,@I_vUSRDEFND4=default,@I_vUSRDEFND5=default,@O_iErrorState=@p30 output,@oErrString=@p31 output

       --EMOIS 9/2/2105 Change update to single update statement for each row, this will cause trigger to fire correctly
       DECLARE @sopNumber CHAR(21)
       DECLARE updateSOP CURSOR FOR
              SELECT RTRIM(SOPNUMBE) FROM SOP10100 WHERE BACHNUMB = @sourceBatch and SOPNUMBE in (select SOPNUMBE from SOP10200 where 
                      (ITEMNMBR LIKE '%CUSTOM' AND 
                ATYALLOC > 0) OR (ITEMNMBR LIKE '%SPECIAL' AND 
                ATYALLOC > 0))

       OPEN updateSOP FETCH NEXT FROM updateSOP INTO @sopNumber
       WHILE @@FETCH_STATUS = 0
       BEGIN
              UPDATE SOP10100
                     SET BACHNUMB = @destBatch --ReqShipDate = CONVERT(date, getDate()) Removed to keep original ReqShipDate
                     FROM SOP10100 WHERE SOPNUMBE = @sopNumber

              FETCH NEXT FROM updateSOP INTO @sopNumber
       END
       CLOSE updateSOP DEALLOCATE updateSOP

       -- select rtrim(sopnumbe) as 'BackOrderNo' from #movedBO 

       -- reconcile batch headers

       set nocount on
       DECLARE @bachnumb char(25)
       , @numtrx int
       , @batchamount numeric(19,5)

       DECLARE c_sop CURSOR FOR  
       SELECT rtrim(BACHNUMB) FROM SY00500  WHERE (SERIES = 3) AND (BCHSOURC = 'Sales Entry') order by 1

       OPEN c_sop FETCH NEXT FROM c_sop INTO @bachnumb

       WHILE @@FETCH_STATUS = 0
       BEGIN  SELECT @numtrx = ISNULL(COUNT(SOPNUMBE),0)
       , @batchamount = ISNULL(SUM(DOCAMNT),0)  
       FROM SOP10100  WHERE BACHNUMB = @bachnumb

         UPDATE SY00500 SET NUMOFTRX = @numtrx, BCHTOTAL = @batchamount  WHERE BACHNUMB = @bachnumb
       --Print rtrim(@bachnumb) +' ,Transactions = '+COnvert(varchar(100),@numtrx) +' ,BatchTotal = '+COnvert(varchar(100),@batchamount)
         FETCH NEXT FROM c_sop INTO @bachnumb
         END
         CLOSE c_sop DEALLOCATE c_sop
end

select @sourceBatchRecordCount = COUNT(*) from SOP10100 where bachnumb = @sourceBatch
select @destBatchRecordCount = COUNT(*) from SOP10100 where BACHNUMB = @sourceBatch 
and SOPNUMBE in (select SOPNUMBE from SOP10200 where ATYALLOC > 0)

select sopnumbe into #movedBO2 from SOP10100 where BACHNUMB = @sourceBatch and SOPNUMBE in (select SOPNUMBE from SOP10200 where ATYALLOC > 0)


if @destBatchRecordCount > 0 
begin


       set @p30=0
       set @p31=''
       exec taCreateUpdateBatchHeaderRcd @I_vBACHNUMB=@destBatch,@I_vBCHCOMNT=default,@I_vSERIES=3,@I_vGLPOSTDT=default,@I_vBCHSOURC='Sales Entry    ',
            @I_vDOCAMT=0,@I_vORIGIN=1,@I_vNUMOFTRX=0,@I_vCHEKBKID=default,@I_vCNTRLTOT=default,@I_vCNTRLTRX=default,@I_vPOSTTOGL=0,@I_vPmtMethod=default,
            @I_vEFTFileFormat=default,@I_vRequesterTrx=default,@I_vBRKDNALL=default,@I_vUSERID=default,@I_vBACHFREQ=default,@I_vRECPSTGS=default,
            @I_vMSCBDINC=default,@I_vAPPROVL=default,@I_vAPPRVLDT=default,@I_vAPRVLUSERID=default,@I_vTRXSOURC=default,@I_vUSRDEFND1=default,
            @I_vUSRDEFND2=default,@I_vUSRDEFND3=default,@I_vUSRDEFND4=default,@I_vUSRDEFND5=default,@O_iErrorState=@p30 output,@oErrString=@p31 output

       --EMOIS 9/2/2105 Change update to single update statement for each row, this will cause trigger to fire correctly
       DECLARE updateSOP CURSOR FOR
              SELECT RTRIM(SOPNUMBE) FROM SOP10100 WHERE BACHNUMB = @sourceBatch and SOPNUMBE in (select SOPNUMBE from SOP10200 where ATYALLOC > 0)

       OPEN updateSOP FETCH NEXT FROM updateSOP INTO @sopNumber
       WHILE @@FETCH_STATUS = 0
       BEGIN
              UPDATE SOP10100
                     SET BACHNUMB = @destBatch,  ReqShipDate = getDate() --Removed to keep original ReqShipDate
                     FROM SOP10100 WHERE SOPNUMBE = @sopNumber

                     --UPDATE SOP10100          SET ReqShipDate = CONVERT(date, getDate()) 
                     --            FROM SOP10100 WHERE SOPNUMBE = @sopNumber

              FETCH NEXT FROM updateSOP INTO @sopNumber
       END
       CLOSE updateSOP DEALLOCATE updateSOP

       -- select rtrim(sopnumbe) as 'BackOrderNo' from #movedBO2

       -- reconcile batch headers

       set nocount on

       DECLARE c_sop CURSOR FOR  
       SELECT rtrim(BACHNUMB) FROM SY00500  WHERE (SERIES = 3) AND (BCHSOURC = 'Sales Entry') order by 1

       OPEN c_sop FETCH NEXT FROM c_sop INTO @bachnumb

       WHILE @@FETCH_STATUS = 0
       BEGIN  SELECT @numtrx = ISNULL(COUNT(SOPNUMBE),0)
       , @batchamount = ISNULL(SUM(DOCAMNT),0)  
       FROM SOP10100  WHERE BACHNUMB = @bachnumb

         UPDATE SY00500 SET NUMOFTRX = @numtrx, BCHTOTAL = @batchamount  WHERE BACHNUMB = @bachnumb
       --Print rtrim(@bachnumb) +' ,Transactions = '+COnvert(varchar(100),@numtrx) +' ,BatchTotal = '+COnvert(varchar(100),@batchamount)
         FETCH NEXT FROM c_sop INTO @bachnumb
         END
         CLOSE c_sop DEALLOCATE c_sop
end

SELECT RTRIM(sopnumbe) AS 'BackOrderNo' 
INTO #movedBO3
FROM #movedBO
UNION ALL
SELECT RTRIM(sopnumbe) AS 'BackOrderNo' 
FROM #movedBO2

IF EXISTS (SELECT * FROM #movedBO3)
    SELECT * FROM #movedBO3
ELSE 
    select 'No documents.' as 'BackOrderNo'

DROP TABLE #movedBO
DROP TABLE #movedBO2
DROP TABLE #movedBO3

This should work. Since I don't have your data, I cannot say for sure you will not have issues, but this should give you a good idea of how to do this. The RDL (report) should not have to change, I don't think.

Hope this helps!

SSRS Report Not Pulling Data from Stored Procedure

1 Answers