Retrieving All Data into SQL Reporting Services from a Stored Procedure

Hello:

I have attached a screenshot of the layout of my SQL Reporting Services report (SSRS report) in both the Design and Preview tabs.

As you can see, my issue is that EXTDCOST field does not appear (i.e. shows 0.00 for all records), even though data from the other three columns does appear.

The report and, therefore, the attached .rdl file pull data from a stored procedure which I have pasted below.

How can I get this one field to show data, without modifying this stored procedure.

Thanks!  Much appreciated!!!

John

ALTER PROCEDURE [dbo].[p_cis_seeHITB] @I_nSortBy TINYINT = NULL
	,@I_nReceiptOptions TINYINT = NULL
	,@I_sStartItemNumber CHAR(31) = NULL
	,@I_sEndItemNumber CHAR(31) = NULL
	,@I_sStartAccountNumber CHAR(128) = NULL
	,@I_sEndAccountNumber CHAR(128) = NULL
	,@I_sStartLocationCode CHAR(11) = NULL
	,@I_sEndLocationCode CHAR(11) = NULL
	,@I_nStartQTYType INT = NULL
	,@I_nEndQTYType INT = NULL
	,@I_dtStart DATETIME = NULL
	,@I_dtEnd DATETIME = NULL
	,@sStartClass CHAR(11) = NULL
	,@sEndClass CHAR(11) = NULL
	,@sStartGenericDesc CHAR(11) = NULL
	,@sEndGenericDesc CHAR(11) = NULL
	,@I_fUseGLPostDate TINYINT = NULL
	,@I_fIncludeZeroQtyItems TINYINT = NULL
	,@O_SQL_Error_State INT = 0 OUTPUT
AS

		
--testing
/* 
if object_id('cis_seeHITBParms') is not null drop table cis_SeeHITBParms 
Select @I_nSortBy SortBy
	,@I_nReceiptOptions ReceiptOptions
	,@I_sStartItemNumber StartItemNumber
	,@I_sEndItemNumber EndItemNumber
	,@I_sStartAccountNumber StartAccountNumber
	,@I_sEndAccountNumber EndAccountNumber
	,@I_sStartLocationCode StartLocationCode
	,@I_sEndLocationCode EndLocationCode
	,@I_nStartQTYType StartQTYType
	,@I_nEndQTYType EndQTYType
	,@I_dtStart dtStart
	,@I_dtEnd dtEnd
	,@sStartClass StartClass
	,@sEndClass EndClass
	,@sStartGenericDesc StartGenericDesc
	,@sEndGenericDesc EndGenericDesc
	,@I_fUseGLPostDate UseGLPostDate
	,@I_fIncludeZeroQtyItems IncludeZeroQtyItems
into cis_seeHITBParms
*/
--select * from cis_seeHITBParms

Declare @I_nSortBy TINYINT = NULL
	,@I_nReceiptOptions TINYINT = NULL
	,@I_sStartItemNumber CHAR(31) = NULL
	,@I_sEndItemNumber CHAR(31) = NULL
	,@I_sStartAccountNumber CHAR(128) = NULL
	,@I_sEndAccountNumber CHAR(128) = NULL
	,@I_sStartLocationCode CHAR(11) = NULL
	,@I_sEndLocationCode CHAR(11) = NULL
	,@I_nStartQTYType INT = NULL
	,@I_nEndQTYType INT = NULL
	,@I_dtStart DATETIME = NULL
	,@I_dtEnd DATETIME = NULL
	,@sStartClass CHAR(11) = NULL
	,@sEndClass CHAR(11) = NULL
	,@sStartGenericDesc CHAR(11) = NULL
	,@sEndGenericDesc CHAR(11) = NULL
	,@I_fUseGLPostDate TINYINT = NULL
	,@I_fIncludeZeroQtyItems TINYINT = NULL
	,@O_SQL_Error_State INT = 0 --OUTPUT

Select
	@I_nSortBy = SortBy
	,@I_nReceiptOptions = ReceiptOptions
	,@I_sStartItemNumber = StartItemNumber
	,@I_sEndItemNumber = EndItemNumber
	,@I_sStartAccountNumber = StartAccountNumber
	,@I_sEndAccountNumber = EndAccountNumber
	,@I_sStartLocationCode = StartLocationCode
	,@I_sEndLocationCode =EndLocationCode
	,@I_nStartQTYType = StartQtyType
	,@I_nEndQTYType = EndQtyType
	,@I_dtStart= dtStart
	,@I_dtEnd = dtEnd
	,@sStartClass= StartClass
	,@sEndClass=EndClass
	,@sStartGenericDesc=StartGenericDesc
	,@sEndGenericDesc=EndGenericDesc
	,@I_fUseGLPostDate =UseGLPostDate
	,@I_fIncludeZeroQtyItems = IncludeZeroQtyItems
--select *
from cis_seeHITBParms	
	

--end testing*/


