How to add a period selector on a report

(2 posts) (2 voices)
  • Started 1 week ago by Alex35580
  • Latest reply from myDBR Team
  1. Alex35580, Member

    Hello,
    As a newbie, I need your help. I have tried to add two date pickers in a report, to define dynamically a period as a filter to the sql query below without reloading the webpage.
    Can you help me to create this dynamic filter please ?

    Thanks for your help
    My code is :

    select 'dbr.css', 'body {background:#00a8b5}';
    select 'dbr.css', '.report_top {background-color:#00a8b5}';
    select 'dbr.css', '#dbr_rt3 td {border:1px solid #a9a9a9;}';
    select 'dbr.css', '.align_r {text-align:center}';
    select 'dbr.css', '.align_l {text-align:center}';
    select 'dbr.css', '.row_header, .drill_header, .cell_header, .dbr_calendar .fc-day-header, .dbr_calendar .fc-basic-view th.fc-week-number, .dbr_calendar .fc-week-number.fc-widget-header, .fc-head-container.fc-widget-header {background:linear-gradient(to bottom, rgba(76,76,76,1) 0%,rgba(93, 93, 93, 1) 100%)}';
    select 'dbr.search', 1;
    select 'dbr.header.group', 'Synthesis', 'Num of Calls 1to1','Total call time';
    select 'dbr.header.group', 'Calls 1to1', 'Total call 1to1','1to1>30d';
    select 'dbr.header.group', 'Calls Conf', 'Total call Conf','Conf>30d'; SELECT REPLACE(UPPER(o.COMPANY),'_',' ') as CUSTOMER,
    (SELECT COUNT(*) FROM v5.MAX_DURATION_BY_CALL_ID e WHERE e.COMPANY=o.COMPANY) as 'Num of Calls 1to1',
    (SELECT COUNT(*) FROM v5.MAX_DURATION_BY_CONF_ID m WHERE m.COMPANY=o.COMPANY) as 'Num of Conf',
    SEC_TO_TIME((SELECT COALESCE(SUM(a.DURATION_MAX),0) FROM v5.MAX_DURATION_BY_CALL_ID a WHERE a.COMPANY=o.COMPANY)+(SELECT COALESCE(SUM(m.DURATION_MAX),0) FROM v5.MAX_DURATION_BY_CONF_ID m WHERE m.COMPANY=o.COMPANY)) as 'Total call time',
    (SELECT SEC_TO_TIME(SUM(a.DURATION_MAX)) FROM v5.MAX_DURATION_BY_CALL_ID a WHERE a.COMPANY=o.COMPANY) as 'Total call 1to1',
    SEC_TO_TIME((SELECT SUM(a.DURATION_MAX) FROM v5.MAX_DURATION_BY_CALL_ID a WHERE a.COMPANY=o.COMPANY)/(SELECT COUNT(*) FROM v5.MAX_DURATION_BY_CALL_ID e WHERE e.COMPANY=o.COMPANY)) as 'Avg Call 1to1 duration',
    CONCAT((SELECT SEC_TO_TIME(SUM(b.DURATION_MAX)) FROM v5.MAX_DURATION_BY_CALL_ID b WHERE b.COMPANY=o.COMPANY and (`CALL_DATE` > (NOW() - INTERVAL 7 DAY)) ),' (',(SELECT COUNT(*) FROM v5.MAX_DURATION_BY_CALL_ID f WHERE f.COMPANY=o.COMPANY and (`CALL_DATE` > (NOW() - INTERVAL 7 DAY))) ,')') as '1to1<7d',
    CONCAT((SELECT SEC_TO_TIME(SUM(c.DURATION_MAX)) FROM v5.MAX_DURATION_BY_CALL_ID c WHERE c.COMPANY=o.COMPANY and (`CALL_DATE` < (NOW() - INTERVAL 7 DAY)) and (`CALL_DATE` >= (NOW() - INTERVAL 30 DAY))),' (',(SELECT COUNT(*) FROM v5.MAX_DURATION_BY_CALL_ID g WHERE g.COMPANY=o.COMPANY and (`CALL_DATE` < (NOW() - INTERVAL 7 DAY)) and (`CALL_DATE` >= (NOW() - INTERVAL 30 DAY))) ,')') as '7d < 1to1 < 30d',
    CONCAT((SELECT SEC_TO_TIME(SUM(d.DURATION_MAX)) FROM v5.MAX_DURATION_BY_CALL_ID d WHERE d.COMPANY=o.COMPANY and (`CALL_DATE` < (NOW() - INTERVAL 30 DAY)) ),' (',(SELECT COUNT(*) FROM v5.MAX_DURATION_BY_CALL_ID h WHERE h.COMPANY=o.COMPANY and (`CALL_DATE` < (NOW() - INTERVAL 30 DAY))) ,')') as '1to1>30d',
    INTERVAL 7 DAY)) and (`CALL_DATE` >= (NOW() - INTERVAL 30 DAY))) as y,
    /*CONCAT(*/(SELECT SEC_TO_TIME(SUM(m.DURATION_MAX)) FROM v5.MAX_DURATION_BY_CONF_ID m WHERE m.COMPANY=o.COMPANY)/*,' (', (SELECT COUNT(*) FROM v5.MAX_DURATION_BY_CONF_ID m WHERE m.COMPANY=o.COMPANY),')')*/ as 'Total call Conf',
    SEC_TO_TIME((SELECT SUM(m.DURATION_MAX) FROM v5.MAX_DURATION_BY_CONF_ID m WHERE m.COMPANY=o.COMPANY)/(SELECT COUNT(*) FROM v5.MAX_DURATION_BY_CONF_ID m WHERE m.COMPANY=o.COMPANY)) as 'Avg Conf duration',
    CONCAT((SELECT SEC_TO_TIME(SUM(n.DURATION_MAX)) FROM v5.MAX_DURATION_BY_CONF_ID n WHERE n.COMPANY=o.COMPANY and (`CONF_DATE` > (NOW() - INTERVAL 7 DAY)) ),' (',(SELECT COUNT(*) FROM v5.MAX_DURATION_BY_CONF_ID n WHERE n.COMPANY=o.COMPANY and (`CONF_DATE` > (NOW() - INTERVAL 7 DAY))) ,')') as 'Conf<7d',
    CONCAT((SELECT SEC_TO_TIME(SUM(p.DURATION_MAX)) FROM v5.MAX_DURATION_BY_CONF_ID p WHERE p.COMPANY=o.COMPANY and (`CALL_DATE` < (NOW() - INTERVAL 7 DAY)) and (`CALL_DATE` >= (NOW() - INTERVAL 30 DAY)) ),' (',(SELECT COUNT(*) FROM v5.MAX_DURATION_BY_CONF_ID p WHERE p.COMPANY=o.COMPANY and (`CONF_DATE` < (NOW() - INTERVAL 7 DAY)) and (`CONF_DATE` >= (NOW() - INTERVAL 30 DAY))) ,')') as '7d < Conf < 30d',
    CONCAT((SELECT SEC_TO_TIME(SUM(q.DURATION_MAX)) FROM v5.MAX_DURATION_BY_CONF_ID q WHERE q.COMPANY=o.COMPANY and (`CONF_DATE` < (NOW() - INTERVAL 30 DAY)) ),' (',(SELECT COUNT(*) FROM v5.MAX_DURATION_BY_CONF_ID q WHERE q.COMPANY=o.COMPANY and (`CONF_DATE` < (NOW() - INTERVAL 30 DAY))) ,')') as 'Conf>30d' FROM
    v5.MAX_DURATION_BY_CALL_ID o
    GROUP BY o.COMPANY;

    Posted 1 week ago #
  2. myDBR Team, Key Master

    Well,
    probably before that you might want to optimize the query a bit. You have +20 subqueries in a query which basically calculates the number or 1to1 and conf calls and the duration of those. The performance of the query might be quite poor when you have more data in the tables.

    Another thing is that you are converting the times into time via SEC_TO_TIME-function. The max time data is 838:59:59, so you might run over the max time. myDBR supports larger times, just use seconds and colstyle formatting 'HH:MM:SS'.

    Would suggest that you first to the calculation to two temp tables (1to1 and conf) and do the calculations in one query per table. You can use conditional sum's to do the date range calculations. You can then sum up all the data as a result. This will reduce the number of queries to 3-4 and greatly enhance the performance.

    As for the Ajax page refresh, you could use 'embedded_report_xx' target for a linked report (the content of your report), so the date picker for date parameters will be included in the report and you the refresh is done via Ajax. You can also ask the dates via popup if you like. See demo.

    --
    myDBR Team

    Posted 1 week ago #

Reply

You must log in to post.