Description of reports

Introduction

A control panel has a built-in mechanism for generating various reports. The report is understood as a presentation of statistical data in the form of lists with or without diagrams.

The following discussion assumes that the reader is familiar with the general metadata structure of the ispmanager UI, an overview of which is given in the article “Structure and functionality of plugins”.

If you are faced with the task of creating a new report in ispmanager, there are two possible solutions:

• write a handler that will return an XML description of the page with the report. Follow the link above to read more about handlers. This method is considered as the general one in the article, the section about <reportdata> element is applicable only when using this reporting option.
• add report metadata to the plugin's XML description and use <action> or <query> elements to retrieve the data. Sample code for this option is given at the end of the article. 

Below is a description of the XML metadata structure on which the reports are based, as well as the structure of the data passed to be displayed in the reports.

General structure of the report page

• Form: is used to select the range or object of the displayed data. It is described by <form> tag inside the <metadata> element.

• Table: displays the data. It is described by the <band> tag inside the <metadata> element. A table in a report is slightly different from a regular list. It has sorting, there can be more than one table per page; table cells can contain links to nested reports as well as anchors to reports below. A table can be hidden or shown using a special button above the table.
• Diagram: is based on data from a table, described by the <diagram> tag inside the <band> element. Thus, the diagram in a report is always linked to a table. Several diagrams can be based on data from one table.

A <band> element can contain nested <band> elements corresponding to child reports that are displayed below the “parent” on the same page. It is assumed that the child reports display the data corresponding to the report parameters selected in the form, just like the parent report.

<metadata name="journal.stat" type="report" level="30" firstrun="no">
 	 <toolbar>
		 <toolbtn func="journal" type="back" img="t-back" name="back"/>
	 </toolbar>
	 <form>
		 <period/>
	 </form>
	 <band name="function">
		 <diagram name="func" label="funcname" data="percentage" type="pie"/>
		 <col name="funcname" type="data" total="count" link="yes"/>
		 <col name="percentage" type="data" sort="digit" sorted="desc" total="sum"/>
		 <band name="user">
			 <diagram name="user" label="username" type="histogram">
				 <line data="percentage"/>
			 </diagram>
			 <col name="username" type="data" total="count"/>
			 <col name="percentage" type="data" sort="digit" sorted="desc" total="sum"/>
		 </band>
	 </band>
 </metadata>

Comments on the metadata above:

• The above given code does not contain data for the report, nor does it contain localization strings such as the report header. It only contains metadata describing the structure of the report. Report data and localization strings will be discussed in later sections of this article.
• The <toolbar> element is not directly related to the report and performs the same function as in forms, it contains buttons that can perform various actions. In this case, the toolbar contains the “Back” button.
• The top-level <band> element describes the main report. The table of the main report contains three columns described by <col> elements.
• The nested <band> element describes child tables, the number of which will correspond to the number of rows in the table of the main report.
• Both parent and nested <band> elements contain a <diagram> element, so that a pie chart for the main report and one histogram for each of the child tables will be displayed.

Example of the report based on the metadata given above:

<metadata> element

The <metadata> element containing the report metadata has a number of attributes that are important for this type of page:

type="report" - indicates to ispmanager that the element data should be interpreted as a report description;

name - a mandatory attribute, as in the case of other page types, specifies the func (function) in ispmanager that handles this report. When submitting form data within the report, this data will be passed to the handler associated with the specified func.

firstrun - if it is set to firstrun="no”, the report will not be generated when the page is opened, i.e. the page will not contain report tables and charts until the form data with report parameters is sent. Can be useful when the report takes a long time to generate.

Localization strings

You can use the messages in the messages section to set descriptions for the entire report and for individual tables and diagrams.

Report description

To add a text description of the report under the header and above the form, add a message named report_info to the list of messages in the report xml, for example:

<msg name=“report_info”>Detailed report on server resource usage</msg>

Table description

To add a description to a certain table with data, add a message with a name in the format table_[BANDNAME] to the messages section of the XML report, for example:

<msg name=“table_used_disk”>Disk space usage</msg>.

Diagram description

To add a description to an individual diagram, a message with a name in the format diagram_[DIAGRAMNAME] should be added to the messages section of the XML report, for example:

<msg name=“diagram_used_disk_chart”>Diagram of changes in disk space usage</msg>

<form> element

This element describes the form if it has to be displayed. The form is displayed at the top of the page right after the header and can be used for selection of parameters for building the report. The structure of this element is the same as in the description of regular forms.

Make sure to use validators to check input values. For example, validator check=“date” in combination with mask=“9999-99-99-99” checks input values for compliance with the format “date YYYYY-MM-DD”, validator check=“int” checks that an integer was entered. Read more: Validators

<band> element

Structure of the report data and output data interface are described with the <band> element. Here is an example of its structure:

<band>
   <query/>
   <action/>
   <diagram>
      [[<line/>]]
   </diagram>
   <col/>
   [[<band>]]
 </band>

The <band> elements can be included into <band> of a higher level.  The data displayed in <band> is specified either by means of <query> or <action> elements, or by passing data in the <reportdata> element (see the <reportdata> element below).  All included <band> elements must have either <action> or <query> element. One <band> cannot contain both <query> and <action> elements.

<band> element attributes:

name attribute: band name, a key attribute.

hidden attribute: if specified as hidden="yes", the table with data will be hidden.

 <col> element

Represents a column of data in the report table.

Attributes:

name - column name, a key attribute.

convert - algorithm for encoding values in cells when calculating totals (see attribute total). The only possible value is “bytes” - data size format, e.g. “128 MB”.

link - if specified as link="yes", the column will contain a link (anchor) to child data elements, while values of the column will be the headers for the child tables.

sort  - specifies how to sort out data in the column. Its value can be either alpha (sort alphabetically; by default) or digit (sort in ascending order).

total - specifies total sums shown at the bottom of your table. Possible values: count (number of rows with data in that column), sum (total sum of cell values in that column, for numeric columns), sumsuffix (sum of cell values in this column with suffix processing and convert=“money”), avg (average of cell values in this column).

nestedreport- adds a link to another report (function) that will be specified in the attribute's value. Also, the page of the other report will be passed the parameters elid - the value in the cell (or the id attribute from elem), colname - the column name, and all the parameters of the report form.

<query> element

Contains an SQL-query that provides data to be displayed in the report.

The <query> element can only be used in the plugin's XML description (see examples at the end of this article), but not in the XML data returned by the handler.

In double square brackets: [[value]] - you can pass values from the form into the query or a value from the main table (usually the id record ) into the query for the child table.

You should use aliases for columns in the query to associate them with the <col> element.

Example of using aliases and values in double square brackets:

 .....
  <form>
    <!-- getting the value of the ip address for which the report should be displayed -->
    <field name="ip">
      <input name="ip" type="text"/>
    </field>
  </form>
  <band name="emaildomain">
    <!-- query to ispmanager database using the entered ip address value, aliases are used -->
    <query>SELECT emaildomain.id AS id, emaildomain.name AS name, emaildomain.ip AS ip as email_cnt FROM emaildomain WHERE ip=[[ip]]</query>
    <!-- the name attribute of the <col> element is passed the alias from the query above -->
    <col name="name" type="data" link="yes"/>
    <col name="ip" type="data"/>
    <band name="email" psort="used">
      <!-- query for child tables using the id of the domain for which the table is built-->
      <query>SELECT name AS emailname, used FROM email WHERE domain=[[emaildomain.id]]</query>
      <col name="emailname" type="data"/>
      <col name="used" type="data"/>
    </band>
  </band>
  .....

When generating reports with queries to a database, you should specify a source of data for such queries. Use the isp_api::SetReportSource() function to specify the source. By default, all queries access the current panel database if no other is specified.

<action> element

Contains a query to any function. The function should provide report data according to its description.