DECLARE @numsegs INT
	,@natseg TINYINT
	,@cnt TINYINT
	,@acctseg CHAR(40)
	,@acctsegd VARCHAR(400)
	,@acctsegl VARCHAR(400)
	,@acctsegt VARCHAR(4000)
	,@guid UNIQUEIDENTIFIER
	,@temptable VARCHAR(50)
	,@length TINYINT



SELECT @numsegs = (
		SELECT MXNUMSEG
		FROM DYNAMICS..SY003001
		)

SELECT @cnt = 1

SELECT @acctseg = ''

SELECT @acctsegd = ''

SELECT @guid = NEWID()

SELECT @temptable = @guid

SET @temptable = '##' + substring(@temptable, 1, 8)

WHILE @cnt <= @numsegs
BEGIN
	SELECT @length = SGMNTLTH
	FROM DYNAMICS..SY00302
	WHERE SGMTNUMB = @cnt

	SELECT @acctseg = ' [ACTNUMBR_' + ltrim(STR(@cnt)) + '] [char] (' + ltrim(STR(@length)) + ') NOT NULL, ' + CHAR(13)

	SET @acctsegl = rtrim(@acctseg)

	SELECT @cnt = @cnt + 1

	SET @acctsegd = @acctsegd + @acctsegl
END

SET @acctsegt = 'CREATE TABLE ' + @temptable + '(' + (ltrim(rtrim(@acctsegd))) + 
	' ITEMNMBR char(31) NOT NULL,   LOCNCODE char(11) NOT NULL,   DOCDATE datetime NOT NULL CHECK (DATEPART(hour,DOCDATE) = 0 and DATEPART(minute,DOCDATE) = 0 and DATEPART(second,DOCDATE) = 0 and DATEPART(millisecond,DOCDATE) = 0),   GLPOSTDT datetime NOT NULL CHECK (DATEPART(hour,GLPOSTDT) = 0 and DATEPART(minute,GLPOSTDT) = 0 and DATEPART(second,GLPOSTDT) = 0 and DATEPART(millisecond,GLPOSTDT) = 0),   JRNENTRY int NOT NULL,   SEQNUMBR int NOT NULL,   ITEMDESC char(101) NOT NULL,   ITMGEDSC char(11) NOT NULL,   DOCNUMBR char(21) NOT NULL,   DOCTYPE smallint NOT NULL,   TRXSORCE char(13) NOT NULL,   TRXREFERENCE smallint NOT NULL,   HSTMODUL char(3) NOT NULL,   TRXQTY numeric(19,5) NOT NULL,   VARIANCEQTY numeric(19,5) NOT NULL,   QTYTYPE smallint NOT NULL,   UNITCOST numeric(19,5) NOT NULL,   EXTDCOST numeric(19,5) NOT NULL,   CRDTAMNT numeric(19,5) NOT NULL,   DEBITAMT numeric(19,5) NOT NULL,   IsLandedCostTrx tinyint NOT NULL,   IsVarianceTrx tinyint NOT NULL,   IsOverrideReceipt tinyint NOT NULL,   VCTNMTHD smallint NOT NULL,   ASOFDATE datetime NOT NULL CHECK (DATEPART(hour,ASOFDATE) = 0 and DATEPART(minute,ASOFDATE) = 0 and DATEPART(second,ASOFDATE) = 0 and DATEPART(millisecond,ASOFDATE) = 0),  STR1 char(1) NOT NULL,   DECPLQTY smallint NOT NULL,   DECPLCUR smallint NOT NULL,   ACTINDX int NOT NULL,   DEX_ROW_ID int identity NOT NULL)'

EXEC (@acctsegt)

EXEC ivCreateHistoricalIVTrialBalance @temptable
	,@I_nSortBy
	,@I_nReceiptOptions
	,@I_sStartItemNumber
	,@I_sEndItemNumber
	,@I_sStartAccountNumber
	,@I_sEndAccountNumber
	,@I_sStartLocationCode
	,@I_sEndLocationCode
	,@I_nStartQTYType
	,@I_nEndQTYType
	,@I_dtStart
	,@I_dtEnd
	,@sStartClass
	,@sEndClass
	,@sStartGenericDesc
	,@sEndGenericDesc
	,@I_fUseGLPostDate
	,@I_fIncludeZeroQtyItems
	,@O_SQL_Error_State

--SET @acctsegt = 'select a.*, b.ACTNUMST, c.ActDescr from ' + rtrim(@temptable) + ' a, GL00105 b, GL00100 c where a.ACTINDX = b.ACTINDX and b.ACTINDX = c.ACTINDX'

SET @acctsegt = 'select a.*,b.ACTNUMST, c.ActDescr from ' + 
			rtrim(@temptable) + ' a inner join GL00105 b on a.ACTINDX = b.ACTINDX 
			inner join GL00100 c  on b.ACTINDX = c.ACTINDX'


EXEC (@acctsegt)

Select allOpen in new window

Extended-Cost-by-Account-Number.rdl