Конструктор отчетов

Обзор

Конструктор отчетов является инструментом, который позволяет легко создавать отчеты, а также выгружать необходимые данные напрямую с БД. Сложность создаваемых отчетов полностью зависит от уровня знаний SQL пользователем.

Чтобы открыть конструктор отчетов необходимо перейти у вкладку "Еще-Отчеты" и выбрать "Конструктор":

Open

После нажатия откроется окно создания/изменения отчетов.

Окно выбора отчета

В этом окне пользователь может либо создать новый отчет, нажав кнопку "Новый", либо проводить действия з уже существующими отчетами.

Чтобы изменить существующий отчет - его необходимо выбрать со списка в поле "Отчет": В этом окне отчеты разбиты по категориям. После выбора нужного отчета для пользователя станут активны действия с ним. С отчетом можно совершать следующие базовые действия:

• Open

  

  Изменить - необходимо нажать кнопку "Изменить" - откроется конструктор создания отчета, с уже заполненными данными из выбранного отчета. Работа с конструктором описана ниже;

• Сохранить - необходимо нажать кнопку "Сохранить" - сохраняет изменения в отчет, кнопка неактивна, если нету изменений;

• Отмена действий - необходимо нажать кнопку "Отмена" - отменяет внесенные изменения в отчет, кнопка неактивна, если нету изменений;

Также есть набор дополнительный действий по администрированию созданных отчетов, для вызова меню с действиями необходимо нажать на . После нажатия откроется окно с возможными действиями:

• База данных - открывает базу данных, детально описано в разделе Работа з базой данных;

• Сохранить как - позволяет повторно сохранить отчет с новым названием, даже если не было изменений;

• Изменить категорию - позволяет переместить отчет в другую категорию (например Сток, Продажи, Анализ OOS, и тд.);

• Расписание - позволяет настроить рассылку отчета на электронную почту. Настройка производится в следующем окне:В этом окне пользователь выбирает день недели, время, кому отправлять, какое оглавления и текст письма должны быть. К настроенному письму будет прикреплено файл с данными с отчета.

• Доступ - позволяет выбрать у кого будет доступ к отчету:"Только я" - доступ только у пользователя который создал;"Только администраторы" - доступ только у пользователей с правами администратора;"Все" - доступ открыт для всех пользователей;

• Экспорт отчета - позволяет выгрузить выбранный отчет в excel;

• Импорт отчета - позволяет загрузить отчет в виде sql запроса из файла.

Базовые SQL запросы

Создание отчета

После нажатия "Новый" в окне выбора отчета откроется конструктор нового отчета:

В конструкторе отчета есть три блока:

• Скрипт, категория - блок в котором прописывается сам SQL запрос. Описан в разделе Работа с запросом;

• Таблица - блок в котором добавляются и настраиваются колонки отчета. Описан в разделе Работа с колонками;

• Параметры - блок в котором настраиваются изменяемые параметры для запуска отчета. Описан в разделе Работа с параметрами;

Работа с запросами

В этом блоке пользователь прописывает sql запрос, который должен будет выгрузить данные с таблиц в БД. Запросы пишутся на языке SQL. Для написания запросов необходимо иметь базовый навык программирования на SQL. Для получения базового навыка есть множество вводных курсов по SQL в интернете. Выучить SQL на базовом уровне можно в кратчайшие сроки (до 1 дня).

Базовые запросы, необходимые для создания базовых отчетов описаны в разделе Базовые SQL запросы.

В этом блоке документации описаны возможны действия с уже написанным блоком sql кода, работа с базовыми запросами, которые необходимы для создания базовых отчетов описана в разделе Базовые SQL запросы.

Код SQL запроса вводится в блоке "Скрипт, Категория:":

После того, как код скрипта написан с ним возможны следующие операции:

