Створення спеціальних форм звітів для 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 виберіть значення uk-UA.
Серед властивостей звіту зверніть також увагу на розмір звіту. Для цього використовуються наступні параметри: 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.
