SSRS - Display multi-value parameter as text string

=JOIN(Parameters!type.Value,",")

To limit it to a specific length

="Parameter: " +Format(Parameters!DocDate.Value,"dd-MMM-yyyy") +" And Cashier: " +IIF(Len(Join(Parameters!LastUserID.Value,","))>80, Mid(Join(Parameters!LastUserID.Value,","),1,80)+" and more." , Join(Parameters!LastUserID.Value,","))

LS One - Failed to insert. Could not connect to Site Service.

Confirm that the table schema in the company database matches the schema in the Audit database.
Modify the Audit database if necessary.

You may also get a "Could not connect to Site Service" error if there are any errors in the schema

Use this script to identify and update all fields to the correct schema.
Update the FScript and c.name where necessary

SELECT      c.name  AS 'ColumnName'

            ,t.name AS 'TableName','alter table test_audit.dbo.' + t.name + ' alter column ITEMID nvarchar(30) not null' as FScript

FROM        sys.columns c

JOIN        sys.tables  t   ON c.object_id = t.object_id

WHERE       c.name = 'ITEMID'

ORDER BY    TableName

            ,ColumnName;

                     SELECT      c.name  AS 'ColumnName'

            ,t.name AS 'TableName','alter table test.dbo.' + t.name + ' alter column ITEMID nvarchar(30) not null' as FScript

FROM        sys.columns c

JOIN        sys.tables  t   ON c.object_id = t.object_id

WHERE       c.name = 'ITEMID'

ORDER BY    TableName

            ,ColumnName;

Dynamics GP - AP Check stub/remittance prints with a large number of 0 transactions

Cause:
There are a number of credits or other transactions applied to this invoice in addition to this cheque

Resolution:

• GP>Tools>Setup>Payables>List Documents on Remittance
• Switch to "Invoices Only" instead of "All Documents" to hide non-invoice transactions

Dynamics GP - Enable Backorders on all Items and Classes

• Tools>SOP Setup>Back Order>Setup Backorder Doc Type
• Tools>SOP Setup>Order>Assign Backorder type to Order
• Cards>Item>Options>Enable Backorders
• or Item Class - Enable Backorders and roll down
• Or use these scripts

update iv00101 set alwbkord = 1

update iv40400 set alwbkord = 1

Dynamics GP - Current Cost is wrong. Compare Last Received Cost to Current Cost Values

/****** Object:  View [dbo].[BI_AUDIT_CurrentCost]    Script Date: 8/24/2017 10:39:14 AM ******/
//This will take the lowest cost of all receipts for an item on the last date received to get around the //issue of large rounded 1-item costs

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_AUDIT_CurrentCost]
AS
SELECT        TOP (100) PERCENT dbo.IV10200.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.IV10200.RCTSEQNM AS LRSNM, dbo.IV10200.DATERECD, dbo.IV10200.QTYONHND, dbo.IV10200.UNITCOST,
                         dbo.IV10200.DEX_ROW_ID AS Lastentry, dbo.IV00101.CURRCOST, LRSNM.LsLowRcvCost, LRSNM.LsLowRcvCost - dbo.IV00101.CURRCOST AS CostDiff, Qty.AllQtyOnHnd
FROM            (SELECT        ITEMNMBR, QTYONHND AS AllQtyOnHnd
                          FROM            dbo.IV00102
                          WHERE        (LOCNCODE = '')) AS Qty RIGHT OUTER JOIN
                         dbo.IV00101 ON Qty.ITEMNMBR = dbo.IV00101.ITEMNMBR RIGHT OUTER JOIN
                         dbo.IV10200 INNER JOIN
                             (SELECT        IV10200_2.ITEMNMBR, MAX(IV10200_2.RCTSEQNM) AS LRSNM, MIN(IV10200_2.UNITCOST) AS LsLowRcvCost
                               FROM            dbo.IV10200 AS IV10200_2 INNER JOIN
                                                             (SELECT        ITEMNMBR, MAX(DATERECD) AS LDR
                                                               FROM            dbo.IV10200 AS IV10200_1
                                                               GROUP BY ITEMNMBR) AS LDR ON IV10200_2.ITEMNMBR = LDR.ITEMNMBR AND IV10200_2.DATERECD = LDR.LDR
                               GROUP BY IV10200_2.ITEMNMBR) AS LRSNM ON dbo.IV10200.ITEMNMBR = LRSNM.ITEMNMBR AND dbo.IV10200.RCTSEQNM = LRSNM.LRSNM ON
                         dbo.IV00101.ITEMNMBR = dbo.IV10200.ITEMNMBR