• Запуск для проверки - для запуска отчета необходимо нажать кнопку . После нажатия кнопки начнется выполнения скрипта, оно может занять от нескольких секунд до минут, в зависимости от сложности и объема данных, которые выгружаются. Если компилятор кода увидит ошибку в коде - он её выведет внизу в красном блоке:
  
  

  Если ошибок нету - внизу окна появится сообщение, что отчет загружается, и по окончании загрузки откроется окно с результатом запроса. Если запрос теоретически может выводить более 10000 тыс строк, желательно для его проверки использовать ограничения TOP 1000 или TOP 10000 после команды SELECT;

• Остановка отчета - если отчет очень долго грузится, или же вы просто решили остановить выполнения - это можно сделать нажав на ◼︎. Эта кнопка становится красной и активной, когда запущено выполнение отчета.

• Перенос выбранных в SELECT полей в колонки отчета. Колонки в отчет добавляются всегда с запроса. Для добавления колонок необходимо нажать кнопку . После нажатия система сама определит колонки и их названия и добавит их к отчету:
  
  

  Также эти колонки система выведет в блоке "Таблица", где вы сможете их настроить. Настройка колонок описана в разделе "Работа с колонками". Система автоматически добавляет все колонки. Ненужные удаляются нажатием на ❎. Позже их можно будет добавить с блока "Таблица".

• Перенос параметров отчета в поле "Параметры" для настройки. Для переноса параметров необходимо нажать ➡️. После нажатия система сама определит что является параметрами и перенесет их названия в поле "Параметры". Не перенесенные параметры отображаются в редакторе sql запроса красным цветом, а перенесенные - зеленым. Каждый параметр необходимо настроить, как их настраивать и в каких форматах могут быть параметры в коде sql запроса описано в блоке "Работа с параметрами.

Работа с колонками

Все поля выбранные через SELECT в sql запросе отображаются в двух местах - сверху над конструктором и в блоке "Таблица":

Изначально все поля автоматически добавляются в отчет после нажатия кнопки  в окне ⬆️ sql запроса, и отображаются сверху над конструктором и в блоке "Таблица". После удаления любого поля, его можно потом добавить обратно нажав на + возле его названия в блоке "Таблицы". 
Также можно менять очередность отображения полей - просто перетаскивая их названия в верхнем блоке. Также можно менять и размер колонок - растягивая их. Вид колонок в верхнем блоке полностью отвечает тому, как они отобразятся и в отчете.

Для изменения параметров поля необходимо нажать на его название - после этого откроется окно со следующими параметрами:

• Style All - редактирования общего стиля отчета. Процесс аналогичен редактированию в любом другом текстовом редакторе;

• Expand by - детально это поле описано в разделе Extend By и Group By;

  Group by - детально это поле описано в разделе Extend By и Group By;

• Название - пользователь задает как будет называться эта колонка в отчете;

• Код поля - указывает какие данные тянуть в колонку. Например есть запрос:
  SELECT SKU_ID SKU, STORE_ID STORE FROM SALES_SKU
  Если в настройках колонки SKU поставить код поля STORE, то в отчете в колонке SKU будут отображаться данные из колонки STORE. Но это возможно только если отображается одна колонка SKU, если отображаются колонки и SKU и STORE - тогда добавить код поля не получится.
  Нельзя добавлять код поля на то же поле.

• Тип данных - выбор какой тип данных должен отображаться в поле. Поддерживаются следующие типы данных:

  • Текст - для текстовых полей - например "Название товара";

  • Число - для числовых полей - например "Продажи в штуках";

  • Дата - для полей с любым форматом даты - например "Неделя продаж" или "Дата продажи";

• Формат - это поле появляется если выбрать тип данных "Число" или "Дата".

• Ссылка - позволяет добавить ссылку на другую страницу, данные или что то еще. Например есть запрос:
  SELECT SKU_ID SKU, SITE_LINK LINK FROM MONITORING
  Колонка SKU выводит кода товаров, а колонка LINK ссылки на сайт. В этом случае если в настройках колонки SKU выбрать в поле "Ссылка" ссылку на LINK - то потом в отчете при нажатии на номер в колонке SKU будет открываться ссылка из колонки LINK.
  Ссылку можно добавлять только на другие колонки;

• Ширина - пользователь может ограничить возможною ширину поля в отчете. Значение нужно вводить в пикселях. Если ширина auto, то поле будет растянуто до размеров текста.

