Atomia Billing System

Creating and managing reports

Tags: 394 views 0

How to create new or edit existing reports and how to view data provided by them.

Overview

The reports feature comes with a set of predefined billing reports which can be used out of the box without any additional configuration. This feature also allows you to create new custom reusable report data sources and reports which can be saved for future use by your staff.

Before you begin

The reports consist of two parts:

  • Data source – Specifies how the data is fetched from the database (using SQL query or stored procedure), which columns that are available, and how data is formatted. It also specifies the filtering.
  • Report definition – Specifies the columns which are shown in the report, their type, and segmentation options.

Data sources can be shared between reports. This means that one data source can provide data for a few different reports if they require similar data but differ in their presentation.

Creating a Data source

  1. Open Atomia Admin Panel and go to Reports > Billing Reports and click the Data Sources button to list available data sources.
  2. Click the Create new Data source button to open the form for adding a new data source.
  3. Give your Data source a name and (optionally) a description.
  4. Select the type of data source (SQL query or Stored procedure).
  5. Based on the previous choice, provide the query or the stored procedure name for retrieving the data.
  6. Click on the Check query button to test the entered query or stored procedure and fetch test data and available columns.
    1. If the entered query is not valid or the stored procedure fails to execute, check the error message, correct your query, and test it again by clicking Check query.
  7. Select the column you want to make available for reports by checking the check box in its row.
  8. Give the column a user-friendly alias which will be used as a column header in the reports.
  9. Select the data type for formatting of the column data.
  10. Repeat steps 7-9 for all desired columns.
  11. Order the columns in the required order using the Up and Down buttons.
  12. In the filters table, select the filters you want to enable with the data source.
    1. If you are using a SQL query data source type, map every filter to one of the selected columns.
  13. When you are done, press Save to store your Data source.

Creating a Data Source

Image 1: Creating a Data Source.

Important!

When composing SQL queries for report data sources, keep in mind that they have to have the following format:

select * from (_your query which extracts the data as nested query_) _some_identifier_

Here is one example of a valid data source query:

select * from (select i.fk_account_id, cast(i.invoice_date as date) as invoice_date , i.number, i.currency, i.total, i.customer_id, i.customer_name from invoice i where status <> 1) inv_data

The data source filters will be appended to this query, meaning that your initial filtering should be done inside the nested query like shown in the example.

Important!

If the stored procedure is to be used for data retrieval it has to be pre-created on the database server. When it is tested during the creation of the data source it will be executed and its output presented as test data and available columns. In order for filters to be used it has to accept parameters with names @Reseller, @FromDate, and @ToDate.

The following is a valid example of the procedure which can be used as the data source:

CREATE PROCEDURE [dbo].[InvTotalsTest]

@Reseller UNIQUEIDENTIFIER,

@FromDate DATETIME,

@ToDate DATETIME AS 

SELECT i.fk_account_id, CAST(i.invoice_date AS DATE) AS invoice_date, i.number, i.currency, i.total, i.customer_id, i.customer_name 

FROM invoice i WHERE i.status &lt;&gt; 1 AND i.fk_account_id = @Reseller AND i.invoice_date BETWEEN @FromDate AND @ToDate

Creating a Report

  1. Open Atomia Admin Panel and go to Reports > Billing Reports which lists existing reports.
  2. Click the Create new report button to open the form for adding a new report.
  3. Give your report a name and (optionally) a description.
  4. Select the data source for the reports which will list available columns.
  5. Select the column you want to show in the report by checking the check box in its row.
  6. Select the type of the column.
  7. Check the segmentation checkbox if you want to allow segmentation for the column.
  8. Repeat steps 5-7 for all desired columns.
  9. Order columns in the desired order using the Up and Down buttons.
  10. When you are done with all columns, press Save to store your report.

Creating a Report

Image 2: Creating a Report.

Keep in mind

There are three types of report columns:

  • Info – The default type which should be used for all non-currency and non-numeric columns. This column type is available for segmentation (i.e. grouping).
  • Currency – Should be used for the currency column in billing reports and there can be only one column of this type per report. This column is used for the totals summary and is always added to the segmentation.
  • Value – Should be used for all numeric columns which are going to be summarized when the segmentation is done. In the previous example, the invoice total column is the candidate for this type. This means that when segmentation by date is done the totals will be summarized by date and currency.

Viewing reports

  1. Open Atomia Admin Panel and go to Reports > Billing Reports which lists existing reports.
  2. Click on the report name to load it with the default filters’ values.
    1. If the report has a reseller filter the current reseller from the reseller selector is used.
    2. If the report has a date period filter the current month will be used.
  3. Adjust the filters to the desired values.
    1. If the report has a reseller filter, change reseller using the reseller selector at the top of the page.
    2. If the report has period filter, click on date control to open the calendar and select the desired period.
  4. The report will re-render with the selected values.
  5. Segment report data by selecting one or more segmentation columns from the drop-downs. When a segmentation column is selected the report engine performs segmentation by summing the value columns by selected segmentation and currency columns.

Viewing reports

Downloading the report in CSV format

  1. Open Atomia Admin Panel and go to Reports > Billing Reports which lists existing reports.
  2. Click on the report name to load it with the default filters’ values.
  3. Adjust the filters to the desired values.
  4. Do segmentation if needed.
  5. Click the Generate CSV button to prepare data.
  6. Click the Download CSV button to download a CSV file to your computer.

Useful tip

The report engine always exports data based on the current filters’ values and selected segmentation. It will export all data, not only the current page.

Printing reports

  1. Open Atomia Admin Panel and go to Reports > Billing Reports which lists existing reports.
  2. Click on the report name to load it with the default filters’ values.
  3. Adjust the filters to the desired values.
  4. Do segmentation if needed.
  5. Select to show all data rows in the footer of the data table.
  6. Print the page directly from your browser. It will print the table only without other page elements.

Was this helpful?

Related Articles