Создание специальных форм отчетов для CUCM
Эта статья предназначена для пользователей биллинговой системы Tariscope, использующих ее для анализа вызовов, выполненных через Cisco Unified Communications Manager (CUCM), и желающих получить какую-либо дополнительную информацию о вызовах, содержащуюся в CDR файлах, но отсутствующую в представлениях для вызовов и стандартных формах отчетов системы Tariscope. Также эта статья может быть полезна всем пользователям Tariscope как пример по самостоятельному созданию необходимых форм отчетов.
CDR файл от CUCM содержит много полей, большинство из которых не попадает в базу данных Tariscope, если в настройках этой телефонной системы не указано зберигать все CDR поля. В некоторых случаях те CDR поля, которые не обрабатываются, могут представлять интерес. К примеру, это может быть полезно, если вы хотите анализировать, какая из сторон телефонного разговора прекратила вызов. Об этом случае есть отдельная статья на сайте.
Другой пример, это анализ кодов завершения вызовов. Информация об этом не нуждается в хранении всех полей, и этому вопросу посвящена следующая статья на сайте. Но если вы хотите генерировать периодические отчеты по кодам завершения вызовов, то предлагаем дальше прочитать статью, где мы рассмотрим, как создать необходимую форму отчета.
Для создания форм отчетов для Tariscope следует использовать бесплатную программу Microsoft Report Builder. Інсталляцию этой программы можно загрузить з сайта компании Microsoft.
В этой статье невозможно рассмотреть все возможности этой программы. Для этого обратитесь к документации на сайте Microsoft. Мы рассмотрим только основные шаги, которые следует выполнить для создания формы отчета по кодам завершения вызовов. Будем считать, что нам нужно создать фору отчета, с помощью которой будут формироваться отчеты за заданный период и будут отображаться коды завершения вызовов и количество вызовов соответствующего кода. Данные отчета должны быть упорядочены по кодам окончания вызова.
Заметим, что в основе формы отчета используется запрос SQL или целая программа с использованием языка Transact-SQL (T-SQL). Кроме этого нужно знать, где в базе данных Tariscope находятся нужные данные. Такую информацию можно найти в документе “Tariscope 4.6. Каталог базы данных”. Этот документ предоставляется только официальным пользователям Tariscope. Для этого отправьте запрос в службу поддержки компании SoftPI.
После запуска программы Microsoft Report Builder появляется окно, которое предлагает варианты создания формы отчета с помощью различных мастеров или используя Blank Report (пустой отчет). Выберем последний вариант, окно программы примет вид, как на рисунке 1.
Рисунок 1
Окно программы содержит меню, а також окна: Report Data, формы отчета и Properties.
Меню программы состоит из следующих пунктов:
- File. Позволяет открыть существующую форму отчета или сохранить форму отчета.
- Home. Отображается панель инструментов для редактирования элементов отчета, а также кнопку Run для запуска формирования отчета.
- Insert. Отображается панель с элементами, которые можно вставить в форму отчета.
- View. Отображается панель инструментов для выбора отображаемых окон программы.
Окно Properties отображает особенности, которые относятся ко всей форме отчета. Рекомендуется сразу задать некоторые свойства отчета, такие как автор (Author), описание (Description) и язык (Language).
Первые два параметра – информативные и необязательны для задания, хотя мы рекомендуем их использовать. А вот отсутствие конкретного языка может привести к тому, что отчет не будет работать. Поэтому для параметра Language выберите значение, например, ru-KZ, для Казахстана.
Среди свойств отчета обратите внимание на размер отчета. Для этого используются следующие параметры: ActiveSize, Margins, PageSize. При необходимости можно изменить эти параметры.
На форме отчета по умолчанию уже вставлены сверху раздел Report Title (Заголовок отчета) и снизу колонтитул страницы (Page Footer), в котором отображается встроенный параметр (Build-in-Fieled), отображающий время выполнения запроса к серверу SQL. Этот параметр называется Execution Time.
Список других встроенных полей, которые можно использовать в форме отчета, можно увидеть, если в окне Report Data открыть ветвь Build-in-Fields.
Если вас не интересует время выполнения запроса, щелкните по контуру поля &ExecutionTime и удалите его. Если вы не планируете отображать какие-либо данные в нижнем колонтитуле, то его также можно удалить. Для этого в его свойствах для параметра Height (высота) задайте 0. Можно также удалить раздел Report Title, а для заголовка отчета вставить текстовое поле. Для этого в меню программы выберите Insert (вставить) и щелкните по иконке Text Box (текстовое поле) и в форме отчета выберите область, где это поле будет находиться. Затем введите в нем название отчета (рисунок 2).
Рисунок 2
Зададим параметры отчета: дату и время, начиная с которых будут анализироваться данные вызовов, и дату и время, до которых будут анализироваться данные вызовов. Для этого в появившемся окне New выберите Parameters и щелкните правой кнопкой мыши. Появится пункт Add Parameter (Добавить параметр), щелкните по нему. В результате появится окно Report Parameter Properties (свойства параметра отчета).
В этом окне в позиции Name введите имя параметра. Например, для первого параметра будь то FromDate. В позиции Prompt зададим, например, фразу “From date, time”(С даты, времени). И в списке Data Type (тип данных) выберем пункт Date/Time. После чего щелкните по кнопке OK для сохранения данных параметра.
Повторим такие же действия для второго параметра (Рисунок 3).
Рисунок 3
Следующим шагом создания формы отчета будет создание запроса SQL для получения необходимых данных. Для этого следует понять, где эти данные брать. Основные параметры вызова хранятся в таблице Calls или в представлении viCalls. Как уже упоминалось выше, такую информацию можно найти в документе “Tariscope 4.6. Каталог баз данных». Как видно из описания таблицы Calls этого документа, в таблице содержится поле ReleaseCause, в котором хранится код причины завершения вызова. Также из данной таблицы для сотворения SQL запроса будет нужно поле CallDateTime, в котором сохраняется дата и время начала вызовов.
Для создания SQL запроса в форме отчета сначала необходимо указать источник данных. В нашем случае это база данных Tariscope. Для этого в открывшемся окне Report Data выберите Data Sources и щелкните правой кнопкой мыши, далее щелкните по Add Data Source. Появится окно Data Source Properties (рисунок 4), где необходимо указать параметры подключения к SQL серверу и базе данных Tariscope.
Рисунок 4
В позиции Name можете оставить название DataSource1 или заменить, например, Tariscope.
Выберите Use a connection embedded in my report.
В списке Select connection type оставляем избранный пункт Microsoft SQL Server.
В позиции Connection string нужно задать строчку подключения к базе Tariscope. В зависимости от того, на каком компьютере вы создаете форму отчета, эта строка подключения может быть разной.
Одним из вариантов строки подключения к базе данных Tariscope при создании формы отчета на том же компьютере, где установлен SQL сервер, будет следующее:
Data Source=.\\;Initial Catalog=Tariscope
Для того чтобы убедиться, что эта строка введена правильно, щелкните по кнопке Test connection. Если все введено правильно, вы получите окно с надписью: Connection created successfully. Если вы получили ошибку при тестовом подключении, проверьте введенные данные и исправьте ошибку.
Щелкните по кнопке OK, чтобы сохранить параметры настройки строки подключения к базе данных.
В открывшемся окне Report Data выберите пункт Datasets и затем щелкните правой кнопкой мыши, далее щелкните по Add Dataset.
Появится окно Dataset Properties (Рисунок 5).
Рисунок 5
В позиции Name можно оставить название набора данных или задать свое название, например CallTerminationCodes.
Выберите пункт Use a dataset embedded in my report. Окно Dataset Properties несколько изменит вид
(рисунок 6).
Рисунок 6
В списке Data source выберите название источника данных, созданного на предыдущем шаге. В нашем примере это источник данных Tariscope.
В позиции Query введите SQL запрос, который будет получать нужные данные из базы данных Tariscope
Одним из вариантов SQL запросов, отображающим код причины завершения вызова и количество вызовов с таким кодом за заданный промежуток времени, будет следующее:
SELECT ReleaseCause, COUNT(ID) AS TerminationCodeNumber
FROM Calls
WHERE CallDateTime BETWEEN @FromDate AND @ToDate
GROUP BY ReleaseCause
ORDER BY ReleaseCause
Первая строка этого запроса обеспечивает выбор кода окончания вызова (ReleaseCause) и количества, сколько раз каждый код встречается (TerminationCodeNumber).
Вторая строка запроса указывает, что данные выбираются из таблицы Calls.
Третья строка указывает, что данные выбираются за период, где начало вызова (поле CallDateTime) находится в промежутке времени, которое задается параметрами начала периода (@FromDate) и окончания периода (@ToDate).
Четвертая строка обеспечивает группировку данных по полю причины завершения вызова (ReleaseCause).
Последняя, пятая строка упорядочивает результаты запроса по значению поля ReleaseCause.
Приведенный SQL запрос будет работать корректно, если у вас вместе с Tariscope используется только одна телефонная система, CUCM. Если у вас несколько телефонных систем, но CUCM только один, то выбор данных именно по его вызовам можно напрямую задать в условиях SQL запроса. Для этого следует определить, какой идентификатор имеет CUCM в системе Tariscope. Для этого в Tariscope откройте страницу Управление устройствами. Таблица устройств содержит идентификаторы (ID) телефонных систем. Найдите ID, принадлежащий CUCM. Например, это ID = 320. В этом случае приведенный выше SQL запрос следует записать следующим образом:
SELECT ReleaseCause, COUNT(ID) AS TerminationCodeNumber
FROM Calls
WHERE CallDateTime BETWEEN @FromDate AND @ToDate
AND PBXID = 320
GROUP BY ReleaseCause
ORDER BY ReleaseCause
В этом запросе выделена часть, добавленная. То есть добавлено условие, что обрабатываются вызовы только от АТС с идентификатором 320. В том случае, если у вас несколько телефонных систем, и вы по каждой из них хотите получать отдельный отчет, то нужно в форму отчета добавить еще один параметр: ID АТС. Тип данных для этого параметра должен быть: integer. И если мы его назвали PBXID, то SQL запрос, учитывающий АТС, будет выглядеть следующим образом:
SELECT ReleaseCause, COUNT(ID) AS TerminationCodeNumber
FROM Calls
WHERE CallDateTime BETWEEN @FromDate AND @ToDate
AND PBXID = @PBXID
GROUP BY ReleaseCause
ORDER BY ReleaseCause
Рекомендуем всегда перед написанием запроса в этом окне убедиться, что этот запрос работает правильно. Для этого можно использовать либо страницу SQL-запроса в Tariscope, либо Microsoft SQL Server Management Studio (SSMS). При такой проверке следует иметь в виду, что параметры формы отчета следует указать конкретные данные. К примеру, наш последний запрос мы можем проверить на данных за 01.09.2024 года для АТС с ID = 320. Для этого запрос должен выглядеть следующим образом:
SELECT ReleaseCause, COUNT(ID) AS TerminationCodeNumber
FROM Calls
WHERE CallDateTime BETWEEN '2024-09-01 00:00:00' AND '2024-09-01 23:59:00' AND
PBXTD =320
GROUP BY ReleaseCause
ORDER BY ReleaseCause
После того, как вы убедились, что запрос работает корректно и вставили его в окно Dataset Properties, сохраните этот запрос, щелкнув по OK в этом окне.
Теперь добавим в форму отчета таблицу, где будут отображаться результаты запроса SQL. Для этого в меню программы Microsoft Report Builder нужно выбрать пункт Insert, а затем щелкнуть на панели инструментов по иконке Table -> Insert table. После чего на форме отчета указать место для таблицы. Форма отчета будет выглядеть подобно тому, что указан на рисунке 7.
Рисунок 7
При этом окно Properties отображает параметры этой таблицы. Следует связать таблицу с набором данных (dataset), который мы предварительно создали. Для этого для параметра таблицы DataSetName следует выбрать набор данных CallTerminationCodes.
Таблица, которую мы вставили, содержит 3 столбца. Но в нашем запросе у нас есть только два поля. Поэтому один из столбцов таблицы нужно удалить. Для этого следует щелкнуть по строке заголовка столбца, появится обрамление таблицы, щелкнуть по этому обрамлению, что приведет к возникновению меню (рисунок 8).
Рисунок 8
Выберите Delete Columns, этот столбец будет удален. Есть и другие варианты удаления столбца.
В оставшихся столбцах введем заголовки, а в следующей строке выберем поля отображаемых данных (рисунок 9).
Рисунок 9
После этого можно проверить формирование отчета. Для этого на панели инструментов приложения щелкните по иконке Run.
В результате чего программа примет вид, как показано на рисунке 10.
Рисунок 10
В позиции "С даты, времени" введите дату и время начала периода в формате: день. месяц. год часа: минуты: секунды. Например: 01.09.2024 00:00:00
В позиции "До даты, времени" введите дату и время окончания периода в формате: день.месяц.год часа:минуты:секунды. Например: 01.09.2024 23:59:59.
В позиции ID АТС введите идентификатор АТС. К примеру: 320.
Щелкните по кнопке View Report. Если все сделано корректно, отобразится отчет. Пример такого отчета представлен на рисунке 11.
Рисунок 11
Мы получили тот результат, которого и ожидали. Но отчет можно усовершенствовать. К примеру, указать период, за который он содержит данные; добавить описание кода завершения вызова, указать общее количество вызовов и прочее.
Давайте добавим данные по периоду, за который формируется отчет. Эти данные содержатся в параметрах FromDate и ToDate, которые мы создали. Добавим в форму отчета Text Box по аналогии как добавляли таблицу. Выберем эту текстовую позицию на форме отчета и щелкните правой кнопкой мыши. Появится меню, в котором нужно выбрать значение Expression. Это приводит к открытию окна Expression. В этом окне следует ввести выражение, пример которого показан на рисунке 12 и щелкнуть ОК.
Рисунок 12
Если после этого выполнить формирование отчета, теперь отчет будет содержать данные о периоде, показанном на примере в рисунке 13.
Рисунок 13
Теперь добавим результат количества вызовов, которые попадают в отчет. Для этого нужно стать на форме отчета в строке таблицы, где выводятся данные. Щелкнуть правой кнопкой мыши, в появившемся меню выбрать:
Insert Row -> Outside Group – Below. Появится новая строка. В этой строке в столбце Код завершения можно ввести, например: Всего, а в столбце Количество щелкнуть правой кнопкой мыши и выбрать в меню пункт Expression. В появившемся окне Expression введите следующее выражение:
SUM(CInt(Fields!TerminationCodeNumber.Value))
Это выражение суммирует значение поля TerminationCodeNumber с помощью функции SUM, которое предыдущее переводится в целое (integer) с помощью функции CInt.
Сформировав отчет с этими изменениями, получим результат, пример которого представлен на рисунке 14.
Рисунок 14
Теперь рассмотрим, как добавить в отчет описание кодов завершения вызова. Такая информация находится непосредственно в коде системы Tariscope, но она отсутствует в базе данных. Поэтому одним из вариантов является добавление такой информации в базу данных Tariscope. Для этого нужно иметь список кодов и их описания. Список кодов и их описание на украинском языке есть в статье на нашем сайте, таблице 1.
Скопируйте данные этой таблицы и вставьте в Excel в Лист. Первая строка пусть содержит названия столбцов (рисунок 15).
Рисунок 15
Сохраним эти данные в csv файле с названием: CallTerminationCauseCodes.csv
Название этого файла может быть любое, но оно не должно совпадать с каким-либо названием таблиц базы данных Tariscope.
Для создания таблицы с кодами завершения и их описанием воспользуемся программой Microsoft SQL Server Management Studio (SSMS). Следует подключиться в этой программе к SQL серверу с базой данных Tariscope. Выбрать строку базы Tariscope, щелкнуть правой кнопкой мыши и в появившемся меню выбрать:
Tasks -> Import Flat File. Откроется окно Import Flat File 'Tariscope'. Щелкните Next. Окно примет вид, как показано на рисунке 16.
Рисунок 16
Нажмите кнопку Browse и выберите созданный csv файл. В позиции New table name будет предложено имя файла. При желании вы можете изменить это название. Щелкните Next.. Окно отобразит предварительный просмотр данных. Если все устраивает, щелкните Next. Окно программы отобразит создаваемую структуру таблицы, пример чего приведен на рисунке 17.
Рисунок 17
При желании можете сделать поле Code в качестве Primary Key.
Щелкните Next, а затем Finish. В случае успешного импорта данных из файла csv появится информация: Operation Complete. После этого следует обновить данные и проверить наличие создания новой таблицы, в нашем примере это dbo.CallTerminationCauseCodes.
Теперь необходимо изменить SQL запрос формы отчета, чтобы учитывать данные этой таблицы в отчете. Одним из вариантов, как это сделать приводится ниже:
SELECT ReleaseCause, Min(Description) AS Description, COUNT(ID) AS
TerminationCodeNumber
FROM Calls
JOIN CallTerminationCauseCodes ON Code = ReleaseCause
WHERE CallDateTime BETWEEN @FromDate AND @ToDate
AND PBXID = @PBXID
GROUP BY ReleaseCause
ORDER BY ReleaseCause
После сохранения этого запроса необходимо добавить отображение значения поля Description в таблице формы отчета. Для этого нужно выбрать столбец Количество и щелкнуть правой кнопкой мыши. В появившемся меню выбрать: Insert Column -> Left. В заголовке столбца напишем: Описание. В строке значений выбираем Description (рисунок 18).
Рисунок 18
Если теперь сформировать этот отчет, то получим результат, подобный тому, что показан на рисунке 19.
Рисунок 19
Можно и дальше усовершенствовать эту форму отчета в зависимости от требований к нему.
Если этот отчет планируется формировать автоматически по расписанию с помощью Планировщика Tariscope, то снова нужно внести изменения в SQL запрос, чтобы в отчете автоматически формировался заданный промежуток времени по отношению к текущему времени, так как невозможно будет их задавать в качестве параметров, а также напрямую, без параметра, задавался идентификатор АТС. В зависимости от промежутка времени, за которое планируется формировать отчет, SQL запрос может несколько отличаться. Если, например, вы планируете формировать отчеты по кодам завершения звонков ежечасно, то в форме отчета следует удалить параметры, а SQL запрос написать следующим образом:
SELECT ReleaseCause, Min(Description) AS Description, COUNT(ID) AS
TerminationCodeNumber
FROM Calls
JOIN CallTerminationCauseCodes ON Code = ReleaseCause
WHERE CallDateTime BETWEEN DateAdd(hh, -1, GetDate()) AND GetDate()
AND PBXID = 320
GROUP BY ReleaseCause
ORDER BY ReleaseCause
В этом запросе вы должны изменить значение поля PBXID с 320 на идентификатор вашего CUCM. Определение текущего времени производится с помощью функции GetDate. Определение времени, которое было на час раньше текущего времени, используется функция DateAdd.
