Reporting database

This document describes the tables and procedures of the Infinity Reporting Database.

Tables

rHistory_Detail

This table holds information on past events.

Field Name	Type	Length	Description
His_ID	int	4	Unique ID for record
His_SysID	int	4	Link to pContacts table (Con_SysID)
His_SubSysID	int	4	Link to pContactsSub table (consub_ID)
His_Mode	nvarchar	15	The direction of the event
His_CTIServer	nvarchar	50	Name of CTI Control being used
His_CTIMethod	nvarchar	50	Indicates the mode the CTI layer was operating - simple/complex
His_CallDate	datetime	8	Date event occurred
His_CallStartTime	nvarchar	8	Start time
His_CallEndTime	nvarchar	8	End time
His_CallDuration	float	8	Duration in seconds
His_Hour	int	4	Hour event started
His_ProjectID	int	4	Link to sProject table (pro_ID)
His_Project	nvarchar	100	Link to sProject table (pro_Title)
His_SkillID	int	4	Link to sSkills table (ski_ID)
His_Team	nvarchar	100	Link to sSkills table (ski_Description)
His_TeamType	nvarchar	10	Link to sSkills table (ski_SkillType)
His_ReportingTeamID	int	4	Link to sReportingTeam table (team_ID)
His_ReportingTeam	nvarchar	100	Link to sReportingTeam table (team_Title)
His_UserID	int	4	Link to sUsers table (use_ID)
His_User	nvarchar	100	Link to sUsers table (use_Logon)
His_UserFullName	nvarchar	100	Link to sUsers table (use_FirstName + use_Surname)
His_SubCampaign1	nvarchar	50	Reporting information from event
His_SubCampaign2	nvarchar	50	Reporting information from event
His_SubCampaign3	nvarchar	50	Reporting information from event
His_SubCampaign4	nvarchar	50	Reporting information from event
His_SubCampaign5	nvarchar	50	Reporting information from event
His_SubCampaign6	nvarchar	50	Reporting information from event
His_CallOutcomeCode	nvarchar	50	Link to pCallResults table (Call_CallOutcomeCode)
His_SystemCallOutcome	nvarchar	50	Link to pCallResults table (Call_CallOutcomeSystem)
His_CallOutcome	nvarchar	200	Link to pCallResults table (Call_CallOutcomeDesc)
His_SubCode	nvarchar	50	Link to pSubCodes table (sub_Code)
His_SubDesc	nvarchar	50	Link to pSubCodes table (sub_Desc)
His_RecordStatus	int		Link to pResultCodes table (Res_Code)
His_VoiceFileID	nvarchar	50	May hold call recording information
His_ExtNumber	nvarchar	20	Users extension number
His_DialledNumber	nvarchar	50	Number dialled
His_DDI	nvarchar	50
His_TaskType	int	4	Link to pTaskTypes table (tt_ID)
His_TaskReference	int	4	Link to task reference table, for example pEmail or pFulfilment
His_Notes	nvarchar	max	Comments recorded against event
His_BreakTime	float	8	Time spent in break (in secs.)
His_WaitTime	float	8	Time spent in wait (in secs.)
His_PreviewTime	float	8	Time spent in preview (in secs.)
His_DialTime	float	8	Time spent in dial (in secs.)
His_TalkTime	float	8	Time spent in talk (in secs.)
His_WrapTime	float	8	Time spent in wrap (in secs.)
His_Connect	bit	1	Flag to indicated connected
His_Complete	bit	1	Flag to indicated complete
His_Effective	bit	1	Flag to indicated effective
His_Affectable	bit	1	Flag to indicated affectable
His_Recycle	bit	1	Flag to indicated recycled
His_RecycleSetting	nvarchar	50	Time recycle planned for
His_Abandon	bit	1	Flag to indicated abandoned
His_Sale	bit	1	Flag to indicated sale
His_SalesValue	float	8	Value of sale

rHistory_ISummary

This table holds summarised data from the rHistory_Detail table, grouped as an hourly view.

Field Name	Type	Length	Description
HisI_URN	int	4	Unique ID for record
HisI_Date	datetime	8	Date grouping
HisI_ProjectID	int	4	Link to sProject table (pro_ID)
HisI_Project	nvarchar	100	Link to sProject table (pro_Title)
HisI_SkillID	int	4	Link to sSkills table (ski_ID)
HisI_Team	nvarchar	100	Link to sSkills table (ski_Description)
HisI_TeamType	nvarchar	10	Link to sSkills table (ski_SkillType)
HisI_ReportingTeamID	int	4	Link to sReportingTeam table (team_ID)
HisI_ReportingTeam	nvarchar	100	Link to sReportingTeam table (team_Title)
HisI_UserID	int	4	Link to sUsers table (use_ID)
HisI_User	nvarchar	100	Link to sUsers table (use_Logon)
HisI_UserFullName	nvarchar	100	Link to sUsers table (use_FirstName + use_Surname)
HisI_Hour	int	4	Hour grouping
HisI_Sub1	nvarchar	50	Reporting information grouping
HisI_Sub2	nvarchar	50	Reporting information grouping
HisI_Sub3	nvarchar	50	Reporting information grouping
HisI_Sub4	nvarchar	50	Reporting information grouping
HisI_Sub5	nvarchar	50	Reporting information grouping
HisI_Sub6	nvarchar	50	Reporting information grouping
HisI_DDI	nvarchar	50
HisI_SystemHours	float	8	Total time spent in break/wait/preview/dial/talk/wrap (in secs.)
HisI_BreakTime	float	8	Total time spent in break (in secs.)
HisI_WaitTime	float	8	Total time spent in wait (in secs.)
HisI_PreviewTime	float	8	Total time spent in preview (in secs.)
HisI_DialTime	float	8	Total time spent in dial (in secs.)
HisI_TalkTime	float	8	Total time spent in talk (in secs.)
HisI_WrapTime	float	8	Total time spent in wrap (in secs.)
HisI_Dials	int	4	Number of events handled
HisI_Connects	int	4	Sum of connected flags
HisI_Completes	int	4	Sum of complete flags
HisI_Effectives	int	4	Sum of effective flags
HisI_NonEffectives	int	4	Sum of non-effective flags
HisI_Affectables	int	4	Sum of affectable flags
HisI_Recycles	int	4	Sum of recycle flags
HisI_Abandonds	int	4	Sum of abandoned flags
HisI_Sales	int	4	Sum of sale flags
HisI_SalesValue	float	8	Sum of sale values

