This is an old revision of the document!

BeEasy - Reports (2024-03-14)

In data 13/03/2024 è stato richiesto la creazione di N custom report. Questi custom reports sono stati prima studiati da noi (BeCloud) per poter verificare la fattibilità del progetto e a seguito dello studio sono stati identificati i seguenti reports per commessa:
1) Acque Bresciane:

Export Massivo ARERA
Riepilogo Annuo Chiamate Inbound per servizio utenti e TICSI
1. Servizio UTENTI wait custom fields –> TBD.
Report Semestrale ARERA servizio utenti
Report Giornaliero ABs
Report 30 minuti ABs

2) ASM Voghera:

Riepilogo Annuo Chiamate Inbound Annuo ASM Voghera
Riepilogo Annuo ASM Voghera Credito
Report Semestrale ARERA ASM Voghera
Report Annuo Autoletture ASM Voghera

2) ASM Voghera:

Riepilogo Annuo Chiamate Inbound Annuo ASM Voghera
Riepilogo Annuo ASM Voghera Credito
Report Semestrale ARERA ASM Voghera
Report Annuo Autoletture ASM Voghera

3) Broni Stradella:

Riepilogo Annuo Chiamate Inbound BRST

4) ISIDATA:

Riepilogo Annuo Chiamate Outbound Isidata

5) Metano Nord:

Report Semestrale ARERA Metano Nord
(*TBD*) Report Annuo Chiamate Inbound per Commessa

6) Pavia Acque:

Export Massivo ARERA
Riepilogo Annuo Chiamate Inbound per Pavia Acque
Riepilogo Chiamate Annuo Pavia Acque per servizio Credito
Report Semestrale ARERA Pavia Acque
Report Annuo Autoletture Pavia Acque
Riepilogo Annuo Chiamate Outbound Pavia Acque

7) Tari:

Riepilogo Annuo Chiamate Inbound Annuo per Albonese, Cassolnovo, FerreraE, Gravellona, Pieve

8) Utilità:

Riepilogo Annuo Chiamate Inbound per Utilità
Report Semestrale ARERA
Report Giornaliero Inbound Utilita

Report –> Export Massimo
Query:

SELECT
    hex(global_interaction_id) callId,
    from_phone as ani,
    start_time as TStampIn,
    CASE 
        WHEN queue_time > 0 AND talk_time > 0 THEN
            TIMESTAMPADD(SECOND, COALESCE(ivr_time, 0), start_time)
        ELSE NULL
    END AS TStampTransferred,
    CASE 
        WHEN queue_time > 0 AND talk_time > 0 THEN
            TIMESTAMPADD(SECOND, 
                COALESCE(pending_time, 0) +
                COALESCE(queue_time, 0) +
                COALESCE(ivr_time, 0),
            start_time)
        ELSE NULL
    END AS TStampAnswered,
    duration,
    custom3 as OVFL_Varzi,
    TIMESTAMPADD(SECOND, duration, start_time) AS TStampEnd
FROM beeasy_it.call_detail
WHERE  start_time >= $P{start_time} AND start_time < $P{end_time}
AND service_name LIKE 'Acque Bresciane%'  **<-- sostituire con 'Pavia Acque%' per PVAcq**
AND media_type <> 'EMAIL'
ORDER BY initial_start_time, initial_call_id, start_time

Field in Jasper:
ConnID –> $P{FORMATTER_SCRIPTLET}.formatGUID($F{callId})
Ani –> $F{ani}
TStampIn –> $F{TStampIn}
TStampTransferred –> $F{TStampTransferred}
TStampAnswered –> $F{TStampAnswered}
Durata –> $F{duration}
OVFL_Varzi –> $F{OVFL_Varzi}
TStampEnd –> $F{TStampEnd}

Report Caricati in piattaforma con i seguenti nomi:

Export Massivo ARERA ABs / Export Massivo ARERA PVAcq

Report –> Riepilogo_ABs_Credito_TICSI

Query:

SELECT 
start_time,
YEAR(start_time) AS year,
MONTH(start_time) AS month,
service_name,
count(*) as totale_ivr,
COUNT(CASE
    WHEN (queue_time > 0 OR pending_time > 0) AND 
         disposition NOT LIKE 'ABANDONED%' THEN
        1
END) AS offered,
COUNT(CASE
	WHEN (queue_time > 0 or pending_time > 0) AND talk_time > 0 AND 
         disposition NOT LIKE 'ABANDONED%' THEN
        1
END) AS answered,   
(COUNT(CASE
            WHEN (queue_time > 0 OR pending_time > 0) AND talk_time > 0 AND disposition NOT LIKE 'ABANDONED%' THEN 1
        END) * 100.0 / COUNT(*)) AS percentage_answered,
