This particular issue stemmed from the addition of the checkboxes for Item Quantity and Amount in the Dynamics GP Report Writer version of the HITB report. The parameter change was made in the stored procedure for the report, but not reflected in the SSRS version of the report. Adding the following information to the see HITB stored procedure for the SSRS version of the report will correct the issue.
- When @temptable is created it needs to include a new column:
QTYONHND int NOT NULL
- When the ivCreateHistoricalIVTrialBalance stored procedure is called, we need to pass a value for the new @I_fIncludeZeroQtyValue input parameter (e.g. 1). You will likely need to add a front-facing parameter the SSRS report itself to allow users to eventually set this value. You can get by with hard-coding it to something like shown in the stored procedure as 1 (True).
Both changes are highlighted in Yellow, but you can use the stored procedure script below to modify the stored proc and let me know if the issue occurs or not. You will need to change the DBNAME portion of the USE [DBNAME] part of the script to use the company database name where reports are deployed and run it for each company database that has reports deployed.
USE [DBNAME]
GO
/****** Object: StoredProcedure [dbo].[seeHITB] Script Date: 11/19/2018 3:48:34 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[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
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 DYN18..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 DYN18..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, QTYONHND 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,
1,
@O_SQL_Error_State
set @acctsegt = ‘select a.*, b.ACTNUMST from ‘
+ rtrim(@temptable)
+ ‘ a, GL00105 b where a.ACTINDX = b.ACTINDX’
exec (@acctsegt)
GO
Have any questions about fixing this glitch or other questions about Dynamics GP? Please contact us at any time!
