Hiding Only dbr.calc summary

(6 posts) (2 voices)
  • Started 3 weeks ago by astroshark
  • Latest reply from myDBR Team

Tags:

  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?

    Posted 3 weeks ago #
  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

    Posted 3 weeks ago #
  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;

    Posted 3 weeks ago #
  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

    Posted 3 weeks ago #
  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?

    Posted 3 weeks ago #
  6. myDBR Team, Key Master

    Just make sure you are using the latest version.

    --
    myDBR Team

    Posted 3 weeks ago #

Reply

You must log in to post.