ORDER BY CostDiff
GO

Play Android Apps on PC - Android Emulators

Nox
https://www.bignox.com/

Dynamics NAV - LS Retail - Pharmacy - "The operation could not complete because a record in the Prescription Order Table was locked by another user. Please retry the activity." when scanning a prescription

Codeunits involved upon prescription scan

• T10015350 Prescription Order
• Flowfields:Prescription Order Lines Sum: Amount, Insurance Payment, Discount amount,Customer Payment
• C10015331 Prescription POS Connection
• ScanPrescriptionOrder
• GetPrescriptionOrder
• C10015395 Pharmacy Web client
• GetPrescriptionOrder
• Possibly Writing to Prescription Order, and stalling while calculating flowfields, resulting in access error as code continues to populate Prescription Order
• GetAndReservePrescriptionOrder
• 
  

Dynamics GP - Copy Settings from One company to another

This is possible using the PSTL - Company Copy tool.

• Select the modules
• Select Data
• Copy

Dynamics GP - SQL View - Inventory Transaction Adjustment Line Detail with Reason codes, accounts and username

/****** Object:  View [dbo].[BI_INV_ItemAdjDtl]    Script Date: 17/08/2017 10:11:09 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[BI_INV_ItemAdjDtl]
AS
SELECT        dbo.IV30200.TRXSORCE, dbo.IV30200.IVDOCTYP, dbo.IV30200.DOCNUMBR, dbo.IV30200.DOCDATE, dbo.IV30200.BACHNUMB, dbo.IV30200.GLPOSTDT, dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC,
                         dbo.IV30300.UOFM, dbo.IV30300.TRXQTY, dbo.IV30300.UNITCOST, dbo.IV30300.EXTDCOST, dbo.IV30300.TRXLOCTN, dbo.IV30300.TRNSTLOC, dbo.IV30300.QTYBSUOM, dbo.IV30300.Reason_Code,
                         dbo.IV40300.Reason_Code_Description, dbo.GL00105.ACTNUMST AS IVActNum, dbo.GL00100.ACTDESCR AS IVActDesc, GL00105_1.ACTNUMST AS OffsetActNum, GL00100_1.ACTDESCR AS OffsetActDesc,
                         GLTrxSmry.UserNm
FROM            dbo.IV30200 INNER JOIN
                         dbo.IV30300 ON dbo.IV30200.IVDOCTYP = dbo.IV30300.DOCTYPE AND dbo.IV30200.DOCNUMBR = dbo.IV30300.DOCNUMBR INNER JOIN
                         dbo.GL00100 ON dbo.IV30300.IVIVINDX = dbo.GL00100.ACTINDX INNER JOIN
                         dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX INNER JOIN
                         dbo.GL00100 AS GL00100_1 ON dbo.IV30300.IVIVOFIX = GL00100_1.ACTINDX INNER JOIN
                         dbo.GL00105 AS GL00105_1 ON GL00100_1.ACTINDX = GL00105_1.ACTINDX INNER JOIN
                         dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
                             (SELECT        ORTRXSRC AS TrxSource, DTAControlNum AS SourceDocNum, USWHPSTD AS UserNm
                               FROM            dbo.GL10000
                               UNION
                               SELECT        ORGNTSRC, ORCTRNUM, USWHPSTD
                               FROM            dbo.GL20000
                               UNION
                               SELECT        ORGNTSRC, ORCTRNUM, USWHPSTD
                               FROM            dbo.GL30000) AS GLTrxSmry ON dbo.IV30300.TRXSORCE = GLTrxSmry.TrxSource AND dbo.IV30300.DOCNUMBR = GLTrxSmry.SourceDocNum LEFT OUTER JOIN
                         dbo.IV40300 ON dbo.IV30300.Reason_Code = dbo.IV40300.Reason_Code


GO

Dynamics GP - Reason Code Setup Table - IV40300

Reason Code Table - IV40300

Eone Smartview and Smartlist builder - the multi-part identifier could not be bound

http://www.eonesolutions.com/discussion/smartlist-builder-lists-not-populating/


Reinstall the latest versions of Smartlist Builder and Smartview

Globalprotect v4 server certificate verification failed

Cause:
The proper certificates are not installed

Resolution:

• After browsing to the portal to download the globalprotect client, if the site is showing a certificate error
• Click on the error>View Certificates>Certification Path
• It may have multiple certificates listed here. Install all certificates with a red x to the Trusted Certification Authority folder
• Refresh the page to confirm that the certificates are working correctly
• 
  

Windows IRQL not less or equal BSOD

• Download Malwarebytes and scan machine to remove any malware
• Stop steam service
• Stop all antivirus
• Stop all firewall
• Install new drivers
• https://www.drivereasy.com/
• This actually did work in identifying the exact drivers i needed to install

SQL - Cannot edit job steps

https://davidbridge.wordpress.com/2011/05/13/cannot-edit-job-steps-in-sql-server-2008-r2-management-studio-on-64-bit-windows/

Run cmd as administrator
Run these two lines

This will register 64 bit DTS.dll
c:\windows\system32\regsvr32 "c:\Program Files\Microsoft SQL Server\100\DTS\Binn\dts.dll"

This will register 32 bit which is required as SSMS is a 32-bit application.
c:\windows\syswow64\regsvr32 "c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dts.dll"

SQL - getdate() without any time

DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

Dynamics GP - SQL View - Aged AP Payments, GL, Invoices, POs - AP Aging by Payment Date

/****** Object:  View [dbo].[BI_AP_Apply]    Script Date: 13/09/2017 03:49:46 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_AP_Apply]
AS
SELECT        P.VENDORID AS Vendor_ID, PM.VENDNAME AS Vendor_Name, CASE P.DOCTYPE WHEN 1 THEN 'Invoice' WHEN 2 THEN 'Finance Charge' WHEN 3 THEN 'Misc Charge' END AS Document_Type,
                         P.DOCDATE AS Document_Date, P.VCHRNMBR AS Voucher_Number, P.DOCNUMBR AS Document_Number, P.DOCAMNT AS Document_Amount, P.TEN99AMNT AS [1099_Amount],
                         P.TEN99BOXNUMBER AS [1099_Box],
                         CASE P.TEN99TYPE WHEN 1 THEN 'Not a 1099 Vendor' WHEN 2 THEN 'Divident' WHEN 3 THEN 'Interest' WHEN 4 THEN 'Miscellaneous' WHEN 5 THEN 'Withholding' ELSE '' END AS [1099_Type],
                         COALESCE (PA.APPLDAMT, 0) AS Applied_Amount, COALESCE (PA.VCHRNMBR, '') AS Payment_Voucher_Number, COALESCE (P2.DOCNUMBR, '') AS Payment_Doc_Number, COALESCE (P2.DOCDATE, '1/1/1900')
                         AS Payment_Date, COALESCE (PA.DATE1, '1/1/1900') AS Apply_Date, COALESCE (PA.GLPOSTDT, '1/1/1900') AS Apply_GL_Posting_Date,
                         CASE PA.DOCTYPE WHEN 4 THEN 'Return' WHEN 5 THEN 'Credit Memo' WHEN 6 THEN 'Payment' ELSE '' END AS Payment_Type, COALESCE (PA.POSTED, 'Unpaid') AS Payment_Status, P.PORDNMBR,
                         PO.DOCDATE AS PODate, PO.CURNCYID, P.DUEDATE, PM.VNDCLSID AS VendorClass, PO.POPCONTNUM AS ReqNo, P.CURTRXAM, CASE WHEN datediff(d, COALESCE (P2.DOCDATE, getdate()), getdate()) < 31 AND
                          P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, getdate()) < 31 AND P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [Current], CASE WHEN datediff(d, COALESCE (P2.DOCDATE,
                         getdate()), getdate()) BETWEEN 31 AND 60 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, getdate()) BETWEEN 31 AND 60 AND
                         P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [31_to_60_Days], CASE WHEN datediff(d, COALESCE (P2.DOCDATE, getdate()), getdate()) BETWEEN 61 AND 90 AND
                         P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, getdate()) BETWEEN 61 AND 90 AND P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [61_to_90_Days], CASE WHEN datediff(d,
                         COALESCE (P2.DOCDATE, getdate()), getdate()) BETWEEN 91 AND 120 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, getdate()) BETWEEN 91 AND 120 AND
                         P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [91_to_120_Days], CASE WHEN datediff(d, COALESCE (P2.DOCDATE, getdate()), getdate()) > 90 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d,
                          P.DOCDATE, getdate()) > 120 AND P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [120_and_Over], DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AS AgingDate, DATEDIFF(d, COALESCE (P2.DOCDATE,
                          GETDATE()), GETDATE()) AS DaysOld, ISNULL(PO.SUBTOTAL, 0) AS POSubTot, ISNULL(PO.TAXAMNT, 0) AS POTaxAmt, ISNULL(PO.SUBTOTAL + PO.TAXAMNT, 0) AS POTotal
FROM            (SELECT        VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED, TEN99AMNT, TEN99BOXNUMBER, TEN99TYPE, PORDNMBR, DUEDATE, CURTRXAM
                          FROM            dbo.PM30200
                          UNION ALL
                          SELECT        VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED, TEN99AMNT, TEN99BOXNUMBER, TEN99TYPE, PORDNMBR, DUEDATE, CURTRXAM
                          FROM            dbo.PM20000) AS P LEFT OUTER JOIN
                         dbo.POP10100 AS PO ON P.PORDNMBR = PO.PONUMBER LEFT OUTER JOIN
                             (SELECT        VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE, DOCDATE, APPLDAMT, VCHRNMBR, DATE1, GLPOSTDT, CASE POSTED WHEN 0 THEN 'Unposted' ELSE 'Posted' END AS POSTED
                               FROM            dbo.PM10200
                               UNION
                               SELECT        VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE, DOCDATE, APPLDAMT, VCHRNMBR, DATE1, GLPOSTDT, 'Posted' AS POSTED
                               FROM            dbo.PM30300) AS PA ON P.VCHRNMBR = PA.APTVCHNM AND P.VENDORID = PA.VENDORID AND P.DOCTYPE = PA.APTODCTY LEFT OUTER JOIN
                             (SELECT        VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE
                               FROM            dbo.PM20000 AS PM20000_1
                               UNION ALL
                               SELECT        VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE
                               FROM            dbo.PM30200 AS PM30200_1) AS P2 ON P2.VCHRNMBR = PA.VCHRNMBR AND P2.DOCTYPE = PA.DOCTYPE LEFT OUTER JOIN
                         dbo.PM00200 AS PM ON P.VENDORID = PM.VENDORID
WHERE        (P.DOCTYPE IN (1, 2, 3)) AND (P.VOIDED = 0)

GO

Dynamics GP - Can print to screen, but when printing to printer, nothing prints, with no error message

When a batch is posted, 3 or 4 reports may print to screen.
The way GP processes these tasks is as follows

• Print Report1 to screen
• Print Report2 to screen
• Print Report3 to screen
• Print Report4 to screen
• Complete Posting
• Submit print requests to printer

GP will not actually submit the print jobs to the printer until all of the "print to screen"s have been processed

If this is not the issue then try
Backup Reports.dic first
After ruling out printer and connectivity issues, test printing with a new dictionary.

If this works, dictionary is corrupted.
Remove old dictionary, reimport all customizations

Dynamics GP - Fix Note indexes on imported records

https://blogs.msdn.microsoft.com/developingfordynamicsgp/2010/02/04/fixing-missing-note-index-values/

This script will update all NOTEINDX fields with a value of 0 to the correct noteindx value for the @tablename defined


/*
** AssignNoteIndx.sql
**
** This script assigns note index values to all records
** in the given table that currently do not have one.
**
** Edit the table name below and execute in
** context of appropriate company database.
*/

