Automatic generation of reports only for a specified group of subscribers in Tariscope
One of the modules of the Tariscope billing system, both Tariscope Enterprise and Tariscope Provider, is the Tariscope Tasks. Its main purpose is to automate actions related to most of the tasks faced by the Tariscope system user.
One of the main such tasks is the automatic generation of reports on schedule. If such reports are to be generated for individual subscribers, the question arises of how to specify them when creating a Scheduler task. This is the question we will consider in this article.
To create a new task for Tariscope Tasks, select Tariscope Tasks → Task List from the menu. The Task List page will open, an example of which is shown in Figure 1.
Figure 1
Click on the Add icon on the toolbar. The New task page will open (Figure 2).
Figure 2
We will not describe all the features of setting up a task to generate a report. You can see this in the article. The only thing to note is that in the Action type list, the Report item must be selected, and in the Report name box, the report that needs to be generated.
The Source list is used to determine which subscribers should generate this report. It has three values:
- SQL queries.
- Subscriber.
- Group.
Any of these items allow you to select a list of subscriber’s IDs for which you want to create a report.
If you want to create a report for individual groups of subscribers that are in the Tariscope system, then select the Group item (Figure 3).
Figure 3
Click on the “…” button to the right of the Select subscribers group item. The Group window appears, an example of which is shown in Figure 4.
Figure 4
Select the subscriber groups for which the report should be created and click Select. The identifiers of the selected groups will be displayed in the Select subscriber group box.
Other task settings are performed as in general for setting up a task for generating a report.
In the case when you want to generate reports for subscribers based on some other criteria than their belonging to a group, then select the value Subscriber in the Source list. Click on the “…” button to the right of the Select subscriber group item. The Subscribers window appears, an example of which is shown in Figure 5.
Figure 5
Select the required subscribers and click the Select button. In this case, the identifiers of the selected subscribers will be displayed in the Select subscriber group box.
Other task settings are performed as in general for setting up a task for generating a report.
In the case when you need to generate reports for some subscribers you have selected or subscribers who have some identical parameter, then in the Source list, select the SQL queries value. This choice requires the user to know SQL and the structure of the Tariscope database but allows you to flexibly select any subscribers by any of their parameters.
[The Tariscope database catalog, which contains a description of the database tables, can be provided to an official Tariscope user upon his request to the SoftPI support service.]
Click on the Select subscriber group button, which is located to the right of the item with the same name. The Edit Subscriber Group window appears, an example of which is shown in Figure 6.
Figure 6
This window displays an SQL query that selects the ID and Email fields of those subscribers who are currently active from the stored procedure dbo.ab_mc_abonents (this is done using the condition:
GETDATE() BETWEEN ConnectDate AND DisconnectDate, where
GETDATE() is a function that gets the current date,
ConnectDate is the date from which the subscriber becomes active,
DisconnectDate is the date until which the subscriber is considered active.
For the above query, you can use the Subscribers table instead of the dbo.ab_mc_subscribers stored procedure.
The query selects two fields: ID and Email. ID (subscriber identifier) is a required field because it is passed to the report via the AbonentID report parameter, ensuring that the report is created specifically for this subscriber.
If reports do not need to be sent to subscribers by email, but will only be stored in a folder, then you can omit the Email field in the request.
Suppose, you want to generate reports using the Tariscope Tasks for subscribers with IDs: 6229, 6225, 6233. In this case, replace the SQL query in the window (Figure 6) with the following:
SELECT ID, EMail FROM Abonents WHERE ID IN (6229, 6225, 6233)
The selection of subscribers with specific identifiers (ID) is performed based on the following condition:
ID IN (6229, 6225, 6233)
Now let's assume that you want to generate reports for all subscribers who have an active tariff plan with identifier (ID) = 43. To do this, you need to write an SQL query, one of the options of which is given below:
SELECT ID, EMail FROM ab_mc_abonents AS Ab
WHERE AbonentPlaneID = 43
Other SQL reports are also possible when you need to select subscribers by other parameters.
If you need to create an SQL query to generate reports for some specific subscribers, but you do not know how to write an SQL query, contact the SoftPI technical support service.
Virtual subscribers
Tariscope that is a call accounting and billing system contains a feature that is named 'virtual subscribers'. What is it and for which purposes it can be used?
"Virtual subscribers" are pseudo-subscribers, which can be assigned to a route (a group of lines) or an IP gateway connecting your telephone system with some communication operator.
Tariscope performs a call rating only if calls were made from or to a subscriber whose data are in the Tariscope database. In the case of transit calls, as a rule, there are no such subscribers and, accordingly, such calls are not rated. But if you still need to rate them, the virtual subscriber will come in handy. The entire cost of calls passing through a route or gateway will be charged on the subscriber and transit calls will also be charged.
Consider a different situation. For example, the PBX has 3 routes, each of which is connected to various telecom provides. And you want to know at any moment: how many calls and what call types passed on a route, how much they cost, what kind of telephone traffic prevails: incoming or outgoing, etc. Answers to such questions can be relevant both for corporate users who use the Tariscope Enterprise edition and for telecom providers who use the Tariscope Provider edition. This information can be obtained, for example, from a report on routes. But again, if transit calls pass through the routes, then these calls will not be rated without virtual subscribers, and the report will contain data that will not include the cost of transit calls. The use of virtual subscribers is the most correct way to solve such a task.
The feature of "virtual subscribers" can be especially actual for transit telecom operators or telecommunication operators offering services of a virtual PBX. Because Tariscope allows users to simultaneously calculate the cost of calls at two different rates, you can immediately get the cost of calls that subscribers must pay and the cost at which it is necessary to carry out mutual settlements with another telecom operator.
When creating a virtual subscriber, please note that its use corresponds to 100 subscribers of a Tariscope Enterprise license or 100 phone numbers of a Tariscope Provider license.
Creating Virtual Subscribers
To create a virtual subscriber, in the Tariscope program, go to the Subscribers configuration page. On the toolbar, click on the New subscriber icon. The New subscriber window appears where you should specify a subscriber name which allows you to understand who is this subscriber. For example: Route to the AAA provider.
Make other settings: set a rate plan, the rates of which will be applied to this subscriber, and if necessary, specify the date that this subscriber is considered as. It does not matter what type of subscriber will be assigned to him. You do not need to specify phone numbers and other parameters that are necessary for a real subscriber.
To specify that this subscriber is a virtual one, select the Details tab and turn on the Virtual subscriber switch as shown in Figure 1.
Figure 1
Save these data.
Go to the Routes and gateways configuration page for PBX. If you already have previously set route / gateway data, select the desired route / gateway from the list of routes / gateways and click on the Transit call tariffication icon on the toolbar. The Transit call tariffication window appears, the example of which is shown in Figure 2.
Figure 2
In the Virtual subscriber list, select the virtual subscriber who was created on the previous step.
Click Save.
This completes the entire configuration for the virtual subscriber. All new calls on the route (gateway) will be charged to this subscriber. If necessary, you can re-rate the already available calls data so that the cost of calls will be also charged to the virtual subscriber.
If you are using the Tariscope Provider edition, then find the created virtual subscriber in the accounts and select the month. The call charges for this month will be displayed. If you need to estimate the costs for a route/gateway for a period shorter than a month, then in the call view, filter the data for the required period for this virtual subscriber and group the data for this subscriber. It is also possible to generate a report by routes.
Import of new service prices from an Excel file
To import services, you need to use an xlsx file with information about new service prices. Files in the xls format are not supported, as this format has long been obsolete. If you have an xls file with services from which you need to export new service prices, open it in Excel and save it in the xlsx format.
For greater convenience when setting up the import, it is better if the first row in the Excel file is the table header, as shown in Figure 1.
Figure 1
In this case, when "linking" Excel file columns to database fields, the column names from the Excel file table header will be displayed. If there are empty rows at the beginning of the table, then instead of the column names from the table, the following will be suggested: C1 - for the first column, C2 - for the second column, and so on.
At a minimum, the Excel file with new service prices should contain the following fields:
- The service identifier (ID) in Tariscope.
- The new price.
- The date from which this price becomes effective.
To import data about new service prices, open the Services page and click the Import from file icon (Figure 2).
Figure 2
The Import Wizard page appears (Figure 3)
Figure 3
Click the Start button. The next step of the Import Wizard will be displayed (Figure 4).
Figure 4
As mentioned above, only Excel files in the xlsx format are used for import. Click the Choose button and select the appropriate Excel file. Click the Next button. The next import step will be displayed (Figure 5).
Figure 5
The Available tables list contains a list of Excel sheets. Select the desired one and click the Next button. In the next import step (Figure 6), you must select the correspondence of the Excel file columns to the Tariscope database field names.
Figure 6
In the example shown in Figure 6, an Excel file was opened that contained blank rows before the data table. As a result, the columns are named: C1 … C5.
Accordingly, in the Service ID list, you must select the value C3, because this column contains service identifiers.
In the Price list, select the value C4, and in the From date list, select the value C5.
If the Excel file has a table header in the first row, this import step will be displayed as shown in Figure 7.
Figure 7
In this case, the lists will contain the column names from the table header (Figure 8).
Figure 8
Click the Next button. The Import Wizard page will look like Figure 9.
Figure 9
Here you must specify the date format used in the Excel file. For example, if your date is specified as: Month.Day.Year, then you must enter the format: MM.dd.yyyy
Click the Start Import button.
Check the import results on the Services page. You may need to refresh this page to see the imported data.
In addition, the results of the import are displayed in the Tariscope log.
Defining corporate calls in Tariscope Enterprise
Tariscope Enterprise (SoftPI) is a telephone accounting system. This system has many various functions that allow you to control the costs of telephone calls, optimally spend money on communication channels, detect unanswered calls and much more. Among these features is the feature to easily identify corporate calls. This feature can be useful for those users who have two or more PBXs and who want to control calls within the telephone network.
Consider a system of two telephone systems connected to each other (Figure 1).
Figure 1
There are two PBXs interconnected by Route 1 (group of lines). PBX 2 has access to the public switched telephone network (PSTN) via Route 2. That is, calls from PBX 1 to the PSTN are made via PBX 2. Call data collection is performed from PBX 2. If, for example, a subscriber with extension ‘202’ calls a subscriber of PBX 1 at extension ‘101’, then the CDR (Call Detail Record) data will show that this is an outgoing external call, because it was made outside PBX 2. That is, in the Tariscope system, such a call will be defined as a local call by default. The same situation will occur if CDR data collection is performed from PBX 1, and, for example, a subscriber with extension ‘102’ makes a call to subscriber with extension ‘201’.
In order for such calls in the corporate telephone network to be identified as corporate (internal) calls in the Tariscope system, settings must be made that will allow the system to know that the route connects two corporate PBXs.
Let's consider what settings need to be made in Tariscope for correct processing and billing calls.
- In Tariscope, you need to create two PBXs, although for our example, the basic settings need to be performed only for one PBX (PBX 2).
- You need to determine which route number on PBX 2 is used to connect the two PBXs. You need to add this route, set it to work as a Remote cabinet, and in the Equipment Network setting for PBX 2, set it to connect to PBX 1 and set the call type for such calls to "Corporate".
- All extensions of subscribers of both PBXs are added only for the second PBX.
So, we create two PBXs in Tariscope, an example of which is shown in Figure 2.
Figure 2
CDR data collection will be performed from the PBX named CS1000two. This PBX uses route 11 (line group) to connect to the PBX named CS1000. Therefore, on the Routes and Gateways settings page of the CS1000two PBX, we add this route (Figure 3).
Figure 3
After creating this route, select the corresponding line on the Routes and Gateways page (Figure 4) and click on the Equipment Network icon on the toolbar.
Figure 4
As a result, a corresponding window appears, where in the Equipment list, select the name of the PBX to which this route connects, and in the Call Type list, select the Corporate value (Figure 5).
Then save this setting.
Figure 5
Again, you need to select the line with this route and click on the Transit Call Tariffication icon on the toolbar. The corresponding window will appear, as shown in Figure 6, where you need to enable the Remote cabinet switch.
Figure 6
As a result of these settings, the line with the route connecting the two PBXs will look similar to that shown in Figure 7.
Figure 7
To correctly charge subscribers for the PBX named CS1000two, create a numbering plan on the settings page with that name. This numbering plan must include all extensions (internal telephone numbers) of subscribers of both PBXs.
Next, we add subscribers to Tariscope with their extensions. All extensions in our example must belong to the PBX with the CS1000two name.
For Tariscope to work properly, you must also perform all the other settings described in the Tariscope 4.6. Administrator's Guide document. These settings are not covered in this article.
An example of processed CDR data is shown in Figure 8.
Figure 8
As can be seen from this figure, calls from one PBX, where numbers start with 1, to another PBX, where numbers start with 2, and vice versa, are defined in Tariscope as corporate.
Other corporate telephone network configurations are possible. For example, if in Figure 1 an IP telephone system is used as PBX 2, then instead of the route connecting the PBX, you must determine the IP address of the gateway through which such a connection is made and enter this IP address instead of the route number (Figure 3).
If you have more than two PBXs in your corporate telephone network, then settings similar to those described in this article must be made for all routes (gateways) used to connect PBXs to the network.
In addition, for all PBXs that have a separate access to the public switched telephone network, you must organize separate CDR data collection. In this case, the Remote cabinet switch should not be enabled in the routes (gateways) to these PBXs, the extensions (internal telephone numbers) of subscribers must belong to these PBXs, and numbering plans must be created separately for all such PBXs.
Tariscope and Active Directory
This article is primarily intended for users of the Tariscope Enterprise software (hereafter referred to as Tariscope).
The Tariscope system is used to process call information. Determining who made a specific phone call, as well as its cost, is performed only if the directory number is linked to a specific subscriber in Tariscope. Therefore, there is a need for the correct setting of subscriber parameters and constant support in the current state of such information. Tariscope allows you to do this in three ways:
- Manually adjust subscriber data.
- Import subscriber data from external files.
- Receive such data from the Active Directory.
If you have an Active Directory in your company, then using it will probably be the most optimal option for maintaining information about subscribers in an up-to-date state.
The basic information required for Tariscope Enterprise is:
- the subscriber's name,
- the unit where he works,
- his directory numbers (DNs),
- the date from which the employee (subscriber) works in the company and from which he owns DNs,
- release date, that is, the date when the DNs phone numbers no longer belong to this subscriber.
To get data into Tariscope from the Active Directory, a profile is created in Tariscope that specifies which Active Directory attributes are linked to Tariscope database fields. The name of the employee, his department and directory numbers are specified in the main attributes of the Active Directory. As a rule, additional attributes are created for the dates of employment and dismissal. If necessary, you can import other attributes into Tariscope, which are required for generating reports on the use of telecommunication systems (PBXs).
In order for Tariscope to work with additional attributes, you need to add their list separated by commas on the System Variables page to the AdditionalADAttributes parameter. For a detailed description of how to create a Tariscope synchronization profile with Active Directory, see the page.
Data import from AD can be performed either at the initiative of the Tariscope administrator from the Active Directory or Subscribers pages, or automatically according to the schedule by the Tariscope Tasks when creating the corresponding task.
The last option, i.e. the use of the Tariscope Tasks allows you to completely automate the process of obtaining data from the Active Directory, even if some of this data needs to be refined. An example of updating data can be a change of directory number. For example, directory numbers are stored in AD together with access codes to various PBXs, and access codes are not displayed in CDR data. In this case, in order to correctly identify the subscriber who made the call, the access code must be removed from the imported phone number. To finalize the data, you can use the RDL report, which will be launched after data synchronization with the Active Directory and perform the necessary actions with the Tariscope database data. This report may not display any data, but only perform actions with the database.
Thus, if you are a user of the Tariscope Enterprise software and your company uses the Active Directory, its data can be synchronized with the Tariscope database daily in automatic mode, thereby providing Tariscope with up-to-date subscriber data.
