1. 

   rbecker, Member

   Hello,
   I have a crosstab report where I am pulling contact attempt counts of a list of customers and displaying them in order of the contact list they came from. I want to make it so where any attempt count over 25 gets rolled into a single column called "Over 25" or something similar. I was able to pull this manually by running a second SELECT but it caused the result to appear below the crosstab as opposed to another column on the end of it. Is there a way to have this column appended or perhaps use some calculation function which grabs all columns over 25 and adds them up? Report code is below.

   DROP PROCEDURE IF EXISTS sp_DBR_CalledCountsCampaign
   
   $$
   
   CREATE PROCEDURE `sp_DBR_CalledCountsCampaign`()
   
   BEGIN
   
   SELECT 'dbr.hdr', '[list]';
   
   SELECT 'dbr.sum', '[lcount]';
   
   SELECT 'dbr.summary.options', 'limit_summary_level', '1';
   
   SELECT 'dbr.summary.text', '[list]', 'Totals';
   
   SELECT 'dbr.crosstab', '[ccount]';
   
   SELECT CONCAT(v.list_id, " - ", asterisk.vicidial_lists.list_name) as 'List[list]',
   
   v.called_count as 'Called Count[ccount]',
   
   count(*) as '[lcount]'
   
   FROM asterisk.vicidial_list v
   
   LEFT JOIN asterisk.vicidial_lists ON v.list_id = asterisk.vicidial_lists.list_id
   
   WHERE v.list_id = '1003'
   
   GROUP BY v.list_id, v.called_count
   
   ORDER BY v.called_count asc;
   
   END
   
   $$
2. 

   myDBR Team, Key Master

   Hi,
   how would the expected result look like?

   Btw, you can leave out the square brackets in the commands where myDBR knows it will be a column reference and the `limit_summary_level` option is unnecessary when you only have one header level:

   SELECT 'dbr.hdr', 'list';
   
   SELECT 'dbr.sum', 'lcount';
   
   SELECT 'dbr.summary.text', 'list', 'Totals';
   
   SELECT 'dbr.crosstab', 'ccount';
   
   

   --
   myDBR Team
3. 

   rbecker, Member

   I have the summary options in there as I will eventually be expanding this report but I'm testing with only a single list now. If I use a second SELECT statement the results appear down below the existing crosstab, but I would like an extra column added to the end, like the red box shows.

   
4. 

   myDBR Team, Key Master

   What you are trying to do, is to treat all rows that have the v.called_count over 25 as one group. You can do:

   SELECT
   
     CONCAT(v.list_id, " - ", asterisk.vicidial_lists.list_name) as 'List[list]',
   
     if (v.called_count>25, 'Over 25', v.called_count) as 'Called Count[ccount]',
   
     count(*) as '[lcount]'
   
   FROM asterisk.vicidial_list v
   
     LEFT JOIN asterisk.vicidial_lists ON v.list_id = asterisk.vicidial_lists.list_id
   
   WHERE v.list_id = '1003'
   
   GROUP BY v.list_id, if (v.called_count>25, 'Over 25', v.called_count)
   
   ORDER BY v.called_count asc;
   
   

   --
   myDBR Team
5. 

   rbecker, Member

   That worked perfectly, thank you. Is there also a way to have myDBR calculate the total value for each list and place it at the end? dbr.sum doesn't quite seem to do it correctly.
6. 

   myDBR Team, Key Master

   The dbr.sum calculates a sum for the column (will do it correctly). If you want to calculate the horizontal sum for the crosstab data column, use dbr.hsum. See the documentation.

   Btw, you do not need to use the dbr.hdr command either, as you only have a single header column (no need to header have breakpoints for a single value).

   --
   myDBR Team

Reply

You must log in to post.