Accounting and control in production implies reporting: a selection of data with the ability to display it on the screen and / or print. The easiest way to obtain reports is to fill in the tabular presentation with the necessary information and then upload it to MS Excel for formatting and printing.

In My Visual Database, to generate simple reporting, we will use buttons configured to receive data via an SQL query (1) and upload data to MS Excel (2), as well as a form that will contain components for filtering data – report parameters (3).

Let’s take a closer look at how the transaction report works.

Movement report

Having singled out the operations we need from the general flow of material assets, we can determine what entered the warehouse for a certain period, what went into production, what was produced, and what was sold and for what amount, calculate the profit from the sale. The price is displayed as the receipt price. The movement of individual batches of material assets is displayed.

This report displays operations by the given parameters:

  • Transaction type
  • From
  • To
  • Period start
  • Period end

Displayed data:

  • Type of transaction
  • From whom
  • To whom
  • the date
  • vendor code
  • Name
  • Price (incoming)
  • Quantity
  • unit of measurement
  • Receipt amount
  • Amount of expense
  • Profit

Results:

  • Income Amount
  • Expense Amount
  • Profit

Getting data

Although the TdbButton button allows you to enter the text of the SQL query directly into the button settings window, we will leave the field with the SQL query empty, and configure only the display of the column names and specify the table to display the result. This is due to the fact that we will build the query text using a script, which will allow for additional optimization by adding only those filtering constructs that are needed for the selected report parameters.

To form the request text, the button click handler will be used:

procedure frmOperRep_btnUpdate_OnClick(Sender: TObject; var Cancel: boolean);
var
  tmpSQL:string;
begin
  tmpSQL :=
    ' SELECT '+CR+//
    'operTypeName, '+CR+//
    'contrFromName, '+CR+//
    'contrToName, '+CR+//
    ' StrFTime("%d.%m.%Y",docDate), '+CR+//
    'itemCode, '+CR+//
    'itemName, '+CR+//
    'price, '+CR+//
    'qty, '+CR+//
    ' unitCode, '+CR+//
    ' price*qty as DisAmount, '+CR+//
    ' amount, '+CR+//
    ' (amount - price*qty) as margin '+CR+//
    ' FROM '+CR+//
    ' ( SELECT '+CR+//
    'docType.name as operTypeName, '+CR+//
    ' cFrom.name as contrFromName, '+CR+//
    ' cTo.name as contrToName, '+CR+//
    'operDoc.docDate, '+CR+//
    ' item.code as itemCode, '+CR+//
    ' item.name as itemName, '+CR+//
    ' ( SELECT op.amount / op.qty '+CR+//
    ' FROM opera op '+CR+//
    'LEFT JOIN operDoc od ON od.id = op.id_operDoc '+CR+//
    'WHERE op.id_pitem = oper.id_pitem '+CR+//
    ' AND od.id_docType IN (1,6) '+CR+//
    'ORDER BY od.docDate '+CR+//
    ' LIMIT 1 '+CR+//
    ' ) as price, '+CR+//
    'oper.qty, '+CR+//
    ' unit.code as unitCode, '+CR+//
    'oper.amount '+CR+//
    ' FROM opera '+CR+//
    'LEFT JOIN operDoc ON operDoc.id = oper.id_operDoc '+CR+//
    'LEFT JOIN docType ON docType.id = operDoc.id_docType '+CR+//
    ' LEFT JOIN contr cFrom ON cFrom.id = operDoc.id_contr '+CR+//
    ' LEFT JOIN contr cTo ON cTo.id = operDoc.id_contr1 '+CR+//
    'LEFT JOIN item ON item.id = oper.id_pItem '+CR+//
    ' LEFT JOIN unit ON unit.id = item.id_unit '+
    ' WHERE (1 = 1) ';
    // if you need filtering by document type, then add it
    if frmOperRep.cmbOperType.dbItemID <> -1 then
    tmpSQL := tmpSQL+
      ' AND ( operDoc.id_docType = '+IntToStr(frmOperRep.cmbOperType.dbItemID)+' ) ';
    // if you need filtering by sender, then add it
    if frmOperRep.cmbContrFrom.dbItemID <> -1 then
    tmpSQL := tmpSQL+
      ' AND ( cFrom.id = '+IntToStr(frmOperRep.cmbContrFrom.dbItemID)+' ) ';
    // if you need filtering by recipient, then add it
    if frmOperRep.cmbContrTo.dbItemID <> -1 then
    tmpSQL := tmpSQL+
      ' AND ( cTo.id = '+IntToStr(frmOperRep.cmbContrTo.dbItemID)+' ) ';
    // if you need filtering by period, then add it
    if frmOperRep.dtpFrom.Checked then
    tmpSQL := tmpSQL+
      ' AND ( DATE( docDate ) >= DATE ( '+frmOperRep.dtpFrom.sqlDate+' ) ) ';
    if frmOperRep.dtpTo.Checked then
    tmpSQL := tmpSQL +
      ' AND ( DATE( docDate ) <= DATE ( '+frmOperRep.dtpTo.sqlDate+' ) ) ';
    // sort by dates
    tmpSQL := tmpSQL +
      ') as PData ORDER BY docDate ';
  frmOperRep.btnUpdate.dbSQL := tmpSQL;
