Здравствуйте!

Есть excel-ий файл с подключением к OLAP с помощью MDX-запроса. То есть ListObject c подключением. Выгружаем отгрузки по городам (по строкам) и по месяцам (по колонкам) за Апрель - Май 2017 г. См. вложенный файл.

Но если в MDX-запросе изменить период, скажем, Март - Май 2017 и обновить, то по идее колонка "Март 2017" должна быть между колонками "Город" и "Апрель 2017". Но после обновления находится в конце.

Почему так происходит? Как сделать так, чтобы при добавлении новых колонок - колонки находились в той порядке как в MDX-запросе.

То же самое если запускать локальный SQL-запрос к excel-им листам.

iMrTidy,

О, супер! Спасибо!

Заметил один нюанс.

В конечной таблице отображаются такие поля как "Город", "Март 2017", "Апрель 2017" и "Май 2017". Далее нужно сделать обновление при измененном локальном SQL-запросе.

Если в локальном SQL-запросе удалить поле "Май 2017", то в результате это поле якобы удаляется, но вместо него стоит какое то "левое" поле "Апрель 2018". А если вернуть эту галочку, то это не нужное поле не отображается.

Или так. Если в локальном SQL-запросе удалить поле "Май 2017" и добавить "Февраль 2017", то поля в нужном порядке. Если снять галочку, то поля не по порядку.

Во вложенном файле результаты представлены в четырех вариантах.

Как надо правильно макрос написать с учетом таких нюансов, то есть чтобы порядок сохранялся и без всяких "левых" полей?

ferzmikkКак надо правильно макрос написать с учетом таких нюансов, то есть чтобы порядок сохранялся и без всяких "левых" полей? Или как правильно настроить?

Еще один нюанс.

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

Но проблема в том, что если мер очень много, то для каждой меры нужно делать соответствующую запись. Очень не удобно, приходиться писать длинные локальные SQL-запросы, усложняется код, трудно читать, есть вероятность сделать ошибку, и добавлять вложенный запрос, где находятся эти обрабатываемые записи. И причем часто приходится писать такие длинные локальные SQL запросы.

Как можно тут обойти, чтобы не приходилось использовать такие длинные обрабатываемые записи?

iMrTidyА почему Вы используете именно QueryTable? Может быть проще простым SQL запросом?Конечно проще SQL-запрос. Но не разрешают использовать в нашей компании, так как это сильно влияет на производительность системы. Поэтому выгружаем данные из OLAP c помощью сводных таблиц или MDX-запросов.

Со временем в куб добавляются новые меры и атрибуты. Но все в порядке длинной очереди. В нашей компании аналитика очень сложная. Но лишние и не часто используемые поля добавлять не нужно. Это все влияет на производительность.

MDX это не SQL. MDX это не реляционные таблицы, а многомерные. MDX не соединяет таблицы как SQL. При выгрузке данных с помощью MDX-запросов с заданными многими полями (в том числе вычисляемыми) бывает долго выгружает, а иногда вообще не выгружает. И при текущих задач компании одним MDX-запросом не всегда удается получить нужную аналитику.

Поэтому получаем несколько таблиц с помощью MDX-запросов на каждый excel-ий лист. На других листах дополнительные справочные таблицы. Потом обращаемся к этим excel-им листам с помощью локального SQL запроса.

Есть не мало новых отчетов, структура которая формируется в процессе. То есть для какого-то отчета каждый пользователь отчета оставляет заявку на какие то поля с определенной логикой. Пишется сложный макрос, который получает несколько таблиц от OLAP, потом запускает этот локальный SQL-запрос. Что интересное, приходится промежуточные таблицы сохранять на отдельных вкладках типа как темповые и по причине ограничения памяти 2Г. В принципе можно это делать все и в Access. Со временем корректируется MDX-запросы, корректируется локальные SQL-запросы, дорабатывается макрос, получается отчет с более четкой структурой и оформлением. После того как структура отчета утверждается, делается заявка на добавление в кубе необходимых полей с определенной сложной логикой. То есть отчет в какое время был как экспериментальным. Если отдавать заявку минуя разработку как экспериментального отчета, то можно потерять очень много времени.

А также приходится выгружать данные из разных кубов.

Вот несколько причин почему используется QueryTable.

iMrTidyНапример, можно генерировать запрос динамически.Это как? С параметрами?

iMrTidyЯ имел в виду внутренний SQL запрос без QueryTable. Вы все равно используете VBA, тогда можно было бы "выбросить лишнее".Можно пример?

iMrTidystrQuery имеет тип данных string и вполне может генерироваться динамически. На сколько я могу судить, логика построения запросов весьма простая в Вашем случае.Во вложенном примере выше указан простой SQL-запрос чтобы обозначить суть проблемы. В рабочих файлах они очень длинные и от 1 до 10 шт. Так как с каждым разом были заявки на доработки от разных пользователей отчетов, что понимаешь потом что надо было в Access делать и не мучать Excel.

В Excel обычно SQL-запросы храню на надписях, для удобства чтения делаю отступы, если есть вложенные запросы. То есть для одного SQL-запроса два объекта-надписи: слева с параметрами, а справа со значениями параметрами, чтобы убедиться, что значения вместо параметров правильные вставились. Аналогично для остальных SQL-запросов.

Макрос вытаскивает из надписи SQL-запрос с параметрами, вставляет значения вместо параметров (тут работает строковая функция Replace), отдельно выкладывает сгенерированный запрос на отдельную надпись и запускает этот запрос.

Но если SQL-запрос получился очень длинным, то бывает в объект-надпись не помещается. Приходилось этот длинный запрос делить на два запроса.

iMrTidyЯ не работал с OLAP, но судя по Вашим описаниям, выгружаемые таблицы можно было бы обрабатывать, например, при помощи MS SQL.Не разрешают использовать Microsoft SQL Server
можно написать надстройку для Excel, например, на C#,Не приходилось еще делать надстройки. C# недавно начал изучать потихонечку.
или даже приложение, которое позволит гибче получать данные из промежуточных таблиц.Мне бы это увидеть бы, чтобы понятнее было.

iMrTidyMySQL? В таком СУБД как MySQL не работал еще