Динамические запросы к БД Орион Про с выводом в Ексель (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 и создаем примерно такую табличку, в которой мы собираемся указывать данные.

Табличка с данными для запроса
Табличка с данными для запроса

Теперь нам необходимо установить связь по локальной сети программы MS Excel, установленной на рабочей станции, с базой данных Microsoft SQL Server 2008 R2 программного обеспечения Орион Про, установленного на сервере посредством «ms query» и драйвера SQL Server.

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

Создание подключения к БД
Создание подключения к БД

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

Выбор источника данных
Выбор источника данных

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

Ввод данных для соединения, выбор БД
Ввод данных для соединения, выбор БД

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

Завершение настройки соединения
Завершение настройки соединения

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

Подключаемся к БД
Подключаемся к БД

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

MS Query и добавление таблиц
MS Query и добавление таблиц

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

Добавление инструкции SQL
Добавление инструкции SQL

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

Отказ от графического представления запроса
Отказ от графического представления запроса

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

Возврат данных в ексель
Возврат данных в ексель

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

Импорт данных
Импорт данных

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

Настройка использования запроса
Настройка использования запроса

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

Изменение запроса
Изменение запроса

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

Указание ячеек данных для запроса
Указание ячеек данных для запроса

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

Таблица без данных
Таблица без данных

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

Таблица с результатами выполнения запроса
Таблица с результатами выполнения запроса

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

Оцените пожалуйста статью:

ПечальноТак себеНе плохоХорошоОтличная статья! 2 оценок.
Загрузка...

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *