How to write reports

BILLmanager contains the most popular reports and allows you to add custom reports according to your specific needs.

If you want to write a more complex report, you should be familiar with SQL, BILLmanager database structure, and have programming skills.

Example of a simple report

You need to complete the following steps:

Add a report into the interface menu or list of reports
Describe the parameter input form (if needed)
Describe the report layout
Create a SQL request to receive data

To describe a report, use the xml file /usr/local/mgr5/etc/xml/<mgr>_mod_<name>.xml, where <mgr> is a product name, <name> is a plug-in name. Eg. we create the plug-in myreport for BILLmanager, so the file will have the name

 /usr/local/mgr5/etc/xml/billmgr_mod_myreport.xml

Let's create an XML- description

<mgrdata>
        <mainmenu level="29">
                <node name="stat">
                        <node name="myreport"/>
                </node>
        </mainmenu>
 
        <metadata name="myreport" type="report">
                <band name="company">
                        <query>select id, name from profile where account=1</query>
                        <col name="name" type="data"/>
                </band>
        </metadata>
 
        <lang name="en">
                <messages name="desktop">
                        <msg name="menu_myreport">My first report</msg>
                </messages>
                <messages name="myreport">
                        <msg name="title">Heading of my first report</msg>
                </messages>
        </lang>
</mgrdata>

In the above example, we added a link to our report into BILLmanager main menu → "Statistics". At the beginning of the report, we will show a list of provider's companies.

If everything is correct and you have several companies in the billing system, you will see something like this:

Inserted data

Let's generate a more complex report and display a list of payment methods for every company. To do so, add the inserted band. The report description will look like the following:

<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>

Enter parameters to generate a report

Let's consider how we can use the form. For example, we need to display only those payment methods which minimum payment amount is larger than a specified value. We need to add a form and parameter into the SQL-request. Add the firstrun="no" attribute to prevent the system from generating the report with any parameters before filling out the form.

Please note: escape the text inside the XML. For example:

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]]

Correct variant

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]]

<metadata name="myreport" type="report" firstrun="no">
        <form>
                <field name="minamount">
                        <input type="text" name="minamount" save="yes" required="yes" check="int"/>
                </field>
        </form>
        <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, minamount from paymethod2company pc left join paymethod p on pc.paymethod=p.id where pc.company=[[company.id]] and p.minamount&gt;=[[minamount]]</query>
                        <col name="paymethod" type="data"/>
                        <col name="minamount" type="data" sort="digit"/>
                </band>
        </band>
 </metadata>

Go from reports to other modules

If we want to switch from a report to a payment method edit form, edit our description:

<metadata name="myreport" type="report" firstrun="no">
                <form>
                        <field name="repminamount">
                                <input type="text" name="repminamount" save="yes" required="yes" check="int"/>
                        </field>
                </form>
                <band name="company">
                        <query>select id, name from profile where account=1</query>
                        <col name="name" type="data"/>
                        <band name="paymethod">
                                <query>select p.id, 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;=[[repminamount]]</query>
                                <col name="id" type="data" nestedreport="paymethod.edit"/>
                                <col name="paymethod" type="data"/>
                                <col name="minamount" type="data" sort="digit"/>
                        </band>
                </band>
</metadata>

We added the column with the payment method id with the nestedreport="paymethod.edit" attribute. It indicates that we need to add the link that will open a new tab with the paymethod.edit function. A column value will be passed as the key. Besides, all the form fields will be passed to the function. Since the paymethod.edit function has its parameter minamount to prevent changing it into the parameter from our form, I renamed the parameter on the form into repminamount.

Go to into an associated report

We can switch not only to other modules but also to associated reports. In the nestedreport attribute you need to specify the name of the report you want to switch to. Eg. I will create a report that will show payment statistics by different statuses associated with a certain payment method.

In the report, I will modify the description of the column with the link

<col name="id" type="data" nestedreport="myreport.detail"/>

and describe the new report

<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>

Add the message section

<messages name="myreport.detail">
                        <msg name="title">Payment methods statsitics</msg>
                        <msg name="status_1">New</msg>
                        <msg name="status_4">Paid</msg>
                        <msg name="status">Status</msg>
                        <msg name="cnt">Number</msg>
</messages>

Note how I have converted the status codes into their names.

Add charts and diagrams

Let's add a chart to our report. To do so, add the following record into band

<diagram name="statuspie" label="status" data="amount" type="pie"/>

If everything is correct, you will see something like this:

The complete file of our plug-in:

<mgrdata>
        <mainmenu level="29">
                <node name="stat">
                        <node name="myreport"/>
                </node>
        </mainmenu>
 
        <metadata name="myreport" type="report" firstrun="no">
                <form>
                        <field name="repminamount">
                                <input type="text" name="repminamount" save="yes" required="yes" check="int"/>
                        </field>
                </form>
                <band name="company">
                        <query>select id, name from profile where account=1</query>
                        <col name="name" type="data"/>
                        <band name="paymethod">
                                <query>select p.id, 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;=[[repminamount]]</query>
                                <col name="id" type="data" nestedreport="myreport.detail"/>
                                <col name="paymethod" type="data"/>
                                <col name="minamount" type="data" sort="digit"/>
                        </band>
                </band>
        </metadata>
        <metadata name="myreport.detail" type="report">
                <band name="payments">
                        <diagram name="statuspie" label="status" data="amount" type="pie"/>
                        <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>
 
        <lang name="ru">
                <messages name="desktop">
                        <msg name="menu_myreport">My first report</msg>
                </messages>
                <messages name="myreport">
                        <msg name="title">Heading of my first report</msg>
                        <msg name="paymethod">Payment method</msg>
                        <msg name="repminamount">Minimum payment</msg>
                        <msg name="hint_repminamount">Show only the payment methods with minimum payment amounts larger than the specified value</msg>
                </messages>
                <messages name="myreport.detail">
                        <msg name="title">Payment methods statistics</msg>
                        <msg name="status_1">New</msg>
                        <msg name="status_4">Paid</msg>
                        <msg name="status">Status</msg>
                        <msg name="cnt">Number</msg>
                </messages>
        </lang>
