Visual Call Reports in MS Excel & Google Sheets

[KyriacosS_3CX]

Templates for Google Sheets & Excel from CSV call logs. ​

A 3CX employee has created a prototype tool to help users generate visual reports from their PBX call log data in Microsoft Excel or Google Sheets. You’ll recall, the CDR system has been rewritten to improve data handling and support larger deployments. Larger deployme...

Continue reading the Original Blog Post.

 

[Quirilianus]

Current Excel template has problems with the 3 digit dn system

When developing it was tested mostly with 3 digit (dn) phone-system data, and some 4 digit dn. Then there were some complications and in the latest version of the formulas the 4 digit distinction was not included in some of them. Sorry about that...

example of the error:
In Data-Output column -Y- you will find this formula:
=IF($C2<>"";IF(X2<>"0:00:00";IF(AC2<>"";"";IF(0+IF(AT2="line";1;0)+IF(AT2="dn";IF(ISNUMBER(VALUE(AR2));IF(VALUE(AR2)<800;1;0);0);0)>0;IF(VALUE(LEFT(X2;2))>0;VALUE(LEFT(X2;2))*3600;0)+IF(VALUE(LEFT(RIGHT(X2;5);2))>0;VALUE(LEFT(RIGHT(X2;5);2))*60;0)+IF(VALUE(RIGHT('Data-Input'!H2;2))>0;VALUE(RIGHT(X2;2));0);""));"");"")

It says on the beginning of the second line <800 which is the boundary between extension and system. In your case it is 8000. if you change this it changes immediately.

We will proceed to check the other formulas, and keep you posted

 

[Quirilianus]

Generic correction to the 4 digit dn problem

In Data-Output column -Y- you will find this formula:
=IF($C2<>"";IF(X2<>"0:00:00";IF(AC2<>"";"";IF(0+IF(AT2="line";1;0)+IF(AT2="dn";IF(ISNUMBER(VALUE(AR2));IF(VALUE(AR2)<800;1;0);0);0)>0;IF(VALUE(LEFT(X2;2))>0;VALUE(LEFT(X2;2))*3600;0)+IF(VALUE(LEFT(RIGHT(X2;5);2))>0;VALUE(LEFT(RIGHT(X2;5);2))*60;0)+IF(VALUE(RIGHT('Data-Input'!H2;2))>0;VALUE(RIGHT(X2;2));0);""));"");"")

change the part
<800;1;0)
into
<800;1;IF(VALUE(AR2)<1000;IF(VALUE(AR2)<8000;1;0);0))

It states now that if the extracted dn is
smaller than 800 ==> 1
smaller than 1000 ==>0
larger than 1000 AND smaller than 8000 ==> 1
larger than 8000 ==> 0

The other formulas present no problem

 

[Quirilianus]

this is based on the data. Changes needed:

1) reset the T in the Timestamp
2) copy/paste trhough a txt editor
3) dot to comma for cost
4) <800 to <8000 copy down the formula to the bottom

 

[Grace Liu]

We filtered out the answered calls from the queue in the call logs, downloaded and copied them into the Excel template, but the dashboard shows 0 for Answered calls. Is this normal?

 

[KyriacosS_3CX]

Hello,

The templates expect unfiltered Call Report data in order to have all the legs of each call available for processing - please try removing the filter and check if the issue goes away.

You can ofcourse limit the report dates, but this should be a bit wider than the period of interest to ensure calls that start/end slightly outside that period are captured in full (+/- 1 day would be more than enough).

 

[Grace Liu]

Hello,

The templates expect unfiltered Call Report data in order to have all the legs of each call available for processing - please try removing the filter and check if the issue goes away.

You can ofcourse limit the report dates, but this should be a bit wider than the period of interest to ensure calls that start/end slightly outside that period are captured in full (+/- 1 day would be more than enough).

Hello @KyriacosS_3CX , we have just exported today's call logs and uploaded it to the Excel sample file. It shows 6 unanswered calls, but when we checked the status in the call logs, the number of unanswered calls exceeds 6:

 

[KyriacosS_3CX]

Any chance the time zone offset is off, between what's shown online and the excell data? Local vs UTC?