Как программно изменить строку подключения к SQL Server в запросе Excel Power?

Я пытаюсь создать сводную таблицу Excel на основе данных из базы данных Microsoft Dynamics NAV.

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

У кого-нибудь есть опыт или совет по этому вопросу?


c# sql-server excel powerquery dynamics-nav
person AronChan    schedule 26.01.2016    source источник
comment
Создайте хранимую процедуру SQL Server. Поместите туда все свои расчеты. Последним оператором процедуры должен быть select, который вернет все данные, необходимые для поворота. Для доступа к различным базам данных и таблицам вы можете использовать Dymanic SQL и Exec   -  person Mak Sim    schedule 26.01.2016

Ответы (2)


arrow_upward
2
arrow_downward

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

let getData =(servername,dbname,tablename)=>
let
Source = Sql.Database(Servername, dbname, [Query="select  abc , def  from" & tablename & " where condition etc etc"]),
#"CustomStep1" = some action on Source,
in
.
.
#"CustomStepn" = some action on Added CustomStepn-1
in
#"Added CustomStepn"
in  
getData

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

Шаг 2. Теперь используйте подход таблицы параметров. Создайте таблицу в обычной области Excel. Что-то вроде

Server Name|DatabaseName | Table_to_be_used

Используйте теперь опцию меню fromtable в параметрах powerquery (или вкладку «Данные» в Excel 16). Добавьте настраиваемый столбец в эту таблицу на шагах powerquery, используя функцию getdata, созданную на предыдущем шаге. Выполните любое другое «Расширение» (по умолчанию первая функция возвращает таблицу, если вы не выполняете никаких других преобразований), «Подведение итогов», «Переименование».

Однако брандмауэр формулы powerquery доставит вам трудности, поскольку powerquery не доверяет собственным SQL-запросам, и вам придется одобрять каждый собственный SQL-запрос. Вы можете попробовать снять флажок «Требовать одобрения пользователя для нового запроса собственной базы данных» в параметре запроса.

Надеюсь, вы поняли идею, и это поможет.

person S M    schedule 27.01.2016
comment
Это будет работать хорошо, но убедитесь, что tablename может быть изменен только запросами, которым вы доверяете, чтобы избежать внедрения SQL в вашу базу данных, например. Blah; DROP TABLE Users; select * from Blah - person Carl Walsh; 27.01.2016
comment
:) Я думаю, что это одна из причин, по которой power query не хочет доверять sql-запросам от пользователя, кроме того, что он портит свертывание запросов. - person S M; 27.01.2016
comment
Вероятно, было бы лучше избегать нативного запроса, такого как этот Source = Sql.Database(servername, dbname){[Item = tablename]}, который также устраняет проблему безопасности и свертывания! :) Если нужно указать схему таблицы, то Sql.Database(servername, dbname){[Schema = schemaname, Item = tablename]} - person Carl Walsh; 27.01.2016

arrow_upward
1
arrow_downward

Возможно, стоило бы изучить создание объектов Query и выставить их через oData, из которых Excel может читать. Преимущество здесь в том, что он может обрабатывать отношения между таблицами изначально и может отображать поля потока, которые вы не видите в прямых SQL-запросах к таблице.

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

Таблица Company даст вам префикс $, а имена таблиц являются статическими между компаниями. Вы можете написать какую-нибудь причудливую логику Excel, чтобы перебирать их.

person Jake Edwards    schedule 26.01.2016