serving the solutions day and night

Pages

Showing posts with label audit. Show all posts
Showing posts with label audit. Show all posts

Thursday, June 12, 2014

Dynamic CRM 2011/2013 Audit Report in SSRS

My client want the particular contact entity attributes audit history in the horizontal way SSRS report.
CRM will create one line record in the audit entity for each action (new, update or delete records).
Refer "Dynamics CRM - Audit Entity" - http://makdns.blogspot.com/2014/06/dynamics-crm-audit-entity.html

1)Step created dynamics sql query

declare @contactid uniqueidentifier="00000000-0000-0000-0000-000000000000"> Declare @AuditTable table (id int identity(1,1), AuditId uniqueidentifier, ObjectTypeCode int, AttributeMask varchar(max) null,
        ChangeData varchar(max), CreatedOn datetime)
declare @AuditColumnResult table (id int identity(1,1), rid int null, AuditId uniqueidentifier, Increment int, ColumnKey int,
        ColumnName varchar(max), OldValue varchar(max), NewValue varchar(max), ObjectTypeCode int, createdon datetime)
declare @AuditHisoty table (AuditId uniqueidentifier, Increment int, ColumnKey int, ColumnName varchar(max), Value varchar(max))


Dynamics CRM - Audit Entity

AuditBase table or Audit entity are captured previous data of records.

CRM will create one line record in the audit entity for each action (new, update or delete records).

For example, First Name  Last Name
New Record -  FNC             LNC  => entity record
Old Record -  FN~LN             => audit stored, for example audit id 2, here first name new value is current entity record value  "FNC" and old value is current audit record "FN".
Old Record -  F~L             => audit stored, for example audit id 1, here first name new value is next audit record value  "FN" and old value is current audit record "F"