</mgrdata>

For more information, please refer to these articles

Form data with a script

Sometimes XML-descriptions are not enough for generating a report.

You may need to write script handlers in one of the following cases:

To generate a report using the data not from the database, for example, a list of files, output of external services, etc.
Dynamic SQL generation based on input parameters
Dynamic generation of the report structure, columns, additional statistics strings, etc.
Complex database structure which cannot be extracted with a single request

In this article, we are not going to describe how to write a custom handler in details. You can find this information in other articles of this Documentation (see the links at the end of this article). Here we will describe only the structure of the output data that your handler must generate.

In our example, the data look like that

<doc>
  <reportdata>
    <company>
      <elem>
        <id>1</id>
        <name>company 1</name>
        <paymethod>
          <elem>
            <id>1</id>
            <paymethod>Wire transfer company 1</paymethod>
            <minamount>100.0000</minamount>
          </elem>
          <elem>
            <id>2</id>
            <paymethod>WebMoney (WMR)</paymethod>
            <minamount>0.0000</minamount>
          </elem>
        </paymethod>
      </elem>
      <elem>
        <id>2</id>
        <name>company 2</name>
        <paymethod>
          <elem>
            <id>2</id>
            <paymethod>WebMoney (WMR)</paymethod>
            <minamount>0.0000</minamount>
          </elem>
        </paymethod>
      </elem>
    </company>
  </reportdata>
</doc>

The data must contain the "reportdata" tag, next - the band tag name (it is "company" in our example). You can place several bands in one report, one after another.

In every band, a separate data string is specified with the "elem" tag. Every column has the corresponding tag with a column name (in our example this is id, name, etc.).

The data are added in the same way for every inserted band, i.e. you first add a band name, then strings (elem), columns. You can use any number of inserted bands, however, it's useless to add more than three bands as it will be difficult to use such reports.

Text description

To make your report more comprehensive, we recommend that you add descriptions and also describe its blocks. The text messages (the msg tags from messages) with pre-defined names will help you

Report description - report_info
Data table description - table_[BANDNAME]
Diagram description - diagram_[DIAGRAMNAME]

Add your report into the list of standard reports

You can find different reports in BILLmanager → "Reports".

To add your report into this section, make sure that

the report name begins with the "report" prefix
the metadata tag contains the "group" attribute

<metadata name="report.myreport" type="report" firstrun="no" group="mygroup">

You can use any name for a group name or you can add your report into the existing group (finance, account, item, marketing, support).

Do not forget about localized messages

<messages name="reportlist">
  <msg name="report_mygroup">My report</msg>
  <msg name="report_myreport">My first report</msg>
</messages>

Access permissions

By default, all authorized users can execute the plug-in. Even if it is not added to the panel menu, it is available through API. If you want to show the report only to "Admin" users, add the level attribute to the metadata tag

<metadata name="report.myreport" type="report" firstrun="no" group="mygroup" level="admin+">

The report will be available only to root and administrators will full permissions. You can allow other users to view this report using standard access functions in the control panel.

The final result of our report will look like this:

<mgrdata>
        <metadata name="report.myreport" type="report" firstrun="no" group="mygroup" level="admin+">
                <form>
                        <field name="repminamount">
                                <input type="text" name="repminamount" save="yes" required="yes" check="int"/>
                        </field>
                </form>
                <band name="company">
                        <query>select id, name from profile where account=1</query>
                        <col name="name" type="data"/>
                        <band name="paymethod">
                                <query>select p.id, 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;=[[repminamount]]</query>
                                <col name="id" type="data" nestedreport="myreport.detail"/>
                                <col name="paymethod" type="data"/>
                                <col name="minamount" type="data" sort="digit"/>
                        </band>
                </band>
        </metadata>
        <metadata name="myreport.detail" type="report" level="admin+">
                <band name="payments">
                        <diagram name="d1" label="status" data="amount" type="pie"/>
                        <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>
 
        <lang name="ru">
                <messages name="reportlist">
                        <msg name="report_mygroup">My reports</msg>
                        <msg name="report_myreport">My first report</msg>
                </messages>
                <messages name="report.myreport">
                        <msg name="title">Heading of my first report</msg>
                        <msg name="paymethod">Payment method</msg>
                        <msg name="repminamount">Minimum payment amount</msg>
                        <msg name="hint_repminamount">Show only the payment methods with minimum payment amounts larger than the specified value</msg>
                </messages>
                <messages name="myreport.detail">
                        <msg name="title">Payment method statistics</msg>
                        <msg name="status_1">New</msg>
                        <msg name="status_4">Paid</msg>
                        <msg name="status">Status</msg>
                        <msg name="cnt">Number</msg>
                </messages>
        </lang>
</mgrdata>