The <action> element can only be used in the plugin's XML description (see examples at the end of this article), but not in the XML data returned by the handler.

The values of the name attribute on <col> elements must match the names of the fields in the list rows.

In addition to the name of the function in the string inside the <action> element, you can set function call parameters, as demonstrated in the example below:

 .....
  <band name="testdata">
    <!-- requesting data on active sites -->
    <action>
      webdomain?filter=on&amp;active=on
    </action>
    <!-- using the site name and folder in the report table -->
    <col name="name"/>
    <col name="docroot"/>
  </band>
.....

<diagram> element

Contains a diagram description to the table specified by the parent <band> element

Attributes:

name - name of the diagram;

label - specifies which columns of the table should be the source for diagram captions;

type - specifies the type of the diagram. Possible values: “pie” (circular), “histogram” (vertical columns), “line” (linear).

Diagram types:

pie - a pie chart;

histogram - a diagram with vertical columns;

line - a diagram with one or more lines connecting the value points.

<line> element

Contains information about where to get data for the diagram, used only inside <diagram> elements with type=“histogram” and type=“line”. A diagram can have multiple line elements.

Attributes:

data - specifies which columns of the table should be the source for the diagram.

Report data XML: <reportdata> element

In addition to the metadata describing the report structure (<band> element within <metadata>), the report requires data to be displayed in it, which is described in the <reportdata> element. This element is used only when the report data is generated by the handler. It does not need to be used if the data for the report is generated through<query> or <action> elements in the report metadata.  

<reportdata> is the immediate parent for elements which names match the value of the name attribute of top-level <band> elements. Thus, if there is a <band name=“traffic”> element in <metadata>, there will be a <traffic> element inside <reportdata>.

Each row of the data table is described by an <elem> element, where element names match the values of the name attribute on <col> elements. In addition, <elem> elements may contain elements describing data for a child table, their names will match the values of the name attribute on the child <band> elements.

Here's an XML example that contains both metadata and data for creating a report:

 <metadata name="journal.stat" type="report" level="30" firstrun="no" mgr="core">
    <!-- top-level <band>, the value of the name attribute corresponds to the <function> element within the <reportdata> -->
    <band name="function">
      <diagram name="func" label="funcname" data="percentage" type="pie"/>
      <!-- top-level column, the value of the name attribute corresponds to the <funcname> element within the <elem> top-level column -->
      <col name="funcname" type="data" total="count" link="yes"/>
      <col name="percentage" type="data" sort="digit" sorted="desc" total="sum"/>
      <!-- <band> of the second level, the value of the name attribute corresponds to the <user> element within the <elem> of the top level -->
      <band name="user">
        <diagram name="user" label="username" type="histogram">
          <line data="percentage"/>
        </diagram>
        <!-- second-level column, the value of the name attribute corresponds to the <username> element within the <elem> of the second level -->
        <col name="username" type="data" total="count"/>
        <col name="percentage" type="data" sort="digit" sorted="desc" total="sum"/>
      </band>
    </band>
  </metadata>
  <reportdata>
    <!-- data for the top-level table described by <band name=“function”>  -->
    <function>
      <elem>
        <!-- cell value in the <col name=“funcname”> column in the top-level table -->
        <funcname>mgr.edit</funcname>
        <percentage>100.00</percentage>
        <!-- data for a child table that is described by <band name="user">  -->
        <user>
          <elem>
            <!-- cell value in the <col name=“username”> column in the child table -->
            <username>root</username>
            <percentage>100.00</percentage>
          </elem>
        </user>
      </elem>
    </function>
  </reportdata>

Example of adding metadata to a plugin's XML description using <query> and <action>

The two examples below serve the same purpose: to display information about email domains. The first example utilizes an <action> element, while the second employs a <query> element. Both examples assume that the XML metadata of the report is added to the plugin's XML without using a handler. Since no handler is used, the <handler> element in the plugin's XML description is not needed in this case. Read more about working with XML descriptions of plugins in the article “Structure and functionality of plugins”.

