Here is SQL I use to copy Advanced financial reports from one SQL server and GP company to another SQL server and GP company. The same process can be used to clone a report form one company to another.
Our GP 2016 Balance sheet and Income statements are in Advanced Financials. Advanced financial report structure is saved in the SQL database under some tables. This means, to copy or clone a report from one company to another, or from one SQL server to another you need to run SQL to do this.
Below is an example of how to migrate a Report.
NOTE you MUST create the report in the target advanced financials with the same name as the source before running the SQL
--Get the Report ID's in old and new GP
DECLARE @ReportID int, @ReportName as varchar(55), @ReportIDGP9 int; SET @ReportIDGP9=34;
SELECT @ReportName=RPRTNAME FROM NETSQL2.NET.dbo.AF40100 WHERE ReportID=@ReportIDGP9;
Print @ReportName;
--Get ReportID in new DB from report Name
SELECT @ReportID=ReportID FROM NET.dbo.AF40100 WHERE RPRTNAME=@ReportName;
Print @ReportID;
--UPDATE Header
UPDATE NET.dbo.AF40100
SET RPRTTYPE=TheData.RPRTTYPE,CLCFRPRT=TheData.CLCFRPRT,LSTMODIF=TheData.LSTMODIF,NOTEINDX=TheData.NOTEINDX
FROM (select RPRTNAME,RPRTTYPE,CLCFRPRT,LSTMODIF,NOTEINDX
from NETSQL2.NET.dbo.AF40100 WHERE ReportID=@ReportIDGP9
) as TheData
WHERE ReportID=@ReportID;
DELETE FROM NET.dbo.AF40101 WHERE ReportID=@ReportID;
INSERT INTO NET.dbo.AF40101
(ReportID,MNHDRCNT,MNFTRCNT,SHDRCNT,SFTRCNT,ROWCNT1,COLCNT,SHDRPCNT,SFTRPCNT,MNHDRFLG,MNFTRFLG,SHDRFLAG,SFTRFLAG,MNHDRSIZ,MNFTRSIZ,SHDRSIZE_1,SHDRSIZE_2,SHDRSIZE_3,SHDRSIZE_4,SHDRSIZE_5,SFTRSIZE_1,SFTRSIZE_2,SFTRSIZE_3,SFTRSIZE_4,SFTRSIZE_5,SHDROPT_1,SHDROPT_2,SHDROPT_3,SHDROPT_4,SHDROPT_5,SHDRPRT_1,SHDRPRT_2,SHDRPRT_3,SHDRPRT_4,SHDRPRT_5,SFTROPT_1,SFTROPT_2,SFTROPT_3,SFTROPT_4,SFTROPT_5,SFTRPRT_1,SFTRPRT_2,SFTRPRT_3,SFTRPRT_4,SFTRPRT_5,COLHDCNT,COLDHSIZ_1,COLDHSIZ_2,COLDHSIZ_3,COLDHSIZ_4,COLDHSIZ_5,COLDHSIZ_6,RTOTLSIZ,COLTOSIZ,COLOFSIZ,LFTMARGN,RTMARGIN,TOPMARGN,BOTMARGN)
select @ReportID,MNHDRCNT,MNFTRCNT,SHDRCNT,SFTRCNT,ROWCNT1,COLCNT,SHDRPCNT,SFTRPCNT,MNHDRFLG,MNFTRFLG,SHDRFLAG,SFTRFLAG,MNHDRSIZ,MNFTRSIZ,SHDRSIZE_1,SHDRSIZE_2,SHDRSIZE_3,SHDRSIZE_4,SHDRSIZE_5,SFTRSIZE_1,SFTRSIZE_2,SFTRSIZE_3,SFTRSIZE_4,SFTRSIZE_5,SHDROPT_1,SHDROPT_2,SHDROPT_3,SHDROPT_4,SHDROPT_5,SHDRPRT_1,SHDRPRT_2,SHDRPRT_3,SHDRPRT_4,SHDRPRT_5,SFTROPT_1,SFTROPT_2,SFTROPT_3,SFTROPT_4,SFTROPT_5,SFTRPRT_1,SFTRPRT_2,SFTRPRT_3,SFTRPRT_4,SFTRPRT_5,COLHDCNT,COLDHSIZ_1,COLDHSIZ_2,COLDHSIZ_3,COLDHSIZ_4,COLDHSIZ_5,COLDHSIZ_6,RTOTLSIZ,COLTOSIZ,COLOFSIZ,LFTMARGN,RTMARGIN,TOPMARGN,BOTMARGN
from NETSQL2.NET.dbo.AF40101 WHERE ReportID=@ReportIDGP9;
DELETE FROM NET.dbo.AF40102 WHERE ReportID=@ReportID;
INSERT INTO NET.dbo.AF40102
(REPORTID,HDRFTRTY,FLDNUM,FLDPOSX1,FLDPOSY1,FLDPOSX2,FLDPOSY2,FLDTYPE,FLDFRMAT,SBHSBFIN,FLDOPT,FLDOPT2,FLDALIGN,FLDFTFML,FLDFTSIZ,FLDSTYLE_1,FLDSTYLE_2,FLDSTYLE_3,FLDSTYLE_4,FLDSTYLE_5,FLDSTYLE_6,FLDVALUE,FLDVALU2,FLDPRNAM)
select @REPORTID,HDRFTRTY,FLDNUM,FLDPOSX1,FLDPOSY1,FLDPOSX2,FLDPOSY2,FLDTYPE,FLDFRMAT,SBHSBFIN,FLDOPT,FLDOPT2,FLDALIGN,FLDFTFML,FLDFTSIZ,FLDSTYLE_1,FLDSTYLE_2,FLDSTYLE_3,FLDSTYLE_4,FLDSTYLE_5,FLDSTYLE_6,FLDVALUE,FLDVALU2,FLDPRNAM
from NETSQL2.NET.dbo.AF40102 WHERE ReportID=@ReportIDGP9;
DELETE FROM NET.dbo.AF40103 WHERE ReportID=@ReportID;
INSERT INTO NET.dbo.AF40103 (REPORTID,COLNUM,CLTKNCNT,COLTYPE,COLSIZE,COLOMCNT,COLOFMRK_1,COLOFMRK_2,COLOFMRK_3,COLOFMRK_4,HIDEFLAG,TEXTVALU,STPERIOD,ENDPEROD,AMNTFROM,HISTYEAR,BUDID,PRTSIGN,PRTCOMMA,PRTPCENT,PRTTEXT,ROUNDOPT,HEADALIN,HDFTFMLY,HDFTSIZE,HEDSTYLE_1,HEDSTYLE_2,HEDSTYLE_3,HEDSTYLE_4,HEDSTYLE_5,HEDSTYLE_6,HEADTYPE_1,HEADTYPE_2,HEADTYPE_3,HEADTYPE_4,HEADTYPE_5,HEADTYPE_6,HEDFRMAT_1,HEDFRMAT_2,HEDFRMAT_3,HEDFRMAT_4,HEDFRMAT_5,HEDFRMAT_6,HEADOPT_1,HEADOPT_2,HEADOPT_3,HEADOPT_4,HEADOPT_5,HEADOPT_6,HEADOPT2_1,HEADOPT2_2,HEADOPT2_3,HEADOPT2_4,HEADOPT2_5,HEADOPT2_6,COLHDNG_1,COLHDNG_2,COLHDNG_3,COLHDNG_4,COLHDNG_5,COLHDNG_6,COLHDNG2_1,COLHDNG2_2,COLHDNG2_3,COLHDNG2_4,COLHDNG2_5,COLHDNG2_6,ALGNOFST,COLEXPER,NOTEINDX,SEGFROM_1,SEGFROM_2,SEGFROM_3,SEGFROM_4,SEGTO_1,SEGTO_2,SEGTO_3,SEGTO_4)
select @ReportID,COLNUM,CLTKNCNT,COLTYPE,COLSIZE,COLOMCNT,COLOFMRK_1,COLOFMRK_2,COLOFMRK_3,COLOFMRK_4,HIDEFLAG,TEXTVALU,STPERIOD,ENDPEROD,AMNTFROM,HISTYEAR,BUDID,PRTSIGN,PRTCOMMA,PRTPCENT,PRTTEXT,ROUNDOPT,HEADALIN,HDFTFMLY,HDFTSIZE,HEDSTYLE_1,HEDSTYLE_2,HEDSTYLE_3,HEDSTYLE_4,HEDSTYLE_5,HEDSTYLE_6,HEADTYPE_1,HEADTYPE_2,HEADTYPE_3,HEADTYPE_4,HEADTYPE_5,HEADTYPE_6,HEDFRMAT_1,HEDFRMAT_2,HEDFRMAT_3,HEDFRMAT_4,HEDFRMAT_5,HEDFRMAT_6,HEADOPT_1,HEADOPT_2,HEADOPT_3,HEADOPT_4,HEADOPT_5,HEADOPT_6,HEADOPT2_1,HEADOPT2_2,HEADOPT2_3,HEADOPT2_4,HEADOPT2_5,HEADOPT2_6,COLHDNG_1,COLHDNG_2,COLHDNG_3,COLHDNG_4,COLHDNG_5,COLHDNG_6,COLHDNG2_1,COLHDNG2_2,COLHDNG2_3,COLHDNG2_4,COLHDNG2_5,COLHDNG2_6,ALGNOFST,COLEXPER,NOTEINDX,SEGFROM_1,SEGFROM_2,SEGFROM_3,SEGFROM_4,SEGTO_1,SEGTO_2,SEGTO_3,SEGTO_4
from NETSQL2.NET.dbo.AF40103 WHERE ReportID=@ReportIDGP9;
DELETE FROM NET.dbo.AF40104 WHERE ReportID=@ReportID;
INSERT INTO NET.dbo.AF40104 (REPORTID,CLCOLNUM,TKNODNUM,TKNTYPE,TKNVALUE,TKNDLVAL,TKNUNACT_1,TKNUNACT_2,TKNUNACT_3,TKNUNACT_4)
select @REPORTID,CLCOLNUM,TKNODNUM,TKNTYPE,TKNVALUE,TKNDLVAL,TKNUNACT_1,TKNUNACT_2,TKNUNACT_3,TKNUNACT_4
from NETSQL2.NET.dbo.AF40104 WHERE ReportID=@ReportIDGP9
DELETE FROM NET.dbo.AF40105 WHERE ReportID=@ReportID;
INSERT INTO NET.dbo.AF40105 (REPORTID,CLCOLNUM,TKNODNUM,TKNTYPE,TKNVALUE,TKNDLVAL,TKNUNACT_1,TKNUNACT_2,TKNUNACT_3,TKNUNACT_4)
select @REPORTID,CLCOLNUM,TKNODNUM,TKNTYPE,TKNVALUE,TKNDLVAL,TKNUNACT_1,TKNUNACT_2,TKNUNACT_3,TKNUNACT_4
from NETSQL2.NET.dbo.AF40105 WHERE ReportID=@ReportIDGP9
DELETE FROM NET.dbo.AF40106 WHERE ReportID=@ReportID;
INSERT INTO NET.dbo.AF40106 (REPORTID,ROWNUMBR,TOTKNCNT,ROWTYPE,ROWSIZE,ROLUPFLG,ROWDESC,SUBSUDID,TYPCLBAL,CATNUMBR,PRTSIGN,PRTHEDER,CENTHEDR,ROWFTFAM,ROWFTSIZ,ROWSTYLE_1,ROWSTYLE_2,ROWSTYLE_3,ROWSTYLE_4,ROWSTYLE_5,ROWSTYLE_6,ROFMRKIN_1,ROFMRKIN_2,ROFMRKIN_3,ROFMRKIN_4,ROFMRKIN_5,ROFMRKIN_6,ROFMRKIN_7,ROFMRKIN_8,ROFMRKIN_9,ROFMRKIN_10,ROFMRKIN_11,ROFMRKIN_12,ROFMRKIN_13,ROFMRKIN_14,ROFMRKIN_15,ROFMRKIN_16,ROFMRKIN_17,ROFMRKIN_18,ROFMRKIN_19,ROFMRKIN_20,ROFMRKIN_21,ROFMRKIN_22,ROFMRKIN_23,ROFMRKIN_24,ROFMRKIN_25,ROFMRKIN_26,ROFMRKIN_27,ROFMRKIN_28,ROFMRKIN_29,ROFMRKIN_30,ROFMRKIN_31,ROFMRKIN_32,ROFMRKIN_33,ROFMRKIN_34,ROFMRKIN_35,ROFMRKIN_36,ROFMRKIN_37,ROFMRKIN_38,ROFMRKIN_39,ROFMRKIN_40,CFLOSCTN,RWEXPERR,NOTEINDX,STTACCT_1,STTACCT_2,STTACCT_3,STTACCT_4,STTACCT_5,STTACCT_6,ENDACCT_1,ENDACCT_2,ENDACCT_3,ENDACCT_4,ENDACCT_5,ENDACCT_6)
select @REPORTID,ROWNUMBR,TOTKNCNT,ROWTYPE,ROWSIZE,ROLUPFLG,ROWDESC,SUBSUDID,TYPCLBAL,CATNUMBR,PRTSIGN,PRTHEDER,CENTHEDR,ROWFTFAM,ROWFTSIZ,ROWSTYLE_1,ROWSTYLE_2,ROWSTYLE_3,ROWSTYLE_4,ROWSTYLE_5,ROWSTYLE_6,ROFMRKIN_1,ROFMRKIN_2,ROFMRKIN_3,ROFMRKIN_4,ROFMRKIN_5,ROFMRKIN_6,ROFMRKIN_7,ROFMRKIN_8,ROFMRKIN_9,ROFMRKIN_10,ROFMRKIN_11,ROFMRKIN_12,ROFMRKIN_13,ROFMRKIN_14,ROFMRKIN_15,ROFMRKIN_16,ROFMRKIN_17,ROFMRKIN_18,ROFMRKIN_19,ROFMRKIN_20,ROFMRKIN_21,ROFMRKIN_22,ROFMRKIN_23,ROFMRKIN_24,ROFMRKIN_25,ROFMRKIN_26,ROFMRKIN_27,ROFMRKIN_28,ROFMRKIN_29,ROFMRKIN_30,ROFMRKIN_31,ROFMRKIN_32,ROFMRKIN_33,ROFMRKIN_34,ROFMRKIN_35,ROFMRKIN_36,ROFMRKIN_37,ROFMRKIN_38,ROFMRKIN_39,ROFMRKIN_40,CFLOSCTN,RWEXPERR,NOTEINDX,STTACCT_1,STTACCT_2,STTACCT_3,STTACCT_4,'' as STTACCT_5,'' as STTACCT_6,ENDACCT_1,ENDACCT_2,ENDACCT_3,ENDACCT_4,'' as ENDACCT_5,'' as ENDACCT_6
from NETSQL2.NET.dbo.AF40106 WHERE ReportID=@ReportIDGP9;
DELETE FROM NET.dbo.AF40107 WHERE ReportID=@ReportID;
INSERT INTO NET.dbo.AF40107 (REPORTID,TOTRWNUM,TKNODNUM,STROWNUM,ENDRWNUM)
select @REPORTID,TOTRWNUM,TKNODNUM,STROWNUM,ENDRWNUM
from NETSQL2.NET.dbo.AF40107 WHERE ReportID=@ReportIDGP9;
DELETE FROM NET.dbo.AF40108 WHERE ReportID=@ReportID;
INSERT INTO NET.dbo.AF40108 (REPORTID,TOTRWNUM,MBRWNUM)
select @REPORTID,TOTRWNUM,MBRWNUM
from NETSQL2.NET.dbo.AF40108 WHERE ReportID=@ReportIDGP9;
DELETE FROM NET.dbo.AF40200 WHERE ReportID=@ReportID;
INSERT INTO NET.dbo.AF40200 (REPORTID,RptOptID,ROWNMBR,NMBRACTS)
select @REPORTID,RptOptID,ROWNMBR,NMBRACTS
from NETSQL2.NET.dbo.AF40200 WHERE ReportID=@ReportIDGP9;
DELETE FROM NET.dbo.AF40201 WHERE ReportID=@ReportID;
INSERT INTO NET.dbo.AF40201 (REPORTID,RptOptID,ROWNMBR,SEQNUMBR,ACTARR1_1_1,ACTARR1_1_2,ACTARR1_1_3,ACTARR1_1_4,ACTARR1_1_5,ACTARR1_1_6,ACTARR1_2_1,ACTARR1_2_2,ACTARR1_2_3,ACTARR1_2_4,ACTARR1_2_5,ACTARR1_2_6,ACTARR1_3_1,ACTARR1_3_2,ACTARR1_3_3,ACTARR1_3_4,ACTARR1_3_5,ACTARR1_3_6,ACTARR1_4_1,ACTARR1_4_2,ACTARR1_4_3,ACTARR1_4_4,ACTARR1_4_5,ACTARR1_4_6,ACTARR2_1_1,ACTARR2_1_2,ACTARR2_1_3,ACTARR2_1_4,ACTARR2_2_1,ACTARR2_2_2,ACTARR2_2_3,ACTARR2_2_4,ACTARR2_3_1,ACTARR2_3_2,ACTARR2_3_3,ACTARR2_3_4,ACTARR2_4_1,ACTARR2_4_2,ACTARR2_4_3,ACTARR2_4_4)
select @REPORTID,RptOptID,ROWNMBR,SEQNUMBR,ACTARR1_1_1,ACTARR1_1_2,ACTARR1_1_3,ACTARR1_1_4,NULL as ACTARR1_1_5,NULL as ACTARR1_1_6,ACTARR1_2_1,ACTARR1_2_2,ACTARR1_2_3,ACTARR1_2_4,NULL as ACTARR1_2_5,NULL as ACTARR1_2_6,ACTARR1_3_1,ACTARR1_3_2,ACTARR1_3_3,ACTARR1_3_4,NULL as ACTARR1_3_5,NULL as ACTARR1_3_6,ACTARR1_4_1,ACTARR1_4_2,ACTARR1_4_3,ACTARR1_4_4,NULL as ACTARR1_4_5,NULL as ACTARR1_4_6,ACTARR2_1_1,ACTARR2_1_2,ACTARR2_1_3,ACTARR2_1_4,ACTARR2_2_1,ACTARR2_2_2,ACTARR2_2_3,ACTARR2_2_4,ACTARR2_3_1,ACTARR2_3_2,ACTARR2_3_3,ACTARR2_3_4,ACTARR2_4_1,ACTARR2_4_2,ACTARR2_4_3,ACTARR2_4_4
from NETSQL2.NET.dbo.AF40201 WHERE ReportID=@ReportIDGP9;
GO