• Style Сell - редактирование отдельного стиля для отображения информации в отчете. Процесс аналогичен редактированию в любом другом текстовом редакторе;

Extend By и Group By

Эти два поля настройки колонки всегда задаются вместе. Эти параметры позволяют реализовать кросс-колонки - когда в одной колонке отображаются множественные данные из других колонок.

Например это позволяет вывести данные о продажах товара за несколько недель в одной строке, при этом не добавляя вручную отдельную колонку для каждой недели. Более детально этот функционал будет объяснен в примере ниже.

• Пользователь создал запрос для вывода продаж на первой недели за последние 5 лет для выбранного товара 56 по всем магазинам (Магазин 1 и Магазин 2):

• В результате он получил следующий отчет:
  
  

  Можно увидеть, что отчет вывел 10 строк, при этом в не очень удобном формате. Намного удобней было бы, если бы все недели и продажи отображались в одной строке с товаром, тогда было бы только две строки, для каждого магазина. Вот в таком виде:

• Для таких трансформаций необходимо пользоваться Expand By. Для начала необходимо убрать из отображения колонку, данные из которой необходимо будет развернуть и вставить в другую колонку. В этом примере - это колонка с неделями - WEEK WK. Поэтому необходимо сверху в строке колонок нажать на  возле колонки WEEK;

• Следующий шаг - нажать на  возле колонки в которой необходимо отобразить другую колонку. В нашем случае это колонки Sales и Stock - поэтому нажимаем на кнопку возле обеих колонок - после нажатия отобразится какая колонка должна отображаться внутри - в нашем случае это WK (Week);

• После этого WK также отображается в поле Expand By в настройках соответствующих колонок. Для Expand by можно использовать любые колонки, которые не отображаются в отчете, но при этом есть в запросе. Дальше необходимо настроить по какой колонке будут группироваться данные. В этом примере необходимо, чтобы данные группировались по магазинам - то есть в поле Group by необходимо выбрать STR (название колонки, которая вытягивает данные по магазинам в sql запросе). Выбирать Group by можно только для одной колонки, в другие колонки с Expand by этот выбор подтянется автоматически. 

• После завершения всех настроек можно нажать на ▶️ и увидеть как отображаются данные:
  
  

Работа с параметрами

Параметры - это величины, которые пользователь выбирает во время запуска отчета. Это позволяет выбирать только те данные, которые соответствуют критериям пользователя.

Параметры автоматически переносятся с кода в sql редакторе в блок "Параметры" после нажатия на кнопку . После того как параметры появились в блоке "Параметры" их можно настроить.

Параметры бывают двух типов:

• :parameter - такие параметры передают одно конкретное значение и в коде их нужно указывать как :
  where variable = :parameter;

  [parameter_list] - такие параметры передают набор значений и в коде их нужно указывать как :
  where variable [parameter_list];

Например, если вам необходимо передать число "5" как параметр вы используете формат :parameter после знака равно. В результате после запуска отчета параметр сработает в коде как:
where value = 5.

Если необходимо передать набор - например (1,3,8), тогда вы используете формат [parameter_list] без знака равно. В результате после запуска отчета параметр сработает в коде как:
where value in (1,3,8).

Названия параметров можно использовать любые удобные, главное использовать правильный формат.

Для настройки параметра необходимо нажать на название параметра в блоке "Параметры". После нажатия откроется окно со следующими настройками параметра:

• ID - идентификатор параметра - он подтягивается автоматически в соответствии с полем, к которому применяется, он не может изменятся;

• Название - название параметра - должно быть равно названию параметра в коде;

• Показывать - название параметра, которое будет отображаться при запуске отчета;

