Creating the SQL Base Report Definition

  • Article

Create the base report definition using a set of INSERT statements into the Report table. The SQL query created in Step 2, is inserted into the [Query] field.

The DmExport and UpmExport bits are set so the report can be exported to Direct Mailer (which uses the rcp_email attribute) and User Profile Management (which uses the rcp_guid attribute).

The ReportType is set to Static_SQL (2). The Protected bit is cleared (set to 0) which allows the report to be overwritten or deleted.

For more information about the Report table, see Analysis/Reporting Schema.

Step 3

DECLARE @ReportID int

DECLARE @ReportStatusID int

INSERT INTO [dbo].[Report] ([DisplayName], [Description], [ReportType],

 [Category], [Query], [DmExport], [UpmExport], [CreatedBy], [Protected])

VALUES ('Registered Users', 'Registered Users', @Static\_SQL, 'Users',

 SQL String, 1, 1, 'the almighty creator', 0)

SELECT @ReportID = @@identity


All rights reserved.