COUNT(CASE
	WHEN disposition LIKE 'ABANDONED%' OR disposition LIKE 'SYSTEM%' THEN 1
END
) as abandoned
FROM beeasy_it.call_detail cd 
WHERE start_time >= $P{start_time} AND start_time < $P{end_time}
AND disposition NOT LIKE 'TRANSFERRED' AND disposition NOT IN ('CONFERENCED', 'CALLER_TRANSFERRED') 
AND service_name = 'Acque Bresciane - TICSI'  
AND (media_type <> 'EMAIL' or media_type is null)
GROUP BY year, month

Field in Jasper:
Mese –> $F{month} == 1 ? “Gennaio” : ($F{month} == 2 ? “Febbraio” : ($F{month} == 3 ? “Marzo” : ($F{month} == 4 ? “Aprile” : ($F{month} == 5 ? “Maggio” : ($F{month} == 6 ? “Giugno” : ($F{month} == 7 ? “Luglio” : ($F{month} == 8 ? “Agosto” : ($F{month} == 9 ? “Settembre” : ($F{month} == 10 ? “Ottobre” : ($F{month} == 11 ? “Novembre” : ($F{month} == 12 ? “Dicembre” : “Invalid Month”)))))))))))
Totale IVR –> $F{totale_ivr}
Offerte Operatore –> $F{offered}
Risposte –> $F{answered}
% Risposte –> $F{percentage_answered}
Abbandonate –> $F{abandoned}

Report –> Report Semestrale ARERA servizio utenti

Query:

SELECT
start_time,
YEAR(start_time) AS year,
MONTH(start_time) AS month,
service_name,
COUNT(CASE
    WHEN (queue_time > 0 OR pending_time > 0) AND 
         disposition NOT LIKE 'ABANDONED%' THEN
        1
END) AS offered,
COUNT(CASE
	WHEN (queue_time > 0 or pending_time > 0) AND talk_time > 0 AND 
         disposition NOT LIKE 'ABANDONED%' THEN
        1
END) AS answered,
((COUNT(CASE
	WHEN (queue_time > 0 or pending_time > 0) AND talk_time > 0 AND disposition NOT LIKE 'ABANDONED%' THEN 1
END)) * 100 / COUNT(CASE
    WHEN (queue_time > 0 OR pending_time > 0) AND 
         disposition NOT LIKE 'ABANDONED%' THEN
        1
END)) as ls_percentage,
100 as indice_as,
avg(queue_time) as tma,
NULL as op_auto
FROM beeasy_it.call_detail cd 
WHERE start_time >= $P{start_time} AND start_time < $P{end_time}
AND disposition NOT LIKE 'TRANSFERRED' AND disposition NOT IN ('CONFERENCED', 'CALLER_TRANSFERRED') 
AND service_name = 'Acque Bresciane'
AND (media_type <> 'EMAIL' or media_type is null)
GROUP BY year, month

Field in Jasper:

Offerte Op. : $F{offered}
Risposte: $F{answered}
% Risposte (Indice LS): $F{ls_percentage}
Indice AS: $F{indice_as}
TMA: $F{tma}
Op. Auto: $F{op_auto}

Creato un “Group” per dividere le row Semestralmente

→ In “Outline” > Groups > Total
1. → Creato Expression: ($F{month} ⇐ 6) ? “First Semester” : “Second Semester”

Allo stesso tempo, creato delle variabili per fare la somma per colonna, nel “Reset Type” ho aggiunto “[Group] Total” in modo che il report fornisca solo le somme/avg per semestre.

Report –> Report Giornaliero ABs
Query:

