hide extra columns

(5 posts) (2 voices)

Tags:

No tags yet.

  1. thang, Member

    Hello,

    I have issues with "hide extra columns" as described in here: https://mydbr.com/doc/?cmd.appearance.html, the title column does not collapse as in the following images:

    https://drive.google.com/file/d/1DnGSBmwHFULnaFu-hjkThfBxkeNdxvzE/view?usp=sharing
    https://drive.google.com/file/d/10FykaW4ydPCyGLh2C-Ey7D6NofEAw-kk/view?usp=sharing

    I'm using the latest version 5.7.6 (build 4015).

    Thanks.

  2. myDBR Team, Key Master

    Can you show the report’s source code?


    myDBR Team

  3. thang, Member

    Please check here:


    DROP PROCEDURE IF EXISTS sp_DBR_report_expense1
    $$
    CREATE PROCEDURE `sp_DBR_report_expense1`()
    BEGIN -- Define report title
    select 'dbr.title', 'EXPENSE REPORT';
    select 'dbr.subtitle', 'Select to Filter'; -- Hide the toggle icon, we'll use buttons instead
    select 'dbr.css', '.toggle_img {display:none}'; -- select 'dbr.purehtml', '<input type="button" value="Hide/show detail rows" onclick="$(''.rowbutton'')[0].click();">';
    select 'dbr.purehtml', '<input type="button" value="Hide/show extra columns" onclick="$(''.colbutton'')[0].click();">'; select 'dbr.javascript', 'function myfunc(checkbox) { alert("Callback"); }';
    -- select 'dbr.toggle','[
    -- {
    -- "name":"Show/Hide rows",
    -- "class":"rowbutton",
    -- "resultclass":"toggledrows",
    -- "callback": "myfunc"
    -- "rows":["Kinh doanh"]
    -- }]
    -- ';
    select 'dbr.toggle','[
    {
    "name":"Show/Hide columns",
    "class":"colbutton",
    "cols":["budget_value", "actual_vs_budget", "percent", "class"]
    }]
    '; -- Define report format
    select 'dbr.headerstyle', 'background-color: #F7F2E0;';
    select 'dbr.footerstyle', 'background-color: #F7F2E0;font-weight: normal;color:#0101DF;'; -- Define cross tab
    select 'dbr.crosstab', 'Month'; -- Define column filters
    select 'dbr.column.filter', 'group_expense', 'select';
    select 'dbr.column.filter', 'exp_layer2', 'text'; -- Define column totals & calculations
    select 'dbr.summary.text', 'group_expense', 'Total'; -- define total row title
    select 'dbr.hdr', 'group_expense'; -- define group by a column (like pivot table) select 'dbr.sum', 'actual_value','budget_value'; -- define total on each column
    select 'dbr.hsum', 'actual_value','budget_value'; -- define total on total column select 'dbr.calc', 'actual_vs_budget', '[actual_value]/[budget_value]*100'; -- define calculations for a new column
    select 'dbr.hnull', 'actual_vs_budget'; -- define calculations for a new column (for total column)
    select 'dbr.colstyle', 'actual_vs_budget', '%.0f %'; select 'dbr.calc', 'percent', '[actual_value]/[actual_value.sum]*100'; -- define calculations for a new column
    select 'dbr.hnull', 'percent'; -- define calculations for a new column (for total column)
    select 'dbr.colstyle', 'percent', '[color:#888]%.2f %'; SELECT 'dbr.colstyle', 'actual_value', mydbr_style('column color'); -- Define the styles
    /*
    Color red cells whose value is greater than 100. Other cells will be green
    */
    select 'dbr.css', '.redclass {color:red;} .greenclass {color:green;}';
    select 'dbr.hidecolumns', 'class';
    select 'dbr.cellclass', 'actual_vs_budget', 'class'; -- Lock/Freeze left columns
    select 'dbr.lockcolumns', 'exp_layer2'; -- Query starts here
    select map_explayer1 AS 'Group Expense[group_expense]',
    map_explayer2 AS 'Expense Details[exp_layer2]',
    CONCAT(`Year`,'/',`Month`) as Month,
    SUM(IF(`Actual_Budget` = 'Actual',`Amount in Local Currency`,0)) as 'Actual[actual_value]',
    SUM(IF(`Actual_Budget` = 'Budget',`Amount in Local Currency`,0)) as 'Budget[budget_value]',
    null as '%Budget[actual_vs_budget]',
    null as '%[percent]',
    if ('actual_vs_budget' > 100,'redclass','greenclass') as 'class'
    from rpt_gl.gl_all
    where
    map_explayer1 IS NOT NULL AND
    `G/L Account` like '64%'
    group by map_explayer1, map_explayer2, CONCAT(`Year`,'/',`Month`)
    order by map_explayer1 ASC, map_explayer2 ASC, CONCAT(`Year`,'/',`Month`) ASC; END
    $$

  4. myDBR Team, Key Master

    Hi,
    the toggle works now with column filter. Thanks for reporting this.

    Btw, the column

    if ('actual_vs_budget' > 100,'redclass','greenclass') as 'class'

    is not right as the string 'actual_vs_budget' is never greater than number 100. You can replace that with

    select 'dbr.calc', 'class', '[actual_value]/[budget_value]>1 ? "redclass" : "greenclass"'; 

    --
    myDBR Team

  5. thang, Member

    Great, the issue is fixed after upgrading.

    Thanks very much for your prompt feedback.


Reply

You must log in to post.