/* -- parameters -- */

Declare @tablename sysname

/* ############## */

set @tablename = 'RM00101' -- put the table name here

/* ############## */


/* -- working variables -- */
set nocount on

declare @tloop int
declare @sCompanyID integer
declare @sSessionID integer
declare @DEX_ROW_ID integer
declare @NewNoteIndex numeric(19,5)
declare @sError integer
declare @Cnt integer

set @tloop = 1
select @sCompanyID = CMPANYID
 from DYNAMICS.dbo.SY01500
 where INTERID = db_name()
set @sSessionID = @@spid
set @Cnt = 0

/* @@@ MAINLINE @@@ */

while @tloop = 1 begin
set @tloop = 0

/* -- check table exists -- */
if object_id (@tablename) is null begin
print 'table : ' + @tablename
print 'does not exist in this database : ' + db_name()
break
end
if @sCompanyID is null begin
print 'No Entry in DYNAMICS system table for this company'
break
end

print 'Assigning Note Index for table : ' + @tablename

/* -- create a proc for repeated calls -- */

exec ('create procedure #nextdex as declare @DEXROWID integer select top 1 @DEXROWID = DEX_ROW_ID from ' + @tablename + ' where NOTEINDX = 0 return isnull(@DEXROWID,0) ' )
exec ('create procedure #assignnext (@NextIndx integer, @DEXROWID integer ) as update ' + @tablename + ' set NOTEINDX = @NextIndx where DEX_ROW_ID = @DEXROWID return @@ERROR ' )


