//Report Certification
if WildMatch(ComputerName(),'TAG*') then
Trace The dashboard is being reloaded via the desktop;
vCommonPath = 'xxxxxx'
OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=TCReport;Data Source=wfaxrpt01p;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=TAGxxxx;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is xxxxx);
ELSE
Trace The dashboard is being reloaded on the server;
vCommonPath = 'E:\QlikStorage\CommonFiles'
ENDIF;
// Below we are including the files to be used for Application Certification
$(Must_Include=$(vCommonPath)\FE Standard Include.txt);
$(Must_Include=$(vCommonPath)\FESCertifiedApplicationList\Include Files\IsCertified.qvs);
$(Include=$(vDataConnectionPath)\TCReport.txt);
/*-----------------------------------Required Script------------------------------------------------------*/
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='YYYY-MM-DD';
SET TimestampFormat='YYYY-MM-DD h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
//criteria for Efax: begins with “MXBPOS”
//criteria for SAP fax: begins with “SC”
//criteria for FI fax: Equals "ExpRpt", begins with “TCL”, begins with “NPO”, begins with “SES”, begins with “PC”, begins with “TCS”
Load *,
If([Originator_Group] = 'TCMFPQ4', 'MFP',
If([Originator_Group] LIKE 'MXBPOS*', 'Efax',
If([Originator_Group] LIKE 'SC*', 'SAP',
If([Originator_Group] = 'ExpRpt' OR
[Originator_Group] LIKE 'TCL*' OR
[Originator_Group] LIKE 'NPO*' OR
[Originator_Group] LIKE 'SES*' OR
[Originator_Group] LIKE 'PC*' OR
[Originator_Group] LIKE 'TCS*', 'FI',
If(len(Originator_Group) < 1, 'External', 'Other'
)))))as [Fax Type Inbound]
;
Load *,
If([Recipient_Group] = 'MFPTEMPL', 'MFP',
If([Recipient_Group] LIKE 'MXBPOS*', 'Efax',
If([Recipient_Group] LIKE 'SC*', 'SAP',
If([Recipient_Group] = 'ExpRpt' OR
[Recipient_Group] LIKE 'TCL*' OR
[Recipient_Group] LIKE 'NPO*' OR
[Recipient_Group] LIKE 'SES*' OR
[Recipient_Group] LIKE 'PC*' OR
[Recipient_Group] LIKE 'TCS*', 'FI',
If(len(Recipient_Group) < 1, 'External','Other'
)))))as [Fax Type Outbound ]
;
Load*, if(SenderFaxLineStatus = 'External','External Sender','Internal Sender') as [Sender Status],
if(ReceiverFaxLineStatus = 'External', 'External Recipient', 'Internal Recipient') as [Receiver Status];
Load*, if(SenderFaxLineStatus = 'External','External Sender',
if(ReceiverFaxLineStatus = 'External', 'External Recipient')) as [External Faxes],
if(SenderFaxLineStatus = 'Internal','Internal Sender',
if(ReceiverFaxLineStatus = 'Internal', 'Internal Recipient')) as [Internal Faxes];
Load*, if(len(trim(MsgClass_Originator))=0, 'Internal', 'External') as SenderFaxLineStatus
, if(len(trim(MsgClass_Recipient))=0, 'Internal', 'External') as ReceiverFaxLineStatus
;
LOAD *, num(month(TimeSent)) & '/' & day(TimeSent) & '/' & year(TimeSent) as Date, /* Formatting TimeSent to Month, Day, and Year*/
Time(TimeSent,'h:mm:ss TT') as Time,
Time(TimeSent,'h TT') as HourTT,
TimeStamp(TimeStamp#(Left(TimeStamp(TimeSent, 'MM/DD/YYYY hh:mm:ss'), 13), 'MM/DD/YYYY hh'), 'MM/DD/YYYY hh TT') as [Date|Hour],
time(MakeTime(hour(Time(frac(TimeSent))),0,0),'h TT') as GroupedHour
;
Load *,
month(TimeSent) as Month, /* creating Month Field */
day(TimeSent) as Day, /* creating Day Field */
year(TimeSent) as Year, /* creating Year Field */
Hour(TimeSent) as Hour
;
SQL SELECT TOP 100000 /*Begin SQL*/
"ID_Action" /* Unique fax ID*/
,"Time_Created" as "TimeArrive" /*Time fax hit kofax*/
,"Time_Action" as "TimeSent" /*Time kofax sent out fax */
,"Message_Class" /* IF message class is blank (SAP, FI, or MFPGUEST?) recipient */
,"Duration" /*duration of sending fax in seconds*/
,"Originator" /* IF originator is F, then this is an external fax sender, that came in on a fax line */
,"Originator_Info" /*details of sender (name, phone number, FI/SAP link, etc.)*/
,"Originator_Group" /*Groups associated with User Profiles in Kofax */
,"Recipient" /*IF recipient is F, then fax was sent to an externally dialed number on a fax line */
,"Recipient_Info" /*details of fax recipient (name, phone number, FI/SAP link, etc.)*/
,"Recipient_Group" /*Groups associated with User Profiles in Kofax */
,"Size" /*File size of fax*/
,"Pages" /*Number of pages of fax*/
,"TXT_STATE" /*Status of fax (Success or Fail)*/
,"Delay" /*Delay time of fax (seconds)*/
,"MsgClass_Originator" /*IF value is "FAX", appears that sender used an external line*/
,"MsgClass_Recipient" /*IF value is "FAX", appears that a fax line was used to receive fax into Kofax */
,"TS_CHANNEL" /*Fax line number used for fax, I believe "ZZ" indicates no fax line was used and kept internal*/
,"TS_REC_CHANNEL" /*Fax line number used to receive incoming fax to Kofax*/
,"Channel_Group_X" /* If value is "F" a fax line was used for either receiving or sending a fax */
,"Channel_X" /* The fax line channel used by Kofax for either receiving or sending a fax */
,"ChG_in" /*If value is "F" then a fax line was used to receive incoming fax to Kofax*/
,"ChG_out" /*If value is "F" then a fax line was used to send fax out from Kofax */
,"Subject" /*Subject of Fax*/
FROM TCReport.dbo."Action_Table_Descr" /*Original Table */
WHERE "Recipient_Group" != 'IPPRINT' /*Filtering out fax delivery/non delivery notifications*/
ORDER BY "TimeArrive" DESC /*Filter by most recent records*/
; /*End SQL*/
/*-----------------------------------End Required Script------------------------------------------------------*/
