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;
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

Reply

You must log in to post.