SQL-Based Reports - Precisely_EnterWorks - EnterWorks - 11.0

When the Change Notification functionality is used to log changes to the CN_Log repository, the data can be exported based on search criteria (such as repository or date range) without needing to use SQL. However, the resulting report will show referential attributes (attributes and their values that provide context such as key business fields, but aren't necessarily part of the change) as separate rows in the export since they are stored that way in the CN_Log repository.

A more useful format for the report is one that includes the referential fields for each change row.

To produce the above report requires creating a View (SQL) scheduled export with a PIVOT like the following:

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

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]'

There may also be use cases (such as the one described in the first section) where a report needs to extract the first old value and the last new value for each attribute from the CN_Log entries that have been created since the last time the report ran. Generating such a report increases the complexity of the required SQL query.