The following example usiese the <action> element which enables the utilization of data from an existing list for the report, in this case, from the "Mail Domains" page (func=emaildomains).

<?xml version="1.0" encoding="UTF-8"?>
<mgrdata>
    <!-- adding a new group and a new item to the main menu -->
    <mainmenu level="admin+">
        <modernmenu>
            <node name="reports">
                <node name="emaildomain_report" />
            </node>
        </modernmenu>
    </mainmenu>
    <!-- report metadata -->
    <metadata name="emaildomain_report" type="report" level="admin+">
        <band name="emaildomain" psort="name">
            <action>emaildomain?filter=on&amp;active=on</action>
            <col name="name" type="data"/>
            <col name="owner" type="data"/>
        </band>
    </metadata>
    <lang name="ru">
        <!-- localization messages for the main menu -->
        <messages name="desktop">
            <!-- name of the group in the modern_menu-->
            <msg name="modernmenu_reports">Reports</msg>
            <!-- name of the menu item-->
            <msg name="modernmenu_emaildomain_report">Mail domains</msg>
        </messages>
        <!-- localization messages for the report page -->
        <messages name="emaildomain_report">
            <msg name="msg_hidedata">Hide data</msg>
            <msg name="msg_nodata">No data</msg>
            <msg name="msg_showdata">Show data</msg>
            <msg name="name">Domain</msg>
            <msg name="owner">Owner</msg>
            <msg name="email_cnt">Number of mailboxes</msg>
            <msg name="report_info">Email domain stats</msg>
            <msg name="title">Report: Email Domains</msg>
        </messages>
    </lang>
</mgrdata>

Example using the <query> element, a form, a diagram and nested tables:  

<?xml version="1.0" encoding="UTF-8"?>
<mgrdata>
    <!-- adding a new group and a new item to the main menu -->
    <mainmenu level="admin+">
        <modernmenu>
            <node name="reports">
                <node name="emaildomain_report" />
            </node>
        </modernmenu>
    </mainmenu>
    <!-- report metadata -->
    <metadata name="emaildomain_report" type="report" level="admin+" firstrun="no">
        <form>
            <field name="ip">
              <input name="ip" type="text"/>
            </field>
          </form>
        <band name="emaildomain" psort="name">
            <query>SELECT emaildomain.id AS id, emaildomain.name AS name, emaildomain.ip AS ip, COUNT(email.id) as email_cnt FROM emaildomain, email WHERE email.domain=emaildomain.id AND ip=[[ip]] GROUP BY emaildomain.id</query>
            <col name="name" type="data" link="yes"/>
            <col name="email_cnt" type="data"/>
            <diagram type="pie" name="emaildomain" data="email_cnt" label="name" />
            <band name="email" psort="used">
                <query>SELECT name AS emailname, used FROM email WHERE domain=[[emaildomain.id]]</query>
                <col name="emailname" type="data"/>
                <col name="used" type="data"/>
            </band>
        </band>
    </metadata>
    <lang name="ru">
        <!-- localization messages for the main menu -->
        <messages name="desktop">
            <!-- name of the group in the modern_menu-->
            <msg name="modernmenu_reports">Reports</msg>
            <!-- messages for the modern_menu, name of the item -->
            <msg name="modernmenu_emaildomain_report">Mail domains</msg>
        </messages>
        <!-- localization messages for the report page -->
        <messages name="emaildomain_report">
            <msg name="msg_hidedata">Hide data</msg>
            <msg name="msg_nodata">No data</msg>
            <msg name="msg_showdata">Show data</msg>
            <msg name="name">Domain</msg>
            <msg name="ip">IP</msg>
            <msg name="email_cnt">Number of mailboxes</msg>
            <msg name="emailname">Name of the mailbox</msg>
            <msg name="used">Disk space used</msg>
            <msg name="report_info">Email domain stats</msg>
            <msg name="title">Report: Email Domains</msg>
        </messages>
    </lang>
</mgrdata>

Below is the report generated with the code from the second example: