Profit and Loss Statement

(16 posts) (2 voices)


No tags yet.

  1. vannc, Member

    Dear MyDBR Team!

    Could you please share the source code and the process of creating the report?
    Profit and Loss Statement: January - February

    Thanks team.

  2. myDBR Team, Key Master

    The 'Show how this report is done' button has been added to the report. Note that to procude a live P&L statement, you need to do some calculations for the account header level sums/percentages. The report in question show a sample formatting.

    myDBR Team

  3. vannc, Member

    Thank team very much!
    Best regards!

  4. vannc, Member

    Dear MyDBR Team,
    I would like to inquire whether the total in the report is calculated directly from the database or using a default function. If it is calculated from the database, could you please guide me on how to utilize the default function 'dbr.summary.calc'?

    Thanks Team!

  5. myDBR Team, Key Master

    Each P&L statement is different, meaning different GL account rows and summary rows depend on your P&L structure and the data available to you.

    There are multiple ways of performing the actual calculations. One option is to define the account and summary rows in a temporary database table and perform the calculations based on your P&L definition. For example, you identify the rows with an ID and aggregate summaries over a range of rows, storing the summary rows in the temporary table.

    Once you have calculated the required rows, you can display the results as demonstrated in the sample report.

    myDBR Team

  6. vannc, Member

    Thank team very much!
    Best regards!

  7. vannc, Member

    Dear MyDBR Team,

    I have questions about the meanings of "ga", "ty_format", "row_class", and how can NET SALES, Gross margin (%), Total personnel costs, Total other operating costs, and EBITDA be decreased in indentation, while the child values are increased in indentation? Could you also provide the structure and data of the pl_data table?

    Thanks Team!

  8. myDBR Team, Key Master

    When you look at the P&L statement, you will see that there are different row types in it.

    • Account rows (Net Sales, Other Income, Cost of goods sold etc). These you usually get from your
    • Summary rows (NET SALES, Gross margin, Total personnel costs etc). These you calculate based on your P&L structure
    • Percentage rows (Gorss margin (%)=Gorss margin/NET SALES)

    The formatting is done via the CSS block in the beginning and with myDBR styling commands.

    In the example the account row names cells contain a CSS class that indents with definition:

    .account td {padding-left: 2em !important}

    You can see the end result for the styling used with the "Inspect" functionality with your browser.

    Check your email for the source code.

    myDBR Team

  9. vannc, Member

    Thanks team!

    Could I ask you why I have linked a report in this code, but it does not show the green arrow icon?
    if object_id('sp_DBR_test2','P') is not null
    drop procedure sp_DBR_test2

    select 'dbr.title', 'dbr.html:<span style="font-size:1.4em">Profit and Loss Statement: January - February<span>';

    select '';

    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;}
    .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}
    .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;

    select '', 0;
    select 'dbr.resultclass', 'pl_report';
    --select 'dbr.freezecols', 1;

    select 'dbr.hidecolumns', 'rowclass';

    select 'dbr.crosstab', 'Month';

    --select 'dbr.colclass', 'account', 'padded_account border_left';
    select 'dbr.colclass', 'account', 'padded_account border_left freeze';
    select 'dbr.lockcolumns', 'account';
    select 'dbr.colclass', 'ty_ga', 'border_left border_right this_year group_allocation';
    select 'dbr.colclass', 'ty', 'this_year';
    select 'dbr.colclass', 'ly', 'border_right prev_year';
    select 'dbr.colclass', 'chg', 'border_right chg';
    select 'dbr.header.colclass', 'ty_ga', 'group_allocation';
    select 'dbr.header.colclass', 'ly', 'prev_year';
    select 'dbr.header.colclass', 'chg', 'chg';
    select 'dbr.rowclass', 'rowclass';
    select 'dbr.lockcolumns', 'account';
    select 'dbr.summary.calc', 'chg', '[ly]==0 ? 0 : ([ty]-([ly]))/abs([ly])*100';

    select 'dbr.colstyle', 'ty_ga', '%.0f; ;%.0f';
    select 'dbr.colstyle', 'ty', '%.0f; ;%.0f';
    --select 'dbr.cellformat', 'ty', 'ty_format';
    --select 'dbr.cellformat', 'ly', 'ty_format';
    select 'dbr.colstyle', 'ly', '%.0f; ;%.0f';
    select 'dbr.colstyle', 'chg', '%.0f %; ;%.0f %';

    select 'dbr.nosort', 0;
    select 'dbr.hidecolumn', 'ty_ga';
    SELECT '', 'sp_DBR_pl_child', 'inAccountName=[account]';
    b.ten_ta AS account,
    b.month AS Month,
    3 AS 'GA[ty_ga]',
    b.no2024 AS 'Period[ty]',
    b.no2023 AS 'Year before[ly]',
    (b.no2024 - b.no2023) / b.no2023 * 100 AS 'Chg%[chg]',
    b.row_class AS 'rowclass'
    FROM release..baocao b
    WHERE b.month != NULL
    order by b.stt;


  10. myDBR Team, Key Master

    In order the linked report to show up, you need to:

    1. Create the linked report procdure
    2. Attach it as a report tnto myDBR
    3. User running the report needs to have permissions to the linked report

    Btw, in many times it is more logical fot the user that if you attach the linked report to the respective column instead of using the green arrow location. In this case the logical place would be the account as the linked report gives details on the accout. This does not affect on whether the linked report shows up or not, just where it does show up.

    myDBR Team

  11. vannc, Member

    Dear team!

    Oh, I see. Now the green arrow is showing. Could you provide a detailed code example for your comment: 'attach the linked report to the respective column'?

  12. myDBR Team, Key Master

    See "Linked report placement" in the documentation.

    myDBR Team

  13. vannc, Member

    Thanks team I have done! :D

  14. vannc, Member

    Dear Team,

    Can toggle do the same thing as collapse like this?
    [link to example:

    If toggle can, could you provide me with a custom function to handle it?
    If not, can toggle toggle something other than the 'account' value of row_class? because I need toggle too with "level1", "level2" value of row_class

  15. myDBR Team, Key Master

    The toggle and the collapse are two separate things which do their own functionality. See the documentation.

    myDBR Team

  16. vannc, Member

    Dear Team,

    I am sorry Team. I now understand the issue.

    Here is the corrected code:

    Copy code
    select 'dbr.toggle','[
    "name":"Show/Hide rows",
    I just need to change 'account' to 'level1', or add more rows for the toggle to work.

    Thanks, Team!


You must log in to post.