1. 

   vannc, Member

   Dear MyDBR's Support!

   I would like to build a dashboard like this. Is it possible to build? If yes, how can I build it?
   https://image-3.gapowork.vn/images/725bb869-ca28-43b3-8f78-d7538a4d48b0/image.jpeg

   Thanks!
   Best regards:
   Van
2. 

   myDBR Team, Key Master

   Yes, you can place the objects into desired layout.

   Use a template to place the myDBR report objects to the layout you wish to use. In the example you have simple columns at top, a crosstabs second and then charts at the bottom.

   --
   myDBR Team
3. 

   vannc, Member

   Thank you!

   And please kindly add code to this report.
   https://mydbr.com/demo/mydbr/report.php?r=210&m=1&h=3a37d0042688382a4a02234e852b9b809322aaba&i=1
   Thanks!

   Best regards:
   Van
4. 

   myDBR Team, Key Master

   See the documentation about templates and recorded variables.

   --
   myDBR Team
5. 

   vannc, Member

   I don't know why showing title, I do not set this text in code, please kindly help me!

   https://drive.google.com/file/d/1YdnEexN2ekU07IZ_FmlMXy-7t9W7ijoR/view?usp=sharing

   This is my code:
   

   if object_id('sp_DBR_report_financial_ratio','P') is not null
   
   drop procedure sp_DBR_report_financial_ratio
   
   go
   
   CREATE PROCEDURE sp_DBR_report_financial_ratio
   
   AS
   
   BEGIN
   
       select 'dbr.css', '
   
       body { background: #efefef}
   
       .dashboard {
   
           display: flex;
   
           flex-wrap: wrap;
   
           justify-content: flex-start;
   
           justify-content: center;
   
           margin-top: 20px;
   
       }
   
       .bi_box { text-align: left; margin-left: 10px; margin-top: 10px; box-shadow: 2px 2px 3px 0px #CCC; }
   
       .bi_box.narrow, .bi_box .bi_data.narrow { width: 220px;  }
   
       .bi_header, .bi_data { background: white; border: 1px solid #CCC; padding: 10px;  }
   
       .bi_data { display: table-cell; vertical-align: bottom; }
   
       .bi_header1 { color: black; font-weight: bold; font-size: 1.3em}
   
       .bi_header2 { color: gray; padding-top: 3px }
   
       .bi_box .small { height: 100px }
   
       .bi_box .text { font-size: 40px; text-align: center;line-height: 80px }
   
       --.bi_box .rstable {margin-bottom: 0px;}
   
       --.bi_green_data { font-size: 24px; color: green; text-align: right; line-height: 16px}
   
       --.bi_green.no { display:none}
   
       ';
   
       -- Wrap the dashboard elements in a flex-div
   
       select 'dbr.html', '<div class="dashboard">';
   
       select 'dbr.template', '#bi_box';
   
       select 'dbr.colstyle', 'bi_content', '%.2f%';
   
       select
   
         b.ROA*100 as 'bi_content',
   
         'narrow' as 'bi_width',
   
         'ROA' as 'bi_header1',
   
         'This year' as 'bi_header2',
   
         'small text' as 'bi_dheight',
   
         'yes' as 'bi_show_green'
   
       from cdc_KT1S_Release_App_2024..total_index_report b;
   
       select 'dbr.template', '#bi_box';
   
       select 'dbr.colstyle', 'bi_content', '%.2f%';
   
       select
   
         b.ROE*100 as 'bi_content',
   
         'narrow' as 'bi_width',
   
         'ROE' as 'bi_header1',
   
         'This year' as 'bi_header2',
   
         'small text' as 'bi_dheight',
   
         'yes' as 'bi_show_green'
   
       from cdc_KT1S_Release_App_2024..total_index_report b;
   
       select 'dbr.html', '</div>';
   
   END
   
   go

   Thanks team!
   Best regards:
   Van
6. 

   myDBR Team, Key Master

   The title is shown by default. Disable it with following:

   select 'dbr.title', '';

   --
   myDBR Team
7. 

   vannc, Member

   I am trying to build a beautiful dashboard, but it looks awful. The numbers are too large, making them hard to read. I don't know how to extend the x-axis, and the line is off.

   This is my current dashboard: current dashboard
   This is the dashboard I envision: dream dashboard. Two big dashboards in one report.
   I also haven't filled out the first big dashboard yet.

   Thank you very much!
   This is my code:

   if object_id('sp_DBR_report_financial_ratio','P') is not null
   
   drop procedure sp_DBR_report_financial_ratio
   
   go
   
   CREATE PROCEDURE sp_DBR_report_financial_ratio
   
   AS
   
   BEGIN
   
       select 'dbr.css', '
   
       body { background: #efefef}
   
       .dashboard {
   
           display: flex;
   
           flex-direction: column;
   
           justify-content: flex-start;
   
           align-items: center;
   
           margin-top: 3px;
   
       }
   
       .bi_box {
   
           margin: 10px 0; /* Adjust margin for vertical spacing */
   
       }
   
       .bi_box { text-align: left; margin-left: 5px; margin-top: 5px; box-shadow: 2px 2px 3px 0px #CCC; }
   
       .bi_box.narrow, .bi_box .bi_data.narrow { width: 110px;  }
   
       .bi_header, .bi_data { background: white; border: 1px solid #CCC; padding: 5px;  }
   
       .bi_data { display: table-cell; vertical-align: bottom; }
   
       .bi_header1 { color: black; font-weight: bold; font-size: 1.3em}
   
       .bi_header2 { color: gray; padding-top: 3px }
   
       .bi_box .small { height: 25px }
   
       .bi_box .text { font-size: 10px; text-align: center;line-height: 20px }
   
       --.bi_box .rstable {margin-bottom: 0px;}
   
       --.bi_green_data { font-size: 24px; color: green; text-align: right; line-height: 16px}
   
       --.bi_green.no { display:none}
   
       ';
   
       -- Wrap the dashboard elements in a flex-div
   
       select 'dbr.html', '<table align="center"><tr><td>';
   
       select 'dbr.html', '<div class="dashboard">';
   
       select 'dbr.title', '';
   
       select 'dbr.template', '#bi_box';
   
       select 'dbr.colstyle', 'bi_content', '%.2f%';
   
       select
   
         b.ROA*100 as 'bi_content',
   
         'narrow' as 'bi_width',
   
         'ROA' as 'bi_header1',
   
         'This year' as 'bi_header2',
   
         'small text' as 'bi_dheight',
   
         'yes' as 'bi_show_green'
   
       from cdc_KT1S_Release_App_2024..total_index_report b;
   
       select 'dbr.template', '#bi_box';
   
       select 'dbr.colstyle', 'bi_content', '%.2f%';
   
       select
   
         b.ROE*100 as 'bi_content',
   
         'narrow' as 'bi_width',
   
         'ROE' as 'bi_header1',
   
         'This year' as 'bi_header2',
   
         'small text' as 'bi_dheight',
   
         'yes' as 'bi_show_green'
   
       from cdc_KT1S_Release_App_2024..total_index_report b;
   
       select 'dbr.template', '#bi_box';
   
       select 'dbr.colstyle', 'bi_content', '%.2f%';
   
       select
   
         b.ROIC*100 as 'bi_content',
   
         'narrow' as 'bi_width',
   
         'ROIC' as 'bi_header1',
   
         'This year' as 'bi_header2',
   
         'small text' as 'bi_dheight',
   
         'yes' as 'bi_show_green'
   
       from cdc_KT1S_Release_App_2024..total_index_report b;
   
       select 'dbr.template', '#bi_box';
   
       select 'dbr.colstyle', 'bi_content', '%.2f%';
   
       select
   
         b.ROCE*100 as 'bi_content',
   
         'narrow' as 'bi_width',
   
         'ROCE' as 'bi_header1',
   
         'This year' as 'bi_header2',
   
         'small text' as 'bi_dheight',
   
         'yes' as 'bi_show_green'
   
       from cdc_KT1S_Release_App_2024..total_index_report b;
   
       select 'dbr.template', '#bi_box';
   
       select 'dbr.colstyle', 'bi_content', '%.2f%';
   
       select
   
         b.LR*100 as 'bi_content',
   
         'narrow' as 'bi_width',
   
         'LR' as 'bi_header1',
   
         'This year' as 'bi_header2',
   
         'small text' as 'bi_dheight',
   
         'yes' as 'bi_show_green'
   
       from cdc_KT1S_Release_App_2024..total_index_report b;
   
       select 'dbr.template', '#bi_box';
   
       select 'dbr.colstyle', 'bi_content', '%.2f%';
   
       select
   
         b.LG*100 as 'bi_content',
   
         'narrow' as 'bi_width',
   
         'LG' as 'bi_header1',
   
         'This year' as 'bi_header2',
   
         'small text' as 'bi_dheight',
   
         'yes' as 'bi_show_green'
   
       from cdc_KT1S_Release_App_2024..total_index_report b;
   
       select 'dbr.template', '#bi_box';
   
       select 'dbr.colstyle', 'bi_content', '%.2f%';
   
       select
   
         b.EBIT*100 as 'bi_content',
   
         'narrow' as 'bi_width',
   
         'EBIT' as 'bi_header1',
   
         'This year' as 'bi_header2',
   
         'small text' as 'bi_dheight',
   
         'yes' as 'bi_show_green'
   
       from cdc_KT1S_Release_App_2024..total_index_report b;
   
       ----------------------------------
   
       select 'dbr.html', '</td><td>';
   
       SELECT 'dbr.css', '
   
       td.cell { white-space:nowrap; padding-left: 2px; padding-right: 5px; }
   
       td.prev_year, td.prev_year a { color: #777 !important; }
   
       th.TableHdrTableData {padding-left: 2px; padding-right: 2px}
   
       td.cell a {text-decoration:none}
   
       td.this_year a { color: #444 }
   
       .whitebg {background-color:white !important; border-bottom: 1px solid #DDDDDD !important}
   
       td.group_allocation {color: #666;}
   
       .toggledrows .row, .toggledrows .row.odd, {background-color:white;}
   
       .toggledrows tr.acc_header td {border-bottom:none;background-color:white;}
   
       .pl_report_v8 .toggle_img {display:none}
   
       .pl_report_v8 tr {height: 31px}
   
       .acc_header {font-weight: 500;background: #F4F4F4;}
   
       .acc_header td {border-top: 1px solid #CCC;border-bottom: 1px solid #CCC;}
   
       .acc_header td {border-top: 1px solid #CCC;border-bottom: 1px solid #CCC;}
   
       tr.acc_header td {border-bottom: 1px solid #666;}
   
       --.account td {padding-left: 2em !important}
   
       --tr.account td.padded_account {padding-left: 30px; background-color: white;}
   
       --tr.account {background-color: white;}
   
       .level1 td:first-child {
   
           color: #006600; /* Màu xanh lá cây cho level1 */
   
           padding-left: 2em;
   
       }
   
       .level2 td:first-child {
   
           color: #663300; /* Màu nâu đất cho level2 */
   
           padding-left: 4em;
   
       }
   
       .level3 td:first-child {
   
           color: #330099; /* Màu xanh đậm cho level3 */
   
           padding-left: 6em;
   
       }
   
       .level4 td:first-child {
   
           color: #990033; /* Màu đỏ tối cho level4 */
   
           padding-left: 8em;
   
       }
   
       .spark {padding:0}
   
       ';
   
       SELECT 'dbr.search', '1';
   
       --select 'dbr.toggle.options', 'speed', 700;
   
       SELECT 'dbr.resultclass', 'bl_total_index';
   
       SELECT 'dbr.crosstab', 'quater';
   
       SELECT 'dbr.colclass', 'account', 'padded_account border_left freeze';
   
       select 'dbr.colclass', 'Sparkline', 'spark';
   
       SELECT 'dbr.colclass', 'ty_ga', 'border_left border_right this_year group_allocation';
   
       SELECT 'dbr.colclass', 'ty', 'this_year';
   
       SELECT 'dbr.header.colclass', 'ty_ga', 'group_allocation';
   
       SELECT 'dbr.colstyle', 'ty_ga', '%.0f; ;%.0f';
   
       SELECT 'dbr.hidecolumn', 'ty_ga';
   
       SELECT 'dbr.lockcolumns', 'account';
   
       select 'dbr.css', '.text_green {color:green}';
   
       select 'dbr.css', '.text_red {color:red}';
   
       --------------------------------------------------
   
       --select 'dbr.cellformat', 'ty', 'd_format';
   
       --select 'dbr.cellformat', 'Plan', 'e_format';
   
       --select 'dbr.cellformat', 'MoM', 'f_format';
   
       --select 'dbr.cellformat', 'ly', 'g_format';
   
       --------------------------------------------------
   
       select 'dbr.summary.text', 'Total';
   
       select 'dbr.hsum', 'ty';
   
       -- Tạo bảng mới
   
       CREATE TABLE #pl_sparkline_temple (
   
           common_col nvarchar(20),
   
           actual DECIMAL(38,2),
   
           quater_value nvarchar(20)
   
       );
   
       --CREATE INDEX idx_common_col ON #pl_sparkline_temple(common_col);
   
       -- Insert dữ liệu vào bảng mới
   
       INSERT INTO #pl_sparkline_temple (common_col, actual, quater_value)
   
       SELECT b.code as 'common_col',
   
       abs(sum(b.result)) as 'actual',
   
       b.quater AS 'quater_value'
   
       FROM cdc_KT1S_Release_App_2024..bl_total_index b
   
       where b.month is not null
   
       GROUP BY
   
         b.quater,
   
         b.code
   
       ORDER BY
   
         b.quater
   
       ------------------------------------------------------------
   
       -- Tạo bảng mới
   
       CREATE TABLE #pl_sparkline_temple2 (
   
         common_col nvarchar(20),
   
         Actuals NVARCHAR(MAX)
   
       )
   
       --CREATE INDEX idx_common_col2 ON #pl_sparkline_temple2(common_col);
   
       INSERT INTO #pl_sparkline_temple2 (common_col, Actuals)
   
       SELECT DISTINCT
   
           common_col,
   
           STUFF((
   
       SELECT ',' + CAST(Actual AS NVARCHAR(MAX))
   
               FROM #pl_sparkline_temple AS t2
   
               WHERE t1.common_col = t2.common_col
   
               ORDER BY t2.quater_value
   
               FOR XML PATH('')
   
           ), 1, 1, '') AS Actuals
   
       FROM #pl_sparkline_temple AS t1;
   
       ------------------------------------------------------------
   
       --select * from #pl_sparkline_temple2;
   
       ------------------------------------------------------------
   
       SELECT
   
       b.description AS '[account]',
   
       temp.Actuals as 'Sparkline',
   
       b.quater as 'quater',
   
       3 AS 'GA[ty_ga]',
   
       -- ACTUAL
   
       SUM(b.result) AS 'Actual[ty]'
   
       FROM cdc_KT1S_Release_App_2024..bl_total_index b
   
       JOIN #pl_sparkline_temple2 temp
   
           ON b.code = temp.common_col
   
       WHERE b.month IS NOT NULL
   
       GROUP BY
   
           b.description,
   
           b.quater,
   
           temp.Actuals
   
       ORDER BY b.quater;
   
       select 'dbr.javascript', "$('.spark').sparkline('html', { type: 'line', height: '30px', width: '132px' });", 'onload';
   
       -------------------------------------
   
       SELECT 'dbr.chart', 'MScolumn', 'Multi Series 2D Column';
   
       SELECT 'dbr.chart.options', 'padding_right', '20';
   
       SELECT 'dbr.keepwithnext';
   
       SELECT b.quater, b.description, SUM(b.result/1000000)
   
       FROM cdc_KT1S_Release_App_2024..bl_total_index b
   
       WHERE b.month IS NOT NULL
   
       GROUP BY
   
           b.description,
   
           b.quater
   
       ORDER BY b.quater;
   
       -------------------------------------
   
       select 'dbr.html', '</div>';
   
       select 'dbr.html', '</td></tr></table>';
   
   END
   
   go
8. 

   myDBR Team, Key Master

   If you want the numbers to be smaller in an object, use CSS to adjust their size.

   You have not defined the chart size, so myDBR uses the default size and layout that fits both the chart and the legend. Your legends are quite wide because they contain the numbers (e.g., 100=110+120+130+140+150, etc.). I'm not sure what you're referring to by 'the line is off.'

   You can also use a template that includes all your elements. This way, it will be easier to position the elements exactly as you want.

   --
   myDBR Team

Reply

You must log in to post.