The intended audience for this topic is an application developer that will create a user interface to provide access to CM4D Scheduler-generated Reports.
|
Stored Procedure |
SearchReports |
Query for a list of Reports |
|
Inputs |
RD_Label (default=%) |
Depository Label |
|
RR_Filename (default=%) |
Report Filename |
|
|
FromDate (default=12-31-1899) |
Starting (oldest) date of Report |
|
|
ToDate (default=current date/time) |
Ending (newest) date of Report |
|
|
RF_Name (default=%) |
Filter Name specification |
|
|
RF_Value (default=%) |
Filter Value specification |
|
|
Resulting Record |
RD_Label |
Depository Label |
|
RR_Filename |
Report Filename |
|
|
RR_DateTime |
Report date and time |
|
|
RR_Thumbnail |
File name of thumbnail, if any |
|
Stored Procedure |
ListReportDepositories |
Query for a list of Report Depositories |
|
Inputs |
RD_Label (default=%) |
Depository Label specification |
|
Resulting Record |
RD_Label |
Depository Label |
|
Stored Procedure |
ListReportRecords |
Query for a list of Report Records |
|
Inputs |
RD_Label (default=%) |
Depository Label |
|
RR_Filename (default=%) |
Report Filename |
|
|
FromDate (default=12-31-1899) |
Starting (oldest) date of Report |
|
|
ToDate (default=current date/time) |
Ending (newest) date of Report |
|
|
Resulting Record |
RD_Label |
Depository Label Report Filename File name of thumbnail, if any |
|
RR_Filename |
||
|
RR_Thumbnail |
|
Stored Procedure |
ListReportFilterNames |
Query for a list of Filter Names |
|
Inputs |
RD_Label (default=%) |
Depository Label specification |
|
RF_Name (default=%) |
Filter Name specification |
|
|
Resulting Record |
RD_Label |
Depository Label |
|
RF_Name |
Filter Name |
|
Stored Procedure |
ListReportFilterValues |
Query for a list of Filter Values |
|
Inputs |
RD_Label (default=%) |
Depository Label specification |
|
RF_Name (default=%) |
Filter Name specification |
|
|
RF_Value (default=%) |
Filter Value specification |
|
|
Resulting Record |
RD_Label |
Depository Label |
|
RF_Name |
Filter Name |
|
|
RF_Value |
Filter Value |
|
Stored Procedure |
GetReportDepository |
Query for a specific Report Depository |
|
Inputs |
RD_Label |
Depository Label |
|
Resulting Record |
RD_ Base |
Path Base Path of Depository |
|
Stored Procedure |
GetReportRecord |
Query for a specific Report Record |
|
Inputs |
RD_Label |
Depository Label Report Filename |
|
RR_Filename |
||
|
Resulting Record |
RR_DateTime |
Report date and time File name of thumbnail, if any |
|
RR_Thumbnail |
|
Stored Procedure |
GetReportFilters |
Query for a list of Filters for a specific Report |
|
Inputs |
RD_Label |
Depository Label Report Filename |
|
RR_Filename |
||
|
Resulting Record |
RF_Name |
Filter Name Filter Value |
|
RF_Value |
Example data sets are available. Contact ATS for access.
To install the example data without an existing CM4D database:
1. Place the “Reports” depository folder on the network
2. Edit “CM4D ReportRecord Example Data.SQL” and change the [ReportDepository.RD_BasePath] field to match the “Reports” path.
3. Execute the creation script (CM4D ReportRecord API.SQL) from within SQL Server Management Studio
4. Execute the data insertion script (CM4D ReportRecord Example Data.SQL) from within SQL Server Management Studio
The following is a listing of the CM4D ReportRecord API.SQL script:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [ReportDepository]
(
[RD_ID] [uniqueidentifier] NOT NULL,
[RD_Label] [varchar](255) NOT NULL,
[RD_FolderPath] [varchar](255) NOT NULL,
CONSTRAINT [PK_ReportDepository] PRIMARY KEY CLUSTERED
(
[RD_ID] ASC
)ON [PRIMARY],
CONSTRAINT [IX_ReportDepository_Label] UNIQUE NONCLUSTERED
(
[RD_Label] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [ReportRecord](
[RR_ID] [uniqueidentifier] NOT NULL,
[RR_RD_ID] [uniqueidentifier] NOT NULL,
[RR_Filename] [varchar](255) NOT NULL,
[RR_DateTime] [datetime] NOT NULL DEFAULT '1899-12-31',
[RR_Thumbnail] [varchar](255) NULL,
CONSTRAINT [PK_ReportRecord] PRIMARY KEY CLUSTERED
(
[RR_ID] ASC
)ON [PRIMARY],
CONSTRAINT [IX_ReportRecord] UNIQUE NONCLUSTERED
(
[RR_RD_ID] ASC,
[RR_Filename] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [ReportRecord] WITH NOCHECK ADD CONSTRAINT [FK_ReportRecord_ReportDepository] FOREIGN KEY([RR_RD_ID])
REFERENCES [ReportDepository] ([RD_ID])
ON DELETE CASCADE
GO
ALTER TABLE [ReportRecord] CHECK CONSTRAINT [FK_ReportRecord_ReportDepository]
GO
CREATE TABLE [ReportFilter]
(
[RF_ID] [uniqueidentifier] NOT NULL,
[RF_RR_ID] [uniqueidentifier] NOT NULL,
[RF_Name] [varchar](255) NULL,
[RF_Value] [varchar](255) NULL,
CONSTRAINT [PK_ReportFilter] PRIMARY KEY CLUSTERED
(
[RF_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [ReportFilter] WITH NOCHECK ADD CONSTRAINT [FK_ReportFilter_ReportRecord] FOREIGN KEY([RF_RR_ID])
REFERENCES [ReportRecord] ([RR_ID])
ON DELETE CASCADE
GO
ALTER TABLE [ReportFilter] CHECK CONSTRAINT [FK_ReportFilter_ReportRecord]
GO
CREATE PROCEDURE [SearchReports]
(
@RD_Label varchar(255) = '%',
@RR_Filename varchar(255) = '%',
@FromDate datetime = '1899-12-31',
@ToDate datetime = '2099-12-31',
@RF_Name varchar(255) = '%',
@RF_Value varchar(255) = '%'
)
AS
BEGIN
SET NOCOUNT ON;
SELECT DISTINCT RD_Label, RR_Filename, RR_DateTime, RR_Thumbnail FROM ReportDepository,ReportRecord,ReportFilter
WHERE ((RD_ID = RR_RD_ID) AND (RF_RR_ID = RR_ID)) AND
(RD_Label LIKE @RD_Label AND RR_Filename LIKE @RR_Filename AND (RR_DateTime BETWEEN (@FromDate) AND (@ToDate)) AND
(RF_Name LIKE @RF_Name and RF_Value LIKE @RF_Value))
END
GO
CREATE PROCEDURE [ListReportDepositories]
(
@RD_Label varchar(255) = '%'
)
AS
BEGIN
SET NOCOUNT ON;
SELECT RD_Label FROM ReportDepository
WHERE RD_Label LIKE @RD_Label
END
GO
CREATE PROCEDURE [ListReportRecords]
(
@RD_Label varchar(255) = '%',
@RR_Filename varchar(255) = '%',
@FromDate datetime = '1899-12-31',
@ToDate datetime = '2099-12-31',
)
AS
BEGIN
SET NOCOUNT ON;
SELECT RD_Label, RR_Filename, RR_Thumbnail FROM ReportDepository,ReportRecord
WHERE (RD_ID = RR_RD_ID) AND
(RD_Label LIKE @RD_Label AND RR_Filename LIKE @RR_Filename AND (RR_DateTime BETWEEN (@FromDate) AND (@ToDate) ))
END
GO
CREATE PROCEDURE [ListReportFilterNames]
(
@RD_Label varchar(255)='%',
@RF_Name varchar(255)='%'
)
AS
BEGIN
SET NOCOUNT ON;
SELECT DISTINCT RD_Label, RF_Name FROM ReportDepository,ReportRecord,ReportFilter
WHERE ((RD_ID = RR_RD_ID) AND (RF_RR_ID = RR_ID)) AND
(RD_Label LIKE @RD_Label AND RF_Name LIKE @RF_Name)
END
GO
CREATE PROCEDURE [ListReportFilterValues]
(
@RD_Label varchar(255)='%',
@RF_Name varchar(255)='%',
@RF_Value varchar(255)='%'
)
AS
BEGIN
SET NOCOUNT ON;
SELECT DISTINCT RD_Label, RF_Name, RF_Value FROM ReportDepository,ReportRecord,ReportFilter
WHERE ((RD_ID = RR_RD_ID) AND (RF_RR_ID = RR_ID)) AND
(RD_Label LIKE @RD_Label AND RF_Name LIKE @RF_Name AND RF_Value LIKE @RF_Value)
END
GO
CREATE PROCEDURE [GetReportDepository]
(
@RD_Label varchar(255)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT RD_FolderPath FROM ReportDepository
WHERE @RD_Label = RD_Label
END
GO
CREATE PROCEDURE [GetReportRecord]
(
@RD_Label varchar(255),
@RR_Filename varchar(255)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT RR_DateTime, RR_Thumbnail FROM ReportDepository,ReportRecord
WHERE (RD_ID = RR_RD_ID) AND
(RD_Label = @RD_Label AND RR_Filename = @RR_Filename)
END
GO
CREATE PROCEDURE [GetReportFilters]
(
@RD_Label varchar(255),
@RR_Filename varchar(255)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT RF_Name, RF_Value FROM ReportDepository,ReportRecord,ReportFilter
WHERE ((RD_ID = RR_RD_ID) AND (RF_RR_ID = RR_ID)) AND
(RD_Label = @RD_Label AND RR_Filename = @RR_Filename)
END
GO