SSRS HITB Report Not Returning Correct Results After Upgrading to Dynamics GP 2018 R2

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.

1. When @temptable is created it needs to include a new column:

 QTYONHND int NOT NULL

2. 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!