Creating the SQL Base Report Definition

  • Article

Create the base report definition using a set of INSERT statements in 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

-- ReportType
DECLARE @Static_SQL tinyint
SELECT @Static_SQL = 2

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, 'Report Creator', 0)
SELECT @ReportID = @@identity

Copyright © 2005 Microsoft Corporation.
All rights reserved.