Is there a way to programmatically add attribute fields to a person report

Question

Crossing Church wants a person report that includes all the person attributes. Rather than having to add them one-by-one in the UI is there a SQL script that can be used to add the attributes to the report?

Answer 1

The following SQL query will add attribute fields to a report:

/* SET THE REPORT ID TO ADD ATTRIBUTE FIELDS HERE */
DECLARE @ReportId int = 682

/*  IF YOU WANT TO DELETE ALL EXISTING ATTRIBUTE FIELDS PRIOR TO ADDING SELECTED ATTRIBUTES, YOU CAN UN-COMMENT THIS DELETE STATEMENT */
/*
DELETE [ReportField]
WHERE [ReportId] = @ReportId
AND [ReportFieldType] = 1
*/

DECLARE @OrderOffset int = ISNULL( ( SELECT MAX([ColumnOrder]) FROM [ReportField] WHERE [ReportId] = @ReportId ), 0 ) + 1

/* DEFINE YOUR ATTRIBUTE QUERY HERE (THIS ONE JUST ADDS FIRST 10 ATTRIBUTES */
INSERT INTO [ReportField] ( [Selection], [ColumnHeaderText], [ReportId], [ReportFieldType], [ShowInGrid], [Guid], [ColumnOrder] )
SELECT TOP 10 LOWER(REPLACE(CAST([Guid] as varchar(36)),'-','')), [Name], @ReportId, 1, 1, NEWID(), -1
FROM [Attribute]
WHERE [EntityTypeId] = 15
AND [FieldTypeId] NOT IN ( 
    114 -- Lava
)ORDER BY [Name]

-- Fix the column order 
UPDATE [ReportField]
SET [ColumnOrder] = [Id] - ([Id] - @OrderOffset)
WHERE [ReportId] = @ReportId
AND [ReportFieldType] = 1
AND [ColumnOrder] = -1