How to convert custom reports from Fluentis2015 to Fluentis2021
This guide shows how to convert customized reports from the Fluentis2015 version to the Fluentis2021 version. In the first part we will work through queries. Next we will edit the XML report to rename the properties that have changed.
The following method is the one to use if there are many custom reports and implies a good knowledge of objects and reports. Another way is to open the report through the Ribbon Report Designer, press validate on the scripts side and you will have a series of warnings for calculated fields, scripts in which there are properties to be renamed. To do this, simply drag the correct field onto the label or write the expression with the renamed property.
Using the method below, in some cases of properties to be renamed won't work. Once the procedure is finished, launch the report in 2015 and 2021 and compare that the result is the same. If not, go through the Ribbon to verify the problem.
Prerequisites
In order to carry out the following operations, it is necessary to have the database version 2015 and the database just converted to 2021. In addition to this, it is necessary to have seen the Xtrareport course and to have a good knowledge of the management of objects in Fluentis.
Report Migration
To move the databases, copy the following query and launch it on the 2021 database. By appropriately changing the two database names at the end of the query.
INSERT INTO [Arm].[RPT_Reports]
([RPTR_Code]
,[RPTR_Name_DICTDE_Id]
,RPTR_CompressedDefinition
,[RPTR_BusinessObject_OBJBO_Id]
,[RPTR_ItemNature]
,[RPTR_Description]
,[RPTR_IsSubreport]
,[RPTR_Context]
,[RPTR_Country_GENC_Id]
,[RPTR_LastModified]
,[RPTR_Version]
,[RPTR_ArmObjectGuid]
,[RPTR_MaxRecords]
,[RPTR_MaxMinutes]
,[RPTR_Order])
SELECT
[RPT_Code]
,[RPT_Name_DICT_Id]
,COMPRESS(Convert(nvarchar(MAX), rpt_definition))
,[RPT_OBJ_Id]
,[RPT_ItemNature]
,RPT_Description
,[RPT_IsSubreport]
,[RPT_Context]
,[RPT_CTRY_Id]
,[RPT_LastModified]
,[RPT_Version]
,[RPT_Guid]
,[RPT_MaxRecords]
,[RPT_MaxMinutes]
,[RPT_Order]
FROM nomeDatabase2015.arm.RPT_Reports
WHERE RPT_ItemNature = 2 AND RPT_OBJ_Id IN (SELECT t.OBJBO_Id FROM nomeDatabase2021.arm.OBJ_BusinessObjects AS t)
Identify report with SQL queries to rename
After completing the first query proceed to run the query below on the 2015 database. The query saves a table in the 2015 database (select * from Report_Result) with table names and column names and report code that have been deleted and/or renamed and then searches in the report definition if there are any queries that contain these tables and columns.
The query may take a long time depending on the number of custom reports. If it is interrupted it must be re-launched. To get it to pick up where it came from, or execute it in transaction.
Replace 2015 with 2021 database with correct name
IF OBJECT_ID (N'Report_Result', N'U') IS NOT NULL
drop table [Database2015].dbo.Report_Result
CREATE TABLE [Database2015].[dbo].[Report_Result](
[RPTR_Id] [int] NULL,
[RPTR_Code] [nvarchar](255) NULL,
[Table_name] [nvarchar](max) NULL,
[Colum_name] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
IF OBJECT_ID (N'Report_CustomList', N'U') IS NOT NULL
drop table [Database2015].dbo.Report_CustomList
SELECT RPTR_Id, RPTR_Code, RPTR_Definition = CONVERT(NVARCHAR(MAX), convert(XML, decompress(RPTR_CompressedDefinition ))) into [Database2015].dbo.Report_CustomList
FROM [Database2021].arm.RPT_Reports
where RPTR_ItemNature = 2 and CONVERT(NVARCHAR(MAX), convert(XML, decompress(RPTR_CompressedDefinition ))) like '%using System.Data;%'
IF OBJECT_ID (N'Report_QueryRename', N'U') IS NOT NULL
drop table [Database2015].dbo.Report_QueryRename
select distinct DB2015.TABLE_NAME, DB2015.COLUMN_NAME into [Database2015].dbo.Report_QueryRename
from (
select distinct table_name, COLUMN_NAME
FROM [Database2015].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'Database2015'
AND TABLE_SCHEMA in ('Fluentis','DBO')
and table_name not like '\_%' escape '\'
and COLUMN_NAME not like '\_%' escape '\') DB2015
left join (select distinct table_name, COLUMN_NAME
from [Database2021].INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA in ('Fluentis','DBO')
and table_name not like '\_%' escape '\'
and COLUMN_NAME not like '\_%' escape '\') DB2021
on DB2015.TABLE_NAME = DB2021.TABLE_NAME and DB2015.COLUMN_NAME = DB2021.COLUMN_NAME
where DB2021.TABLE_NAME is null
declare @table1 as table(RPTR_Id int, RPTR_Code nvarchar(max), Table_name nvarchar(max), Colum_name nvarchar(max))
while(select count(*) from Report_CustomList) > 0
begin
declare @RPTR_Id int = (select top 1 RPTR_Id from Report_CustomList)
insert into @table1
SELECT distinct table1.RPTR_Id, table1.RPTR_Code, table2.TABLE_NAME, null
FROM (select top 1 * from Report_CustomList where RPTR_Id = @RPTR_Id) table1
INNER JOIN (select distinct TABLE_NAME from [Database2015].dbo.Report_QueryRename ) table2 ON table1.RPTR_Definition LIKE CONCAT('%', table2.TABLE_NAME, '%')
if (select count(*) from @table1 where RPTR_Id = @RPTR_Id) = 0
begin
insert into @table1
SELECT distinct table1.RPTR_Id, table1.RPTR_Code, null, table2.COLUMN_NAME
FROM (select top 1 * from Report_CustomList where RPTR_Id = @RPTR_Id) table1
INNER JOIN (select distinct COLUMN_NAME from [Database2015].dbo.Report_QueryRename ) table2 ON table1.RPTR_Definition LIKE CONCAT('%', table2.COLUMN_NAME, '%')
end
insert into Report_Result
select * from @table1 where RPTR_Id = @RPTR_Id
delete from Report_CustomList where RPTR_Id = @RPTR_Id
end
select * from Report_CustomList
select * from Report_Result
When the query is finished execute the following select query: select * from Report_Result, open the reports one at a time and rename the queries using the ARM converter, as follows.
How to rename queries inside a report
First, launch the general select query. Take the code of the first report and paste it into the query under which it will give us as a result all the possible SQL tables to be renamed (and consequently the fields). In the table there are report code and SQL table name to be renamed.
select * from Report_Result with(nolock)
select * from Report_Result with(nolock)
where RPTR_Code like 'reportCode'
As the reports are arranged, they can be removed from this table. Here everyone can adopt the method he prefers to mark which reports he has made and which not (e.g. a Microsoft Excel file).
delete from Report_Result where RPTR_Code like 'reportCode'
Open the report in Ribbon Report Designer in order to have the query, copy the query and open ARM 2021. Click Home > Database (click on the image):
Click the convert script button:
Paste the query to be converted into the left column and press the blue arrow button. On the right column there will be the converted query to paste into the report.
Identify reports in which there are object's properties to be renamed
IF OBJECT_ID (N'Report_Result', N'U') IS NOT NULL
IF OBJECT_ID (N'Report_PropertyResult', N'U') IS NOT NULL
drop table [Database2015].dbo.Report_PropertyResult
CREATE TABLE [Database2015].[dbo].[Report_PropertyResult](
[RPTR_Id] [int] NULL,
[RPTR_Code] [nvarchar](255) NULL,
[Property_Name] [nvarchar](max) NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
IF OBJECT_ID (N'Report_CustomPropertyList', N'U') IS NOT NULL
drop table [Database2015].dbo.Report_CustomPropertyList
SELECT RPTR_Id, RPTR_Code, RPTR_Definition = CONVERT(NVARCHAR(MAX), convert(XML, decompress(RPTR_CompressedDefinition ))) into [Database2015].dbo.Report_CustomPropertyList
FROM [Database2021].arm.RPT_Reports
where RPTR_ItemNature = 2
IF OBJECT_ID (N'Report_PropertyRename', N'U') IS NOT NULL
drop table [Database2015].dbo.Report_PropertyRename
Select distinct p2015.OBJPROP_Code into [Database2015].dbo.Report_PropertyRename
from [Database2015].Arm.OBJ_BusinessObjectProperties p2015
left join (Select *
from [Database2021].Arm.OBJ_BusinessObjectProperties ) p2021
on p2015.OBJPROP_Code = OBJPROIP_Code
where p2021.OBJPROIP_Id is null and p2015.OBJPROP_Code not in ('Acc','PurchaseOrders','Sel','Val','Obj','PR','Print','Comp','DocRif','For','ISO','SalesInvoices','String','Arm','ABI','Bill','CAB','Cin','WIP','Web','ToDay','Stamp','SalesOrders','SalesDeliveryNotes'
,'Rig','Revenue','Reimbursement','QuantityDecimalsNo','PurchaseInvoices','PurchaseDestinationProv','Purchase','ProposedCost','Present','NumerotationType','HumanResource','Effective','Div','Controls','Gen','Set','Leg','Act','Product','Production','Cont','FromProductionOrder','Generic'
,'PayingIn','Summary','TotalDoc','Compulsory','Visible','DocType','Extern','Monthly','NonTaxableOperations','Vendor','Visible','Back','CalcCredit','CalcDebit','Fore','F_Ledger','Bold','Italic','ReportType','ViewTotal','Fixed','Subcontractor','Deduction','Flag','Of','Indirect','MaturityPayment'
,'Intra','CodIva','Min','Dic','Codice','ShowQuantity','Turnover','Qta','BOM','Pers','BirthProv','Split','Supplier','Sing','Mag','Interim','Additional','Load','Calculated','Assigned','Tipo','ShowDetails','Prezzo','Floor','Export','Pagamento',
'Analytic','PreviewedDate','PlannedOrder','CurrencyId','Nir','PrevYear','Delivered','Selected','F_Agents','CodeDescription','DocType','DataMember','SalesJobOrderItemId','SalesJobOrderId','Banca','Fiscale')
order by OBJPROP_Code
declare @table1 as table(RPTR_Id int, RPTR_Code nvarchar(max), Property_name nvarchar(max))
while(select count(*) from Report_CustomPropertyList) > 0
begin
declare @RPTR_Id int = (select top 1 RPTR_Id from Report_CustomPropertyList)
insert into @table1
SELECT distinct table1.RPTR_Id, table1.RPTR_Code, table2.OBJPROP_Code
FROM (select top 1 * from Report_CustomPropertyList where RPTR_Id = @RPTR_Id) table1
INNER JOIN (select distinct OBJPROP_Code from [Database2015].dbo.Report_PropertyRename ) table2
ON table1.RPTR_Definition LIKE CONCAT('%[(.)(")( )([)]', table2.OBJPROP_Code, '[(.)(")( )([)(;)(=)(!)(()(\])]%') escape '\'
insert into Report_PropertyResult
select * from @table1 where RPTR_Id = @RPTR_Id
delete from Report_CustomPropertyList where RPTR_Id = @RPTR_Id
end
select count(*) from Report_CustomPropertyList with(nolock)
select * from Report_PropertyResult with(nolock)
How to rename properties inside a report
First, launch the general select query. Take the code of the first report and paste it into the query under which it will give us as a result all the possible properties to rename.
select * from Report_PropertyResult with(nolock)
select * from Report_PropertyResult with(nolock)
where RPTR_Code like 'reportCode'
As the reports are arranged, they can be removed from this table. Here everyone can adopt the method he prefers to mark which reports he has made and which not (e.g. a Microsoft Excel file).
delete from Report_PropertyResult where RPTR_Code like 'reportCode'
Go to the object browser and check the box Reports, paste and search for the report code, then click right button and edit to be able to enter the XML.
Search inside the XML for the property. Press CTRL + F to open the finder and paste the property to rename inside. Carefully evaluate if this string (the one we are looking for in the report) is a property/object/namespace to be renamed. Replace the string with the new property by finding it in the attached Microsoft Excel file; save the XML.
In this case AbiCode is a property of Bank. Open the Microsoft Excel and search for the property, we should find it in the column of 2015, copy the corresponding of 2021 and paste it in the report XML. Proceed in this way until you finish the report properties and then delete it from the list and proceed with the next one.
The file is the following: This is the link to the Microsoft Excel file where the renamed properties are contained
Summing Up
After completing all the above procedure, if you find other properties as you test the reports to be renamed you can also search directly if they are present in the other custom reports. Replace the affected property with Recording.
select RPTR_Code
from arm.RPT_Reports with (nolock)
where convert(nvarchar(max), DECOMPRESS(RPTR_CompressedDefinition)) like '%.Recording.%'
or convert(nvarchar(max), DECOMPRESS(RPTR_CompressedDefinition)) like 'Recording.%'
or convert(nvarchar(max), DECOMPRESS(RPTR_CompressedDefinition)) like ' Recording '
or convert(nvarchar(max), DECOMPRESS(RPTR_CompressedDefinition)) like '%"Recording"%'
or convert(nvarchar(max), DECOMPRESS(RPTR_CompressedDefinition)) like '%[Recording%'
and rptr_itemnature = 2