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.