SELECT
start_time,
YEAR(start_time) AS year,
MONTH(start_time) AS month,
DAY(start_time) AS day,
DAYNAME(start_time) AS nameday,
service_name,
COUNT(CASE
    WHEN (queue_time > 0 OR pending_time > 0) AND 
         disposition NOT LIKE 'ABANDONED%' THEN
        1
END) AS offered,
COUNT(CASE
	WHEN (queue_time > 0 or pending_time > 0) AND talk_time > 0 AND 
         disposition NOT LIKE 'ABANDONED%' THEN
        1
END) AS answered,
(COALESCE(((COUNT(CASE
	WHEN (queue_time > 0 OR pending_time > 0) 
		AND talk_time > 0 
		AND disposition NOT LIKE 'ABANDONED%' THEN 1
END)) * 100.0 / NULLIF(COUNT(CASE
	WHEN (queue_time > 0 OR pending_time > 0) 
		AND disposition NOT LIKE 'ABANDONED%' THEN 1
END), 0)), 0)) AS answer_percentage,
COUNT(CASE
	WHEN (queue_time > 0 or pending_time > 0) AND talk_time > 0 AND 
         disposition NOT LIKE 'ABANDONED%' and custom5 = 'ROVATO' THEN
        1
END) AS answered_rovato,
(COALESCE(((COUNT(CASE
	WHEN (queue_time > 0 OR pending_time > 0) 
		AND talk_time > 0 AND custom5 = 'ROVATO'
		AND disposition NOT LIKE 'ABANDONED%' THEN 1
END)) * 100.0 / NULLIF(COUNT(CASE
	WHEN (queue_time > 0 OR pending_time > 0) 
		AND disposition NOT LIKE 'ABANDONED%' THEN 1
END), 0)), 0)) AS answer_percentage_rovato,
COUNT(CASE
    WHEN (queue_time > 0 OR pending_time > 0) AND 
         disposition NOT LIKE 'ABANDONED%' AND custom5 = 'OVFL' THEN
        1
END) AS offered_ovfl,
COUNT(CASE
	WHEN (queue_time > 0 or pending_time > 0) AND talk_time > 0 AND 
         disposition NOT LIKE 'ABANDONED%' AND custom5 = 'OVFL' THEN
        1
END) AS answered_ovfl,
(COALESCE(((COUNT(CASE
	WHEN (queue_time > 0 OR pending_time > 0) 
		AND disposition NOT LIKE 'ABANDONED%' 
		AND custom5 = 'OVFL' THEN 1
	END)) * 100.0 / NULLIF(COUNT(CASE
		WHEN (queue_time > 0 OR pending_time > 0) 
			AND disposition NOT LIKE 'ABANDONED%' THEN 1
END), 0)), 0)) AS offered_percentage_ovfl,
(COALESCE(((COUNT(CASE
	WHEN (queue_time > 0 OR pending_time > 0) 
		AND talk_time > 0 
		AND disposition NOT LIKE 'ABANDONED%' 
		AND custom5 = 'OVFL' THEN 1
END)) * 100.0 / NULLIF(COUNT(CASE
	WHEN (queue_time > 0 OR pending_time > 0) 
		AND disposition NOT LIKE 'ABANDONED%' THEN 1
END), 0)), 0)) AS answer_percentage_ovfl
FROM beeasy_it.call_detail cd 
WHERE start_time >= $P{start_time} AND start_time < $P{end_time}
AND disposition NOT LIKE 'TRANSFERRED' AND disposition NOT IN ('CONFERENCED', 'CALLER_TRANSFERRED') 
AND service_name = 'Acque Bresciane'
AND (media_type <> 'EMAIL' or media_type is null)
GROUP BY year, month, day

WAIT FOR CUSTOM FIELDS

Report –> Isidata Outbound

Query:

SELECT
start_time,
YEAR(start_time) AS year,
MONTH(start_time) AS month,
service_name,
COUNT(CASE 
	WHEN pending_time > 0 THEN
	1
END) AS calls_made,
COUNT(CASE
	WHEN talk_time > 0 THEN
	1
END) as calls_answered,
COUNT(CASE
	WHEN media_type = 'CHAT' and service_name LIKE '%SMS%' THEN 
	1
END) as sms_sent,
null as sms_ongoing
FROM beeasy_it.call_detail cd 
WHERE start_time >= $P{start_time} AND start_time < $P{end_time}
AND disposition NOT LIKE 'TRANSFERRED' AND disposition NOT IN ('CONFERENCED', 'CALLER_TRANSFERRED') 
AND service_name LIKE 'Isidata%'
AND caller_phone_type = 'INTERNAL'
AND (media_type <> 'EMAIL' or media_type is null)
GROUP BY year, month

Field in Jasper:

Mese –> $F{mese} Chiamate Effettuate –> $F{calls_made} Chiamate Risposte –> $F{calls_answered} SMS inviati –> $F{sms_sent} SMS in corso –> $F{sms_ongoing}