Статья №1 "Основы" из Цикла "Конструктор отчетов в CyberFleet"

1. Принцип работы конструктора отчетов.

2. Запуск конструктора в работу

/****** Скрипт для команды SelectTopNRows из среды SSMS  ******/

declare @ReportBuilderEnabled varchar(8000)

SELECT @ReportBuilderEnabled=[Opt_Value]

  FROM [BN].[dbo].[SYS_GlobalOptions]

  where opt='ReportBuilderEnabled'

  if @ReportBuilderEnabled is null 

  insert into [BN].[dbo].[SYS_GlobalOptions] (Opt,[Opt_Value])

  values ('ReportBuilderEnabled','1')

  else if @ReportBuilderEnabled<>'1'

  update [BN].[dbo].[SYS_GlobalOptions]

  set [Opt_Value]=1

  where opt='ReportBuilderEnabled'

Скорость обработки зависит от многих параметров, и может прилично колебаться от секунд, до десятков минут (например для 600 ТС, с малым количеством зон, с примерно 400 ДУТ это составило для меня порядка 10-20 минут на 1-и обрабатываемые сутки).

2.4. Сам конструктор для своей работы использует файл connect.udl, размещенный в каталоге клиента киберФлита. Посему его запуск возможен и на отдельном компьютере (без установленного клиента CyberFleet). Я разместил два файла (M2MTelematics.ReportBuilder.exe, Connect.udl) в отдельном каталоге на другом компьютере, изменил файл Connect.UDL (в части адреса сервера SQL) и смог пользоваться конструктором. По умолчанию файл конструктора находится в каталоге Клиента CyberFleet (C:\Program Files\M2M\Cyber Fleet\Client).

3. Создание своего первого отчета

В качестве первого отчета мы построим свой отчет о стоянках всех ТС за период.

Для это, заходим в Конструктор отчетов (КО). Если все в порядке, то у вас появится картинка типа этой - то КО смог подключиться к базе данных

Окно состоит из списка доступных для редактирования отчетов, и действий с ними (меню).

Начинаем с "Создать отчет".

При этом открывается новое окно.

Окно содержит несколько закладок для работы: 

Свойства  - тут описывается название отчета, как отчет будет представляться в CyberFleet, и описываются параметры. В нижней части закладки "Свойства" приводится список Параметров ("Параметры")- то что будет указывать пользователь перед запуском отчета (например период работы, выбирать ТС и так далее).

Так как отчет оперирует Запросами, то на каждый запрос выводится отдельная закладка. В нашем случае запрос будет один, потому закладка тоже одна ("Новый запрос"). 

Данное окно основное при редактировании отчета. Заполните данные поля, как указано на рисунке ниже

Так как программа до сих пор находится в разработке, то не все функции работают корректно, потому лучше периодически нажимать "Сохранить изменения" - это позволит вам, если что-то пойдет не так закрыть все окна, переподключиться к КО заново, и начать с места последнего сохранения.

Перейдите на закладку "Новый запрос".

Если у вас будет несколько запросов, то лучше каждому запросу давать вменяемое имя (Вменяемые и понятные названия - это вообще хорошая привычка при программировании), посему мы назовем наш запрос (впишите в поле Заголовок) "Запрос стоянок" (как на рисунке ниже).

Каждый запрос содержит внутри себя настройку:

1. Источники данных - это таблицы (может быть и результатом работы процедуры какой-либо) из которых вы выбираете данные

2. Условия запроса - это ограничение выборки данных (например обычно вам нужны данные не за весь период, а за конкретную дату/время, да и список ТС можно ограничить)

3. Дополнительное условия группировки - позволяет уточнить группировочный запрос (в данной статье не описывается).

4. Закладка Поля - позволяет настроить выводимые из таблицы данных (запроса) колонки, управлять их порядком вывода, указывать в какой последовательности выводить данные вам требуется.

Для начала, добавим в "Источники данных" таблицу.

Отметьте таблицу "Стоянки ТС и СТ", нажмите ОК под описанием таблицы

Сохраните изменения.

Таблица была добавлена в список источников данных, и получила имя table1. 

Теперь перейдем на закладку "Поля", и укажем, какую именно информацию из нашей таблицы мы хотим получить.

Я добавил поля:

1. Гаражный номер

2. Дата и время начала стоянки

3. Дата и время конца стоянки

4. Продолжительность стоянки, сек

5. Описание местоположения

То что получилось приводится на рисунке ниже

Сохраняем изменения.

Что бы проверить, что наш отчет способен уже работать, нажмем кнопку SQL, и в появившемся окне нажмем "Получить первые 1000 результатов"

В результате, у вас должна появиться закладка "Результат", содержащая какой-то (не более 1000) количество строк с вашими данными.

Такая проверка (через SQL и получение данные) позволяет проверить и предварительно посмотреть результат отчета. Ее рекомендуется производить перед пробой запуска отчета при его настройке. Это практически единственный быстрой способ проверить, а нет ли ни где ошибки в вашем запросе.

 

На данном этапе вы УЖЕ можете попробовать запустить отчет на выполнение, однако, можете не дождаться результатов. Потому как мы ни где не ограничили период выборки данных (то, что в предыдущем окне выглядело быстро, при попытке построить вывести в excel выдало данные в размере нескольких десятков тысяч строк).

Все дело в том, что мы нигде не поставили условие выборки данных за период - т.е. наш отчет пытается получить все возможные данные, накопленные при агрегации за все дни. Количество этих данных для разных исходных таблицах может сильно различаться.

Как уже говорилось выше, дабы пользователь что-то указал в отчете используются Параметры. Нужно добавить два параметра типа "Дата и время", где пользователь укажет время начала и окончания для выборки.

На закладке "Свойства" добавьте два параметра:

ДатаС, ДатаПо, укажите их обязательность, укажите в "Отображать как" нужный текст (данное поле отображается пользователю перед формированием запроса). Так же укажите в "Начальное значение" даты - они нам помогут при тестировании, да и пользователю понятнее, если что-то в поле ввода сразу введено.

Теперь нам нужно уже запросу объяснить, что делать с данными параметрами, т.е. как данные параметры использовать при формировании запроса. Для этого вернемся в запрос, и укажем в "Условиях запроса" для поля "Дата и время начала", что оно больше чем ДатаС (>=) и меньше чем ДатаПо (<=). Должно получиться как на рисунке.

Последовательность нажатий: Поле - выбрать table1.Дата и время начала стоянки, Операции - выбрать >=, Параметр - выбрать ДатаС, Поле - выбрать table1.Дата и время начала стоянки, Операции - выбрать <=, Параметр - выбрать ДатаПо.

Сохраните изменения в отчете.

Перейдем на закладку SQL и снова проверим работу.

Обратите внимание, что скрипт запроса теперь изменился - туда введены переменные @ДатаС, @ДатаПо, которым указано значение по умолчанию. Вы можете указывать свои значения для тестового получения данных.

В принципе можно пробовать формировать отчет в CyberFleet (перезапустите клиента) - смотреть что получилось.

Если вы все делали правильно, то у вас должна появиться таблица Excel, примерно такого содержания:

Как вы заметили, вместо даты и времени стоит число (это представление excel о нашем дате и времени в виде числа). Для перевода в понятные нам нужно через формат ячейки изменить формат на время. Для этого выделяем колонку, правкой кнопкой вызываем Формат Ячейки, указываем время, указываем формат (как на рисунке)

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

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

Для того, что бы решить обе проблемы (не указывать каждый раз формат даты, и изменить названия колонок) вернемся в наш Конструктор Отчетов.

Перейдем в закладку запроса:

Самое простое действие - это изменить название колонок, для этого нужно в графе "Название" на закладке "Поля" указать нужные вам названия. Так же можно поменять с помощью стрелок последовательность следования колонок, или добавить/удалить колонки (список колонок из которых можно добавлять определяется опять же "Источниками").

Переименуйте колонки как указано на рисунке (порядок полей я не менял):

Сохраните изменения, перезапустите отчет в Excel - как видите, проблему с длинными названия колонок мы решили.

Теперь надо разобраться с отображением даты. Для этого:

1. Щелкните на поле "Начало"

2. В нижней части в блоке "Выражение поля" нужно ввести следующую строку

Для этого, 

2.1. щелкните на table1.Дата и время начала стоянки два раза мышкой, в появившемся окне измените тип на Текст, введите строку 

' '+convert (varchar(100),

Обратите внимание, что между одинарными кавычками в начале строки стоит пробел, к кончаетяс строка запятой!!!

После нажатия ОК получиться вот так

2.2. Параметр - выбрать "table1.Дата и время начала стоянки"

2.3. Текст - ввести 

,120)

Обратите внимание, что строка должна начаться с запятой...

т.е. должно в результате получиться вот это

Сохраните изменения.

Аналогичным образом меняем и поле "Окончание", но выбираем в шаге 2.2-а Параметр "table1.Дата и время конца стоянки"

Должно получиться вот так (выделено рамкой):

Сохраним изменения.

Нажмем SQL и проверим работу скрипта - все должно быть ОК. Обратите внимание, что формат записи даты сменился на ГГГГ-ММ-ДД ЧЧ:ММ:СС

Это произошло по тому, что мы принудительно изменили отображение даты на указанный формат. Более подробно вы можете почитать про функцию convert на MSDN. А пробел в начале строки (в кавычках) нам потребовался для того, что бы принудительно задать формат "строка" для передачи даты в Excel (иначе оно обратно в число конвертируется)

declare @ДатаС DateTime

/*Дата с*/ select @ДатаС = '10.02.2013'

declare @ДатаПо DateTime

/*Дата по*/ select @ДатаПо = '11.02.2013'

-- *** >>>>>> Секция специальных пользовательских переменных >>>>>> *** --

DECLARE @TimeZoneDifference INT; 

SET @TimeZoneDifference = 0; -- Разница в часах между часовым поясом выбранным пользователем и часовым поясом сервера

-- *** <<<<<< Секция специальных пользовательских переменных <<<<<< *** --

SELECT  table1.[ObjectGarNum] as [Гаражный номер]

, ' '+convert(varchar(100), table1.[TeleTimeBegin] ,120) as [Начало]

, ' '+convert(varchar(100), table1.[TeleTimeEnd] ,120) as [Окончание]

, table1.[Duration] as [Длит, сек]

, table1.[Location] as [Место]

FROM BN_FLEET_ATP.dbo.REPORT_Stops  table1

WHERE  table1.[TeleTimeBegin] >= @ДатаС AND table1.[TeleTimeBegin] <= @ДатаПо