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

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

By |2019-05-01T14:45:34+00:00May 1st, 2019|Dynamics GP, Technology, Tips & Tricks|0 Comments

About the Author:

Ryan K. Sylvies
Ryan has more than 20 years of experience working with the many facets of Microsoft Dynamics GP. His background as a GP end user, administrator, and consultant have given him a wide range of skills and knowledge. With his experience in multiple industries, including government, retail, distribution, and food services, he has developed the necessary skills to support clients at a high level. Being a full-time support desk consultant for Sikich has increased his ability to analyze complicated situations and respond in a timely and efficient manner, too. Ryan has strong knowledge in the following areas: GP Core; Project Accounting; Report Writer; Distribution Series; Payroll and HR, and GP System and SQL; and other software as requested.
This material has been prepared for general informational purposes only and is not intended to be relied upon as accounting, tax, or other professional advice. Please refer to your advisors for specific advice.

Privacy Information

We use cookies to personalize the website for you and to analyze the use of our website. Click 'Privacy Options' to configure how we can interact with you and your device or computer.

Privacy policy | Close
Settings
private equity services construction accountants Agriculture Services Construction Services Non-Profit Services Government Services