end;
Code language: JavaScript (javascript)

The SQL query itself is quite complex. It includes a subquery to determine the purchase price. A subquery is also used to calculate the profit: the nested query calculates the amount of the purchase and the amount of the sale, and the outer one calculates the amount of profit.

SELECT
  operTypeName,
  contrFromName,
  contrToName,
  StrFTime("%d.%m.%Y",docDate),
  itemCode,
  itemName, 
  price, 
  qty, 
  unitCode, 
  price*qty as DisAmount, 
  amount, 
  (amount - price*qty) as margin 
FROM 
( SELECT 
    docType.name as operTypeName, 
    cFrom.name as contrFromName, 
    cTo.name as contrToName, 
    operDoc.docDate, 
    item.code as itemCode, 
    item.name as itemName, 
    ( SELECT op.amount / op.qty 
      FROM oper op
      LEFT JOIN operDoc od ON od.id = op.id_operDoc
      WHERE op.id_pitem = oper.id_pitem
      AND od.id_docType IN (1,6)
      ORDER BY od.docDate
      LIMIT 1 
    ) as price, 
    oper.qty, 
    unit.code as unitCode, 
    oper.amount 
  FROM oper 
  LEFT JOIN operDoc ON operDoc.id = oper.id_operDoc 
  LEFT JOIN docType ON docType.id = operDoc.id_docType 
  LEFT JOIN contr cFrom ON cFrom.id = operDoc.id_contr 
  LEFT JOIN contr cTo ON cTo.id = operDoc.id_contr1 
  LEFT JOIN item ON item.id = oper.id_pItem 
  LEFT JOIN unit ON unit.id = item.id_unit
  WHERE (1 = 1)
) as PData 
ORDER BY docDate
Code language: SQL (Structured Query Language) (sql)

The fifth line uses the StrFTime() date formatting function, since My Visual Database does not have the ability to set the data display type and all data returned by the SQL query is interpreted as text. The StrFTime() function has two parameters: display format and data field. In this case, the format includes the date (%d), month (%m), and year (%Y), separated by a dot. This representation is accepted in Russia, in other countries a different representation of the date may be accepted, which must be taken into account when localizing the application.

Line 13 calculates the profit as the difference between the purchase price and the sale price. In order for this calculation to be possible, it is necessary to calculate the purchase price in advance, which is done in the subquery, which is located in lines 15 to 43.

The price, in turn, is also calculated using the subquery found on lines 22-29. To do this, the first receipt document for this batch is found, from which the price is taken – the purchase amount is divided by the quantity.

Lines 34-39 contain commands for attaching additional data used for display and filtering: the date and type of document, the names of the recipient and sender, the name of the material value and its unit of measurement.

The WHERE clause on line 40 doesn’t seem to make sense, because the specified clause doesn’t do any filtering. But this technique allows, if necessary, to insert additional filtering conditions, which must begin with the AND keyword. Here’s what it will look like as a result:

...
  WHERE (1 = 1)
  AND ( operDoc.id_docType = 1 )
  AND ( cFrom.id = 5 )
  AND ( cTo.id = 4 )
  AND ( DATE( docDate ) >= DATE ( "2022-09-01" ) ) ';
  AND ( DATE( docDate ) <= DATE ( "2022-09-30" ) ) ';
) as PData 
ORDER BY docDateCode language: SQL (Structured Query Language) (sql)

Data Formatting

In order for financial information to look in the correct format, you need to programly adjust its display after loading it into the component of the tabular display. For this, the onchange () processor is used, in which the display in monetary format for speakers with the price, quantity, the amount of purchase, the amount of sale and profit.

procedure frmOperRep_tgrMain_OnChange(Sender: TObject);
// displaying report data
begin
   // set currency format
   Grid_FinFormat( Sender, 6, False);
   Grid_FinFormat( Sender, 7, False);
   // perform calculation of totals
   TdbStringGridEx(Sender).Columns[9].Footer.FormulaKind := fkSum;
   TdbStringGridEx(Sender).CalculateFooter;
   Grid_FinFormat( Sender, 9, True);

   TdbStringGridEx(Sender).Columns[10].Footer.FormulaKind := fkSum;
   TdbStringGridEx(Sender).CalculateFooter;
   Grid_FinFormat( Sender, 10, True);

   TdbStringGridEx(Sender).Columns[11].Footer.FormulaKind := fkSum;
   TdbStringGridEx(Sender).CalculateFooter;
   Grid_FinFormat( Sender, 11, True);
end;Code language: Delphi (delphi)

The Grid_FinFormat() procedure sets the display format for the specified table column:

procedure Grid_FinFormat( Sender:TObject; AColNumber:integer; ASum:boolean = True);
// formatting a table/tree column into currency format
var
  tmpGrid:TdbStringGridEx;
begin
  tmpGrid := TdbStringGridEx(Sender);
  TNxNumberColumn(tmpGrid.Columns[AColNumber]).FormatMask := '#,##0.00';
  if ASum then

    TNxNumberColumn(tmpGrid.Columns[AColNumber]).Footer.FormatMask := '#,##0.00';
end;Code language: Delphi (delphi)

Export print data

To display a report in MS Excel, it is enough to set the parameters (2) of the btnExcel (1) button as follows: select the action for the “Open in Excel” button (3), specify the table from which the data (4) and select the program to download the data (5). Don’t forget to click the “OK” button to save your settings.

Report output is possible in MS Execel or Calc (OpenOffice) programs.

Main Menu

Three reports have been added to the program:

  • Remains
  • Remains (parties)
  • Movement

To add a report form call to the main menu, we modify the procedure UserApp_InitForm():

procedure UserApp_InitForm;
// form initialization
var
  tmpItem:TMenuItem;
begin
  // splitters
  Splitter_Create( frmItem.pgcDetail, frmItem.panMain, alBottom);
  Splitter_Create( frmOperDoc.pgcDetail, frmOperDoc.panMain, alBottom);
  // menu
  //
  tmpItem := Menu_Add('','Reports',nil,1, '-');
  Menu_Add('Show_frmBalance','Balance',tmpItem);
  Menu_Add('Show_frmPBalance','Batteries (batches)',tmpItem);
  Menu_Add('Show_frmOperRep','Movement',tmpItem);
  //
  tmpItem := Menu_Add('','&References',nil,1, '-');
  Menu_Add('Show_frmContr','&Accounts',tmpItem);
  Menu_Add('Show_frmItem','&Nomenclature',tmpItem);
  Menu_Add('Show_frmPItem','Parts',tmpItem);
  Menu_Add('Show_frmItemType','&Item Type',tmpItem);
  Menu_Add('Show_frmUnit','&Units ',tmpItem);
  Menu_Add('Show_frmDocType','Document Type',tmpItem);
  //
  Menu_Add('Show_frmOperDoc','Document Log',frmMain.mniFile,0);
  Menu_Add('','-',frmMain.mniFile,1, '-');
  //
  Form_ShowOnWinControl( frmProductItem, frmItem.tshProductItem );
  Form_ShowOnWinControl( frmOper, frmOperDoc.tshOper );
end;
Code language: JavaScript (javascript)

As a result, the necessary items for calling reports will be added to the main menu:

Links

Leave a Reply

Your email address will not be published. Required fields are marked *