rHistoryRecording

This table holds information on call recordings linked to events of rHistory_Detail table.

Field Name	Type	Length	Description
HisRec_ID	int	4	Unique ID for record
HisRec_HisID	int	4	Link to rHistory_Detail table (His_ID)
HisRec_SysID	int	4	Link to pContacts table (Con_SysID)
HisRec_SubSysID	int	4	Link to pContactsSub table (consub_ID)
HisRec_ProjectID	int	4	Link to sProject table (pro_ID)
HisRec_Recorder	int	4	Link to sRecorder table (Rec_ID)
HisRec_Reference	nvarchar	255	Information about recording
HisRec_CallDateTime	datetime	8	Date/Time of recording event
HisRec_CallDuration	int	4	Duration of recording event
HisRec_Status	smallint	2	Status of recording event
HisRec_Comments	ntext	16	Comments

rScreenTimings

This table holds the Screen timing information related to an event.

Field Name	Type	Length	Description
rscr_ID	int	4	Unique ID for record
rscr_Date	datetime	8	Date event occurred
rscr_HistoryID	int	4	Link to rHistory_Detail table (His_ID)
rscr_ProjectID	int	4	Link to sProject table (pro_ID)
rscr_Project	nvarchar	100	Link to sProject table (pro_Title)
rscr_SkillID	int	4	Link to sSkills table (ski_ID)
rscr_Skill	nvarchar	100	Link to sSkills table (ski_Description)
rscr_ReportingTeamID	int	4	Link to sReportingTeam table (team_ID)
rscr_ReportingTeam	nvarchar	100	Link to sReportingTeam table (team_Title)
rscr_UserID	int	4	Link to sUsers table (use_ID)
rscr_User	nvarchar	100	Link to sUsers table (use_Logon)
rscr_UserFullname	nvarchar	255	Link to sUsers table (use_FirstName + use_Surname)
rscr_XML	ntext	16	Screen Timing XML Data

rStateTimings

This table holds user state timing details.

Field Name	Type	Length	Description
rsta_ID	int	4	Unique ID for record
rsta_HistoryID	int	4	Link to rHistory_Detail table (His_ID)
rsta_ProjectID	int	4	Link to sProject table (pro_ID)
rsta_Project	nvarchar	100	Link to sProject table (pro_Title)
rsta_SkillID	int	4	Link to sSkills table (ski_ID)
rsta_Skill	nvarchar	100	Link to sSkills table (ski_Description)
rsta_ReportingTeamID	int	4	Link to sReportingTeam table (team_ID)
rsta_ReportingTeam	nvarchar	100	Link to sReportingTeam table (team_Title)
rsta_UserID	int	4	Link to sUsers table (use_ID)
rsta_User	nvarchar	100	Link to sUsers table (use_Logon)
rsta_UserFullname	nvarchar	255	Link to sUsers table (use_FirstName + use_Surname)
rsta_XML	ntext	16	State Timing XML Data

Stored Procedures

Core

Stored Procedure	Description
inf40_REPORTING_InsertSummary	Used for generating an entry into rHistory_ISummary (now obsolete)
inf40_REPORTING_InsertSummaryWithDDI	Used for generating an entry into rHistory_ISummary

Reporting (rdlc)

Stored Procedure	Report
inf40_REPORTING_AgentPerformanceSummary	Agent Performance Summary.rdlc
inf40_REPORTING_AgentPerformanceSummarySplit1	Agent Performance Summary By Data Split 1.rdlc
inf40_REPORTING_AgentPerformanceSummarySplit2	Agent Performance Summary By Data Split 2.rdlc
inf40_REPORTING_AgentProductivity	Agent Productivity.rdlc
inf40_REPORTING_AgentSummaryAccumulative	Agent Summary Accumulative.rdlc
inf40_REPORTING_CallOutcome	Call Outcome.rdlc
inf40_REPORTING_CallOutcomeAverageTimings	Call Outcome Average Timings.rdlc
inf40_REPORTING_CallOutcomeByAgent	Call Outcome By Agent.rdlc
inf40_REPORTING_CallOutcomeByDatafile	Call Outcome By Datafile.rdlc
inf40_REPORTING_CallOutcomeBySplit1	Call Outcome By Split 1.rdlc
inf40_REPORTING_HourlyCallOutcome	Hourly Call Outcome.rdlc
inf40_REPORTING_HourSummary	Hour Summary.rdlc
inf40_REPORTING_MonthSummary	Month Summary.rdlc
inf40_REPORTING_Split1Conversion	Split 1 Conversion.rdlc
inf40_REPORTING_Split2Conversion	Split 2 Conversion.rdlc