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))



Declare @TotalAuditRecord int  = null
declare @Increment int  = null
Declare @CurrentAttribute VarChar(max) = null
declare @CurrentValue VarChar(max) = null
Declare @IAttributemask varchar(max) = null
declare @IChangeData varchar(max) = null
declare @ICreatedOn datetime = null
declare @IAuditId uniqueidentifier = null
declare @IObjectTypeCode int = null

--Get contact audit records and store it into the @AuditTable temp table.
--I am only taking audit records if the attribute contains ",5,",
--because i need only audit report for particular field changes

insert into @AuditTable(AuditId, ObjectTypeCode, AttributeMask, ChangeData, CreatedOn)
select
    AuditId, ObjectTypeCode,
    Case When IsNull(AttributeMask, '') = '' Then '' Else Substring(AttributeMask, 2, Len(AttributeMask) - 2) End,
    ChangeData, CreatedOn
from
    dbo.Audit with(nolock)
WHERE
    ObjectId =@ContactId
    and AttributeMask like '%,5,%'
order by
    CreatedOn desc

select @TotalAuditRecord = COUNT(*) from @AuditTable
select @Increment = 1

--While loop will split all the changed data and attribute mask into muliple lines of records
--for example, if AttributeMask contains 19,26,5, & changeddata contains "M A K~757580000~757580001"
--while loop will transfer single into 3 lines (line1 19 & M A K, line2 26 & 757580000, line3 5 & 757580001)
--increment will be the line  records, columnkey is attributemask, oldvalue is changed data

while @Increment <= @TotalAuditRecord
BEGIN
    select
        @IObjectTypeCode = ObjectTypeCode, @IAuditId = AuditId, @IAttributemask = AttributeMask, @IChangeData = ChangeData, 
        @ICreatedOn = CreatedOn
    from @AuditTable where id = @Increment

    WHILE CHARINDEX(',',@IAttributemask,0) <> 0
    BEGIN
        SELECT
            @CurrentAttribute=RTRIM(LTRIM(SUBSTRING(@IAttributemask,1,CHARINDEX(',',@IAttributemask,0)-1))),
            @IAttributemask=RTRIM(LTRIM(SUBSTRING(@IAttributemask,CHARINDEX(',',@IAttributemask,0)+1,LEN(@IAttributemask)))),
            @CurrentValue=case when len(@IChangeData)>0 then RTRIM(LTRIM(SUBSTRING(@IChangeData,1,CHARINDEX('~',@IChangeData,0)-1))) else '' end,
            @IChangeData=case when len(@IChangeData)>0 then RTRIM(LTRIM(SUBSTRING(@IChangeData,CHARINDEX('~',@IChangeData,0)+1,LEN(@IChangeData)))) else '' end
    
        --Audit Column List
        IF LEN(@CurrentAttribute) > 0
            insert into @AuditColumnResult(AuditId, Increment, ColumnKey, ColumnName, OldValue,  ObjectTypeCode, CreatedOn)
            Values(@IAuditId, @Increment, CAST(@CurrentAttribute as int),'',  @CurrentValue, @IObjectTypeCode, @ICreatedOn)
        IF CHARINDEX(',',@IAttributemask,0) = 0
            insert into @AuditColumnResult(AuditId, Increment, ColumnKey, ColumnName, OldValue,  ObjectTypeCode, CreatedOn)
            Values(@IAuditId, @Increment, CAST(@IAttributemask as int),'',  @IChangeData, @IObjectTypeCode, @ICreatedOn)
    END
    select @Increment = 1 + @Increment
END

--Updating columnname of columnkey, like columnkey is 4 equal to customertypecode, 6 is leadsourcecode
--Only for selected attribute ('anniversary','customertypecode', 'preferredcontactmethodcode', 'leadsourcecode')
--if columname is empty, delete the record.

update @AuditColumnResult set ColumnName = ma.Name
from
    @AuditColumnResult er
        inner join MetadataSchema.Entity me on me.ObjectTypeCode= er.ObjectTypeCode
        inner join MetadataSchema.Attribute ma on ma.EntityId = me.EntityId and ma.ColumnNumber = er.ColumnKey
    where
        ma.Name in ('anniversary','customertypecode', 'preferredcontactmethodcode', 'leadsourcecode')

DELETE FROM @AuditColumnResult WHERE ColumnName not in ('anniversary','customertypecode', 'preferredcontactmethodcode', 'leadsourcecode')

--Inserting current contact record in the @AuditColumnResult temp table
--UNPIVOT will helps to split rows into column

