Динамическая фильтрация данных перед импортом из SQL Server в Excel

Question

Я пытаюсь выяснить, есть ли способ отфильтровать данные из подключения к SQL Server, прежде чем они будут импортированы в Excel. Первоначальный импорт не имеет значения (когда соединение установлено), но мне нужно, чтобы в Excel не было практически никаких данных при последующем открытии и чтобы импортировать соответствующие данные только после выбора параметра. Таблицы SQL могут содержать много данных, и производительность является проблемой.

Поэтому, если я импортирую данные в таблицу, примените фильтр, скажем, для страны, которая должна быть Великобританией, закройте файл и откройте его снова на следующей неделе, в идеале он должен только запрашивать и импортировать данные для Великобритании. И когда я выбираю другую страну, она должна импортировать данные только для этой страны. Мне нужны варианты, которые будут перечислены из таблицы.

Я не верю, что у таблиц есть такая возможность. Но как насчет слайсеров, сводных таблиц, PowerView или PowerPivot?

Ценю любые идеи.

Answer 1

Вы можете сделать это с помощью таблиц, а также Powerpivot и Data Explorer. Самый простой и мой предпочтительный метод в настоящее время - создать параметризованный запрос в Excel. Для этого используйте мастер запросов и в конце добавьте ограничения для каждого параметра, который вы хотите передать. Как только это будет сделано, вы можете отредактировать Свойства соединения и назначить параметр, который будет возвращен как статическое значение, диалоговое окно или ячейку электронной таблицы.

1. Выберите источник данных с помощью MS Query - это необходимо для возможности передачи параметров обратно в SQL; Data>Other Sources>Microsoft Query .
2. Выберите Data Source , выберите ваш источник данных SQL здесь. Обычно я оставляю все эти параметры пустыми, пока не доберусь до окна « Finish , затем выберу « View data or edit query in MS Query.
   • Примечание. Обычно я предварительно обрабатываю необходимую информацию из SQL в едином представлении, к которому я могу подключиться здесь, но обычно вы можете передать любой оператор select. Я не пробовал вызывать хранимую процедуру, поэтому не уверен, как это будет работать.
3. В окне MS Query нажмите кнопку « Show/Hide Criteria button и выберите поле «Критерии». Это будет то, что вызывает ваши параметры для передачи обратно в SQL.
   • Пример. Если у вас есть поле даты и вы выбрали его в поле «Критерии», добавьте значение между [начало] и [конец]. Это создаст параметр, и MS Query попросит вас заполнить оба значения и немедленно вернуть соответствующий набор данных. Если вы проверите созданный оператор SQL, он добавит WHERE(datasource.date Between? а также ?) пункт.
4. Выберите «Файл»> «Вернуть данные в Excel», затем выберите «Таблица».
5. Чтобы таблица автоматически извлекала параметр запроса, выберите ячейку в таблице, перейдите в «Данные»> «Свойства»> «Свойства соединения»> «Определение»> «Параметры».
6. Для каждого параметра вы можете выбрать для него
   1. Предложить вам указать значение; или же
   2. Используйте конкретное значение; или же
   3. Получить значение из ячейки электронной таблицы с возможностью автоматического обновления данных таблицы при каждом изменении значения этой ячейки.
      • В качестве дополнительной опции вы можете использовать проверку данных в ячейке, выбранной вами для вашего параметра, что позволяет ограничить возможные значения на выбор. Вы можете даже получить проверочные ячейки из таблицы, которая подается из значений из вашей базы данных, обеспечивая возможность выбора только значений, хранящихся в базе данных.
7. Установите значение параметра для каждого параметра и нажмите ОК во всех диалоговых окнах.
8. Наконец, чтобы минимизировать размер файла, на вкладке «Свойства подключения»> «Использование» можно выбрать «Обновить данные при открытии файла» и «Удалить данные из внешнего диапазона» перед сохранением книги. Это приведет к сбросу подключенных данных, когда файл не используется активно, минимизируя размер файла.

Я часто буду создавать сводную таблицу на основе таблицы, полученной из параметризованного запроса, чтобы возвращать значения для автоматически обновляемых, определенных диапазонов дат (например, за предыдущие 6 месяцев).

РЕДАКТИРОВАТЬ: чтобы вернуть несколько значений для использования в предложении SQL IN, вам просто нужно изменить окно Критерии в поле MS Query. Например, если у вас есть имя поля «Страна», выберите его в поле «Критерии», затем в поле «Значение» введите IN([Первый], [Второй], [Третий]). Выберите Return Data to Excel, и MS Query попросит ввести значения вручную. Вернувшись в Excel, вы можете снова изменить параметры и указать их на три ячейки в электронной таблице, каждая из которых может быть ограничена проверкой данных. Хотя, возможно, существует ограничение на количество параметров, которые вы можете передать подобным образом, Excel легко обрабатывает три, так что о некоторых других, скорее всего, не может быть и речи.

Answer 2

Если вы не возражаете против использования VBA, то «легкий» способ сделать это - динамически генерировать SQL-запрос.

Я довольно широко использую что-то похожее на приведенное ниже для создания специальных панелей мониторинга, которым не требуется ничего сложного, такого как QlikView или Crystal

Например

Скрытый лист «Переменные» содержит таблицу «Table_SQL_SALES_EMPLOYEES». Это заполняется SQL, чтобы просто вернуть минимум, необходимый для выбора параметра. Что-то вроде SELECT DISTINCT AccountDirector FROM InvoicedSales ORDER BY AccountDirector .

Затем у меня есть именованный диапазон 'Sales_Employees', который его ссылается на as =Table_SQL_SALES_EMPLOYEES[AccountDirector] . Это сопоставлено с полем со списком, в котором Диапазон ввода установлен в «Sales_Employees». Это отправит номер в ячейку по вашему выбору (E5 в моем случае) элемента в выбранном списке. Чтобы вернуть фактическое значение в соответствии с исходным извлечением SQL, необходимо использовать =OFFSET(Table_SQL_SALES_EMPLOYEES[[#Headers],[AccountDirector]],$E$5,0) . Это называется «Slp».

.: Теперь у нас есть выпадающий список всех возможных директоров аккаунтов. Есть еще один для финансового квартала, но тот же принцип.

Когда выбор изменяется в поле со списком, он вызывает макрос 'changeFilter()'

Sub changeFilter()
    Dim wb As Workbook
    Set wb = Excel.ActiveWorkbook
    Dim ws As Worksheet
    Set ws = Excel.ActiveWorkbook.Sheets("Lookups")
    Dim conn As Excel.WorkbookConnection
    Dim slp As Name
    Set slp = wb.Names("Slp")
    Dim qtr As Integer
    qtr = wb.Names("qtr").RefersToRange.Value2
    Dim query As String

    ' Adjusted Sales Consolidated
    Set conn = wb.Connections("SQL_ADJUSTEDSALES_CONSOLIDATED")
    query = "SELECT * FROM InvoicedSales WHERE AccountDirector=" & "'" & slp.RefersToRange.Value2 & "'"
    conn.OLEDBConnection.CommandText = query
    conn.Refresh

Я в основном предварительно настраиваю таблицы / сводки, подключая их к базе данных с жестко закодированными переменными и выбирая параметры «Обновить данные при открытии файла» и «Удалить данные из диапазона внешних данных перед сохранением рабочей книги» - это сохраняет крошечный размер файла так как на самом деле ничего не спасено.

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