• Тип - выбор типа параметра - очень важно указать правильный тип, для корректного срабатывания параметра и избежания ошибок. Поддерживаются следующие типы параметров:

  • Текст - параметр будет передавать текстовое значение, может использоваться только с форматом параметра :parameter;

  • Число - параметр будет передавать числовое значение, может использоваться только с форматом параметра :parameter;

  • Дата - параметр будет передавать дату в формате ГГГГ-MM-ДД, может использоваться только с форматом параметра :parameter;

  • Неделя - параметр будет передавать дату в формате ISO неделя - ГГГГНН, может использоваться только с форматом параметра :parameter;

  • Месяц - параметр будет передавать дату в формате ISO месяц - ГГГГММ, может использоваться только с форматом параметра :parameter;

  • Перечень - параметр будет передавать набор значений в любом формате. Используется если необходимо передавать не одно значение, а несколько. Например (1, 3, 8) или (рц, магазин, склад) или (202015, 202025). Может использоваться только с форматом параметра [parameter_list];

  • Список - параметр будет передавать набор значений выбранных специальным запросом, который указывается в поле "Запрос". Может использоваться только с форматом параметра :parameter;

• Min - минимальное значения параметра. Может использоваться для чисел, дат, недель, месяцев;

• Max - максимальное значения параметра. Может использоваться для чисел, дат, недель, месяцев;

• Запрос - заполняется только для формата "Список". Для отображения возможных запросов необходимо ввести в поле текст 'select', доступные запросы автоматически появятся списком. При запуске отчета информация подтянутая с запроса будет отображаться выпадающим списком при выборе значения параметра. Сейчас автоматически поддерживаются следующие запросы (но также можно создавать дополнительные):

  • report_select_stores - используется для выбора магазина из списка для значения параметра;

  • report_select_groups - используется для выбора группы из списка для значения параметра;

  • report_select_managers - используется для выбора менеджера из списка для значения параметра;

  • report_select_contracts - используется для выбора контракта из списка для значения параметра;

  • report_select_suppliers - используется для выбора поставщика из списка для значения параметра;

  • report_select_whs - используется для выбора склада из списка для значения параметра;

  • report_select_fcst - используется для выбора номера прогноза из списка для значения параметра;

• Индекс - может заводится только для параметров типа Дата, Неделя, Месяц. Индекс позволяет автоматически отнимать или добавлять к параметру значение.
  Так если поставить параметр Дата и индекс -10, то за умолчанием при запуске отчета будет отображаться значение даты на 10 дней меньше заданной даты по умолчанию в поле "Значение". Если поле значение оставить пустым - дата будет отниматься от текущей;

• Значение - вводится значение по умолчанию, которое будет отображаться при выборе параметров перед запуском отчета.

Стандартные кейсы применения параметров в sql запросе

Ниже приведены варианты задания параметров в sql запросе для разных типов данных:

where sku_id = :sku_id - применяется для выбора конкретного ску по коду Майсейлз. При запуске отчета в параметр передается один номер ску. Тип параметра должен быть "Число";
where sku_id [sku_id_list] - применяется для выбора нескольких ску по коду Майсейлз. При запуске отчета в параметр передается набор номеров ску через запятую. Тип параметра должен быть "Перечень";
where item_code = :item_code - применяется для выбора конкретного ску по коду УПП. При запуске отчета в параметр передается один текстовый код ску. Тип параметра должен быть "Текст";
where item_code [item_code_list] - применяется для выбора нескольких ску по коду УПП. При запуске отчета в параметр передается набор текстовых кодов ску УПП через запятую. Тип параметра должен быть "Перечень";
where ((sku_id = :sku_id) or (:sku_id = 0)) - сложный параметр - используется для возможности выводить все ску или один конкретный. В этом случае чтобы вывести все ску проставляется значение 0 для параметра при запуске отчета, или же номер конкретного ску для вывода одного ску;

Выше приведенные примеры задания параметров в коде актуальны для всех типов параметров и полей.

Работа с базой данных

Для работы с конструктором отчетов очень важно понимать структуру базы данных, чтобы понимать какие данные получать из каких таблиц, какие есть названия полей, как связать данные с разных таблиц.

Вызвать структуру базы можно нажав на  и выбрав "База данных". После выбора откроется окно, в которой будут отображаться все таблицы и все поля в них.

В окне базы данных работает поиск, который позволяет найти нужные таблицы и колонки.