insert into @AuditColumnResult(AuditId, Increment, ColumnKey, ColumnName, OldValue,  ObjectTypeCode, CreatedOn)
select distinct contactid, 0, 0, unpvt.ColumnName, unpvt.DataValue, @IObjectTypeCode, GETDATE()
from (select contactid,
cast(CONVERT(varchar(10),anniversary,101) as varchar(max)) as anniversary,
cast(customertypecode as varchar(max)) as customertypecode,
cast(leadsourcecode as varchar(max)) as leadsourcecode,
cast(preferredcontactmethodcode as varchar(max)) as preferredcontactmethodcode
from contact where contactid = @ContactId) p
UNPIVOT
(DataValue for ColumnName in (anniversary, customertypecode, leadsourcecode, preferredcontactmethodcode)
                 ) As unpvt

update @AuditColumnResult set rid= id where Increment <>0
update @AuditColumnResult set rid= 0 where Increment =0

--While loop will insert new value for each attribute.
--Get the first record of the columnname, to go up untill you reach next record of the columnname, that's new value.
--untill the audit record (increment 1) not current entity record (increment 0).

DECLARE @FindColumnName varchar(max) = null
declare @NewValue varchar(max) = null
select @Increment = MAX(rid) from @AuditColumnResultwhile @Increment >= 1
BEGIN
    select  @FindColumnName = ColumnName from @AuditColumnResult  WHERE rid = @Increment    SELECT top 1 @NewValue = OldValue FROM @AuditColumnResult where rid<@Increment  and columnname= @FindColumnName order by rid desc
    update @AuditColumnResult set NewValue= @NewValue where rid  = @Increment
    SELECT top 1 @Increment = rid FROM @AuditColumnResult where rid<@Increment order by rid desc
   
END

--Delete increment 0 is the current record of the entity

delete from @AuditColumnResult where Increment =0

--Get the picklist value

insert into @AuditHisoty(AuditId, ColumnKey, ColumnName, Increment,Value)
select 
    rt.AuditId, rt.ColumnKey, rt.ColumnName, rt.Increment, nv.Value
from
 @AuditColumnResult rt left join
 (SELECT Value, AttributeValue, AttributeName from StringMap  where objecttypecode = 2) nv
    on (rt.ColumnName = nv.AttributeName and rt.NewValue = nv.AttributeValue)  left join
 (SELECT Value, AttributeValue, AttributeName from StringMap where objecttypecode = 2) ov
    on  (rt.ColumnName = ov.AttributeName and rt.OldValue = ov.AttributeValue)
where
     rt.ColumnName in ('customertypecode', 'preferredcontactmethodcode', 'leadsourcecode')
union all
select distinct
    rt.AuditId, rt.ColumnKey, rt.ColumnName, rt.Increment, NewValue
from
 @AuditColumnResult rt
where
     rt.ColumnName in ('anniversary')
order by
    Increment asc

--Get the result horizontal way report

SELECT p.Increment, AuditId, CONVERT(varchar(10),anniversary,101),[customertypecode],[leadsourcecode],[preferredcontactmethodcode]
            from
            (
              SELECT a.AuditId, a.Increment, a.ColumnName, a.Value
              FROM @AuditHisoty AS A
            ) p
            pivot
            (
                min(P.Value)
                for P.ColumnName in ([anniversary],[customertypecode],[leadsourcecode],[preferredcontactmethodcode])
            ) p
order by
    p.increment
   

see the final result    

2)create ssrs report (AH.rdl)
    4 parameters
        CRM_URL(null value, hidden, default values null)
        CRM_FilteredContact (hidden, default values null)
        ContactID (null, hidden, get values from a query -> dataset(DSContact), Value(contactid)
        ContactName (null, hidden, get values from a query -> dataset(DSContact), Value(fullname)
    2 datasets
        AuditHistory
            query - copied the above dynamic sql query
            fields - created 6 field name & source (increment, auditid, anniversary, customertypecode, leadsourcecode, preferredcontactmethodcode)
            parametes - @ContactId -> [@ContactId]
   
    view link code
    =Replace(Parameters!CRM_URL.Value,"CRMReports/viewer/drillopen.aspx","") & "tools/audit/audit_details.aspx?_CreateFromId=%7bDB49F88A-05EC-E311-89F7-0050568C6D7D%7d&_CreateFromType=2&id={" & CType(Fields!AuditId.Value, GUID).ToString  & "}"
3)In CRM, create a new report (Registration History), choose the existing report file(AH.rdl), related record types (contact), display in (all)

3)Goto CRM, select the contact record -> select '...' -> select run report -> select 'Registration History' link

see the result in ssrs report , view will open the audit details page.

 that's it, finally client is impressed and see the happy path.