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.