BILLmanager 6
Documentation
/
BILLmanager 6
/
Developer section
/
How to write reports
en En
es Es
Add to Favorites
Focus mode
Documentation
/
BILLmanager 6
/
Developer section
/
How to write reports

How to write reports

BILLmanager contains the most typical and widely used reports, but this may not be enough. This article explains how to create your own report.

For this, you need SQL knowledge, as well as an understanding of the database structure in BILLmanager. If the report is complex and cannot be built with a single database query, you will also need programming skills in any language you know.

Simple report

Report development consists of the following steps:

  1. Adding the report to the main menu or to the report list.
  2. Describing the form for entering parameters, if needed.
  3. Describing the report layout.
  4. Building the SQL query to retrieve data.

The report definition, like any other plugin, is created using the XML file /usr/local/mgr5/etc/xml/<mgr>_mod_<name>.xml, where <mgr> is the product name and <name> is the plugin name. For example, the plugin for BILLmanager is called myreport. Then the file should be named /usr/local/mgr5/etc/xml/billmgr_mod_myreport.xml.

Example of adding a link to the report in the Statistics section in the left menu. The report shows a list of the provider's companies.

Example of adding a link

Nested data

To display a list of payment methods accepted by each company in the report, add a nested band to the definition:

Example of adding a band
<metadata name="myreport" type="report">
        <band name="company">
                <query>select id, name from profile where account=1</query>
                <col name="name" type="data"/>
                <band name="paymethod">
                        <query>select p.name_ru as paymethod from paymethod2company pc left join paymethod p on pc.paymethod=p.id where pc.company=[[company.id]]</query>
                        <col name="paymethod" type="data"/>
                </band>
        </band>
</metadata>

Parameter input form for building a report

For example, the report may need to display only payment methods whose minimum payment amount is greater than a certain value. To do this, add a form and a parameter to the SQL query. To prevent the report from running automatically with undefined parameters, stop it from being built until the form is filled in: add the firstrun="no" attribute.

Note that text inside the XML file must be escaped. For example:

Example without escaping
select p.name_ru as paymethod, minamount from paymethod2company pc left join paymethod p on pc.paymethod=p.id where pc.company=[[company.id]] and p.minamount>=[[minamount]]

should be written like this:

Example with escaping
select p.name_ru as paymethod, minamount from paymethod2company pc left join paymethod p on pc.paymethod=p.id where pc.company=[[company.id]] and p.minamount&gt;=[[minamount]]
Example

Navigation from reports to other modules

To navigate from the report to the payment method edit form, add the payment method id column with the nestedreport="paymethod.edit" attribute. The "nestedreport="paymethod.edit" attribute creates a link that opens a new tab with the paymethod.edit function. The following values will be passed to the function as keys:

  • the payment method id value;
  • all form fields.

The paymethod.edit function has its own parameter minamoun t. Therefore to prevent it from being replaced by a parameter from the form, rename the parameter in the form to repminamount.

Example of the paymethod.edit function

Navigation to a linked report

To add navigation to a linked report, specify the name of the report you want to open as the value of the nestedreport attribute.

To create a report that displays payment statistics in different statuses for a specific payment method:

  1. In the existing report, change the description of the linked column:

    Example of column description
    <col name="id" type="data" nestedreport="myreport.detail"/>

  2. Add the definition of the new report:

    Example of report description
    <metadata name="myreport.detail" type="report">
                <band name="payments">
                        <query>select status, sum(paymethodamount) as amount, count(*) as cnt from payment where paymethod=[[elid]] group by status</query>
                        <col name="status" type="msg"/>
                        <col name="amount" type="data" convert="money" sort="digit" total="sum"/>
                        <col name="cnt" type="data" sort="digit" total="sum"/>
                </band>
</metadata>

  3. Add a message section for the new report:

    Example of a message section
    <messages name="myreport.detail">
                        <msg name="title">Payment method statistics</msg>
                        <msg name="status_1">New</msg>
                        <msg name="status_4">Credited</msg>
                        <msg name="status">Status</msg>
                        <msg name="cnt">Amount</msg>
</messages>

Note that the status codes were converted to their names.

Adding charts and graphs

To display a chart in the report, add the following line to the band:

<diagram name="statuspie" label="status" data="amount" type="pie"/>
Example of a report with a chart

Generating data with a script

Not all reports can be built using only an XML definition.

The main reasons why handler scripts are needed:

  • building a report from data stored outside the database, for example, a file list, output from external services, and so on;
  • dynamic SQL generation based on input parameters;
  • dynamic building of the report structure, set of columns, additional statistics rows, and so on;
  • complex data structure that is hard to retrieve with a single query.

Structure of the output data that the handler must generate

The data must contain the reportdata tag, then at the next nesting level there is the band name tag (in our example, this is company). Several bands can be placed one after another in a single report. In each band, a separate data row is represented by the elem tag, where each column corresponds to a tag named after the column. In the example script from the article, these are id, name, and so on.

For each nested band, data is added in the same way, i.e. the band name, rows (elem), columns. Any number of nested bands can be used, but we do not recommend more than three, because reports become difficult to understand.

Example with two band name tags

Text descriptions

To make the report clearer, we recommend adding a description and labels for its different blocks. Use text messages for this (the msg tags from messages) with predefined names:

  • report_info — report description;
  • table_[BANDNAME] — table description with data;
  • diagram_[DIAGRAMNAME] — chart description.

Embedding the report into the standard report list

To add your custom reports to the left menu Reports section, follow these rules:

  • the report name must start with the report. prefix;
  • the metadata tag must contain the group attribute.
Example of a meta tag
<metadata name="report.myreport" type="report" firstrun="no" group="mygroup">

You can use your own name for the group, or add the report to one of the existing ones:

  • finance;
  • account;
  • item;
  • marketing;
  • support.

You can localize messages:

Example of localization
<messages name="reportlist">
  <msg name="report_mygroup">My reports</msg>
  <msg name="report_myreport">My first report</msg>
</messages>

Access rights

By default, all authenticated users can run any plugins. Even if a plugin is not in the interface menu, it is available through the API.

To make the report available only to users with administrator rights, add the level attribute to the metadata tag:

Example of a line with the level attribute
<metadata name="report.myreport" type="report" firstrun="no" group="mygroup" level="admin+">

Now the report is available only to administrators with full rights, as well as the root user. For all other employees or departments, access can be granted through the standard rights assignment interface.

Report plugin

The plugin, which is embedded into the left menu as a separate item and has no access rights restrictions:

Example plugin

The plugin taking into account renaming the report and moving it from the main menu to the report list:

Example plugin