Reporting Using Scheduled Exports - Precisely_EnterWorks - EnterWorks - 11.0

Detailed reports can be generated from the CN_Log repository contents using the View (SQL) type of Scheduled Export. The SQL for the export can pull information from the Change Notification Registry and Log repositories as well as from any other repository to include contextual information:

The complexity of the reporting is limited to the complexity of the SQL SELECT statement or Stored Procedure that is defined to generate that report. For example, to generate a report that includes key business fields for each change row:

the CN_Attribute_Log_Always value for those attributes are set to Yes.

The Change Notification log will include those attributes and their values for each logged change:

Then using the PIVOT function in SQL Server, these entries can be converted to columns in the report:

select l.CN_Changed_By as [User], CN_Changed_Datetime as [Change Date], CN_Repository_Name as Repository, 
CN_PK1 as PK1, isnull(CN_PK2, '') as PK2, isnull(CN_PK3, '') as PK3, 
isnull(c.Dept, '') as Dept, isnull(c.[Vendor #], '') as [Vendor #], 
isnull(c.[Mfg. #], '') as [Mfg. #], isnull(c.Product, '') as [Product (aka JS#)],
CN_Attribute_Name as Attribute, isnull(CN_Old_Value, '') as [Prior Value],
	isnull(CN_New_Value, '') as [New Value]
 from CN_Log l
left outer join (Select CN_Change_ID, Dept, [Vendor #], [Mfg. #], Product from 
(SELECT CN_Change_ID, CN_Attribute_Name, CN_New_Value
 FROM (select CN_Change_ID, CN_Attribute_Name, CN_New_Value
 from CN_Log where CN_Attribute_Name in ('Dept','Vendor #','Mfg. #','Product')) e
) AS X
PIVOT
(
MAX (CN_New_Value)
FOR CN_Attribute_Name IN (Dept, [Vendor #], [Mfg. #], Product)
) AS PivotTable) c 
on c.CN_Change_ID = l.CN_Change_ID
 where not (l.CN_Attribute_Name in ('Dept','Vendor #','Mfg. #', 'Product') AND
	l.CN_Old_Value = l.CN_New_Value)
	 order by l.CN_Change_ID, CN_Attribute_Name

The above SQL query creates a pivoted table containing the data from only the change records for the attributes: Dept, Vendor #, Mfg. #, and Product. This data is joined to the main log by the Change ID. The same entries are excluded from the main entry unless they have been changed. This results in each row of the report represent a changed value.

To produce a delta report, showing only the changes since the previous report, the SQL must include the condition comparing the Created column to the [DELTA_DATETIME] field:

where not (l.CN_Attribute_Name in ('Dept','Vendor #','Mfg. #', 'Product') AND
	l.CN_Old_Value = l.CN_New_Value)  and l.Created > '[DELTA_DATETIME]'