Динамические запросы к БД Орион Про с выводом в Ексель (MS Excel)

Как вывести данные из базы данных Microsoft SQL Server в Excel с помощью динамического запроса, получая данные прямо из ячеек? Об этом пойдет речь в этой статье.
Данный кейс позволяет установить на компьютерах заинтересованных лиц своеобразный «Модуль отчетов», не покупая его. Для этого нам понадобятся: рабочая станция заинтересованного лица под управлением ОС Windows и установленным Microsoft Excel, доступ к БД Орион Про.

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

SELECT
pLogData.HozOrgan AS [TabNum],
pList.Name + ' ' + pList.FirstName + ' ' + pList.MidName AS [Name],
PDivision.Name AS [Division],
MIN (TimeVal) AS [DateTime]
 
FROM
pLogData
LEFT JOIN pList ON
pLogData.HozOrgan = pList.ID
LEFT JOIN PDivision ON
pList.Section = PDivision.ID
WHERE
 
pLogData.HozOrgan > 0
AND pList.Name IS NOT NULL
AND pLogData.Mode = 1 --Вход
-- заменить на ? после создания запроса в ms query
AND YEAR (TimeVal) = 2022
-- заменить на ? после создания запроса в ms query
AND MONTH (TimeVal) = 2
-- заменить на ? после создания запроса в ms query
AND DAY (TimeVal) = 14
GROUP BY
pLogData.HozOrgan,
PDivision.Name,
pList.Name + ' ' + pList.FirstName + ' ' + pList.MidName
ORDER BY [DateTime]

Логику построения запроса пояснять я не буду, однако обращу внимание на то, что пока данный запрос статический, т.к. в секции WHERE указаны конкретные данные. Мы слегка модифицируем данный запрос из Excel в будущем, для придания ему «динамичности».

Итак, открываем Excel и создаем примерно такую табличку, в которой мы собираемся указывать данные.

Для этого переходим на вкладку «Данные» > «Из других источников» > «Из Microsoft Query».

В окне «Выбор источника данных» на вкладке «Базы данных» выбираем «Новый источник данных» и нажимаем «ОК».

В открывшемся окне «Создание нового источника данных» указываем имя подключения, выбираем драйвер «SQL Server» и нажимаем кнопку «Связь». Далее, в окне «Вход на SQL Server» в поле «Сервер» указываем сетевое имя машины, на которой крутится сервер Ориона или его IP адрес. Заполняем поля с логином / паролем и нажимаем кнопку «параметры», если соединение успешно установилось, то в списке «База данных», вы найдете свою базу. Ее нужно выбрать из списка. В моем случае это Orion11223. Далее нажимаем «ОК».

После этого, в окне «Создание нового источника данных» рядом с кнопкой «Связь» должно появиться название выбранной вами БД. Если это так, значит все хорошо и мы нажимаем «ОК».

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

Открывается большое окно Microsoft Query и маленькое окно «Добавление таблицы». На данном этапе вы можете построить запрос с помощью конструктора, добавляя таблицы и как-то их связывая, но мы идем другим путем… Удостоверьтесь, что в списке «база данных» вы видите свою БД и закрывайте это окошко.

Сверху в окне «Microsoft Query» ищем и нажимаем кнопку «SQL». Откроется окно «Запрос SQL». В поле «Инструкция SQL» вставляем указанный выше запрос и нажимаем «ОК».

Соглашаемся с тем, что запрос не может быть представлен графически (мы же не дезигнеры)…

После того, как сервер вернет нам запрошенные данные, мы идем на вкладку «Файл» и выбираем «Вернуть данные в Microsoft Excel».

Откроется Ексель и окошко «Импорт данных». Указываем на листе ячейку, которая будет являться левым верхним углом таблицы. Способ представления оставляем «таблица» и переходим в свойства.

В «Свойствах подключения» указываем описание (желательно, если подключений будет много) и на вкладке «Использование» указываем каким образом и как часто необходимо обновлять выводимые данные, т.е. когда и как часто запрос будет выполняться в фоновом режиме. После этого переходи на вкладку «Определение».

Теперь настало время превратить наш статический запрос в динамический. Делается это крайне просто. На вкладке «Определение» окна «Свойства подключения» в поле «Текст команды» мы видим наш запрос. Необходимо в секции WHERE заменить год, месяц и день на знак вопроса [?], тем самым дав понять екселю, что эти данные ему нужно будет где-то взять. После модификации запроса нажимаем «ОК».

Поле того, как вы также нажмете «ОК» в окне «Импорт данных» Ексель предложит вам указать откуда ему брать значение первого параметра. Параметры нумеруются в порядке следования их в запросе. Первым параметром у нас был год, поэтому указываем ячейку под именем столбца «Год». Так же ставим галку «Использовать по умолчанию», иначе придется указывать каждый раз, откуда брать данные. По вопросу применения «Автоматически обновлять при изменении…» решайте сами.

После того, как вы указали все три параметра выполнится запрос и вернет вам пустую таблицу, так как никаких данных мы еще не указывали.

Укажем данные (дату) для просмотра времени прибытия сотрудников на работу в определенный день и нажмем кнопку «Обновить». Теперь запрос выполнился с учетом данных и мы видим результат.

Ячейки для ввода параметров всегда можно изменить в «Свойства подключения» > «Определение» > кнопка «Параметры». Данный пример был приведен в силу его простоты. Условия в секции WHERE так же работают и с текстом.
Пример: pList.Name like ?
В данном случае значение из ячейки будет браться в текстовом виде.