exec @DEX_ROW_ID = #nextdex

while @DEX_ROW_ID > 0 begin

begin transaction
exec DYNAMICS.dbo.smGetNextNoteIndex @sCompanyID, @sSessionID, @NewNoteIndex OUTPUT, @sError OUTPUT
exec #assignnext @NewNoteIndex, @DEX_ROW_ID
commit transaction

set @Cnt = @Cnt + 1

exec @DEX_ROW_ID = #nextdex
end

print 'Number of records changed : ' + convert(varchar(8), @Cnt)


drop procedure #nextdex
drop procedure #assignnext

end

/* ###### end of script #### */

Windows - Faster Navigation - Direct Folders, Autohotkey

Direct folders - Middle click or double click folder navigation
http://www.codesector.com/directfolders


Autohotkey - customizable keystrokes to launch programs
https://autohotkey.com/

Dynamics GP - Integration Manager 2016 - Root data not found, Could not access Microsoft.Dynamics.GP.IntegrationManager.ini file

Cause: UAC is on, and IM has been installed to one location, but then uninstalled and reinstalled to another location.

The xml files in the C:\Users\%userid%\AppData\Roaming\Microsoft Corporation\Microsoft Dynamics GP\16.0.0.0
folder cannot be overwritten, even when run as administrator causing im to constantly point to the old location


