1. 

   astroshark, Member

   Hello,
   I was wondering is there a way to hide only the dbr.calc percentage summary and keep the dbr.sum summary in a report?
2. 

   myDBR Team, Key Master

   Can you show your query what are you trying to do? What columns do you have and how you use dbr.calc and dbr.sum?

   --
   myDBR Team
3. 

   astroshark, Member

   Below is the main portion of my query, my goal is to hide the summary % of [pct] and [tpct] and only show the summary sum value of [cl] and [s]

   
   
   SELECT 'dbr.crosstab', '[dt]';
   
   SELECT 'dbr.sum', '[cl]', '[s]';
   
   SELECT 'dbr.hsum', '[cl]', '[s]';
   
   SELECT 'dbr.calc', '[pct]', '[cl]/[s]*100';
   
   SELECT 'dbr.hnull', '[tpct]';
   
   SELECT 'dbr.calc', '[tpct]', '[cl]/[s2]*100';
   
   SELECT 'dbr.hidecolumn.data', '[tpct]';
   
   SELECT 'dbr.colstyle', '[pct]', '%.2f%;';
   
   SELECT 'dbr.colstyle', '[tpct]', '%.2f%;';
   
   SELECT 'dbr.summary.text', '[ag]','Totals';
   
   SELECT c.agentAS 'Agent[ag]',
   
          cls.descr AS 'Status[st]',
   
          LEFT(c.row_insert_dt,7) AS 'Date[dt]',
   
          SUM(1) AS 'Clients[cl]',
   
          NULL AS '%[pct]',
   
          NULL AS '%[tpct]',
   
          c2.svalue AS '[s]',
   
          c3.svalue AS '[s2]'
   
   FROM   Table c
   
   WHERE  DATE(c.row_insert_dt) BETWEEN StartDate AND EndDate
   
   GROUP BY 1,2,3
   
   ORDER BY 1,2,3 DESC;
   
   
4. 

   myDBR Team, Key Master

   You can limit the level for which the calculation is done.

   
   
   SELECT 'dbr.calc', '[pct]', '[cl]/[s]*100', '', '<0';
   
   SELECT 'dbr.calc', '[tpct]', '[cl]/[s2]*100', '', '<0';
   
   

   --
   myDBR Team
5. 

   astroshark, Member

   So after adding more metrics to this report turns out I need to have the % show, but that tip is great to know for future reports *I was a little confused by the documentation, but then again I wasn't reading it word for word*. But with that said I was able to get the horizontal % and the subtotal % to be correct but the total % is just summing up the numbers and adding a % sign to the end of it.

   
   
       SELECT 'dbr.crosstab', '[dt]';
   
       SELECT 'dbr.sum', '[cl]', '[s]', '[1pmt]', '[1success]';
   
       SELECT 'dbr.hsum', '[cl]', '[s]', '[1pmt]', '[1success]';
   
       SELECT 'dbr.calc', '[pct]', '[cl]/[s]*100';
   
       SELECT 'dbr.calc', '[1pmt_pct]', '[1pmt]/[s]*100';
   
       SELECT 'dbr.calc', '[1success_pct]', '[1success]/[s]*100';
   
       SELECT 'dbr.hnull', '[tpct]','[1pmt_tpct]','[1success_tpct]';
   
       SELECT 'dbr.calc', '[tpct]', '[cl]/[s]*100';
   
       SELECT 'dbr.calc', '[1pmt_tpct]', '[1pmt]/[s]*100';
   
       SELECT 'dbr.calc', '[1success_tpct]', '[1success]/[s]*100';
   
       SELECT 'dbr.hidecolumn.data', '[tpct]';
   
       SELECT 'dbr.hidecolumn.data', '[1pmt_tpct]';
   
       SELECT 'dbr.hidecolumn.data', '[1success_tpct]';
   
       SELECT 'dbr.colstyle', '[pct]', '%.2f%;';
   
       SELECT 'dbr.colstyle', '[1pmt_pct]', '%.2f%;';
   
       SELECT 'dbr.colstyle', '[1success_pct]', '%.2f%;';
   
       SELECT 'dbr.hdr', '[ag]';
   
       SELECT 'dbr.max', '[s]';
   
       SELECT 'dbr.hidecolumn', '[s]';
   
       SELECT 'dbr.lockcolumns', '[st]';
   
       SELECT      c.rep AS 'Agent[ag]',
   
                   cls.descr AS 'Status[st]',
   
                   LEFT(c.row_insert_dt,7) AS 'Date[dt]',
   
                   SUM(1) AS '# of Clients[cl]',
   
                   COUNT(cj.client) AS '[1success]',
   
                   SUM(1) - COUNT(cj.client) AS '[1pmt]',
   
                   NULL AS '% of Clients[pct]',
   
                   NULL AS '% of Clients[tpct]',
   
                   NULL AS '% 1st Pmt Made[1success_pct]',
   
                   NULL AS '% 1st Pmt Made[1success_tpct]',
   
                   NULL AS '% No 1st Draft[1pmt_pct]',
   
                   NULL AS '% No 1st Draft[1pmt_tpct]',
   
                   c2.svalue AS '[s]'
   
       FROM        table c
   
       JOIN        table2 cls ON cls.fieldname = 'client_status' AND cls.value = c.client_status
   
       JOIN        (SELECT c.rep,
   
                           LEFT(c.row_insert_dt,7) AS row_insert_dt,
   
                           SUM(1) AS svalue
   
                    FROM   table c
   
                    WHERE  DATE(c.row_insert_dt) BETWEEN vStartDate AND vEndDate
   
                    GROUP BY 1, LEFT(c.row_insert_dt,7)
   
                   ) c2 ON c2.rep = c.rep AND c2.row_insert_dt = LEFT(c.row_insert_dt,7)
   
       WHERE       DATE(c.row_insert_dt) BETWEEN vStartDate AND vEndDate AND
   
                   c.rep IN (SELECT rep FROM reporting.rep_picker) AND
   
                   cls.notes NOT IN ('Non-Client','Transferred','Products')
   
       GROUP BY 1,2,3
   
       ORDER BY 1,2,3 DESC;
   
   

   The image below shows a sample result the first blue line, the subtotal, is correct. The second blue line, the total, is not correct and instead is summing up each number to itself to come up with 144,34, and 110 and adding the % sign to it. Any idea on what is causing this?

   
6. 

   myDBR Team, Key Master

   Just make sure you are using the latest version.

   --
   myDBR Team

Reply

You must log in to post.