Resolution

• Delete the following folders
• C:\Users\%userid%\AppData\Roaming\Microsoft Corporation\Microsoft Dynamics GP\16.0.0.0 
• Relaunch IM as administrator, and that should generate a new xml file to the new path

Dynamics GP - Sales Comment Inquiry Zoom - Source Table and Fields

• SY04200 - Preset comment id lines - use CMMTTEXT
• SOP10102 - Line Comments in the blue arrow after comment id on the sop line - use CMMTTEXT
• SOP10106 - Document Comments on the blue arrow after comment id - use CMMTTEXT

Dynamics GP - Manufacturing Views - MO Receipts

/****** Object:  View [dbo].[BI_Mfg_MORcts]    Script Date: 8/2/2017 1:12:12 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_Mfg_MORcts]
AS
SELECT        dbo.MOP1100.MANUFACTUREORDER_I AS MONum, dbo.WO010032.DSCRIPTN, dbo.MOP1100.MOPRCTNM, dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.MOP1100.DATERECD,
                         dbo.MOP1100.QTYRECVD, dbo.MOP1100.TO_SITE_I, dbo.MOP1100.USERID, dbo.MOP1100.RECEIPTCOSTSARRAY_10 AS BackflushCost, dbo.MOP1100.POSTED
FROM            dbo.IV00101 RIGHT OUTER JOIN
                         dbo.WO010032 ON dbo.IV00101.ITEMNMBR = dbo.WO010032.ITEMNMBR RIGHT OUTER JOIN
                         dbo.MOP1100 ON dbo.WO010032.MANUFACTUREORDER_I = dbo.MOP1100.MANUFACTUREORDER_I
GO