Мы перешли на новую версию iScala и у нас перестали работать внешние отчёты в Excel, что делать?

Для начала давайте определим наиболее вероятную причину, почему отчёт перестал работать в iScala 3.1 и iScala 3.2 при переходе с более ранних версий. Скорее всего VBA код в Excel’е получал строку соединения с базой данных из открытой сессии iScala. В iScala 3.1 поменялось название объекта, в версиях до 3.1. объект, к которому обращается отчёт, назывался SfwIII (Scala for Windows III), мои коллеги называли его «Эс Эф Вэ ай-яй-яй». В версиях, начиная с 3.1. он называется iScala. Из-за этого встроенный в отчёт VBA код и перестал работать. Про это уже писалось ранее применительно к библиотеке CRUFLavv.dll.

Давайте рассмотрим на примере. Недавно мне прислали подобного рода отчёт:

Отчёт в Excel'е содержащий VBA код для получения строки соединения с базой данных

При попытке загрузки данных (при открытой сессии iScala) возникает ошибка:

При попытке загрузки данных (при открытой сессии iScala) возникает ошибка

Действительно, такого объекта (SfwIII) больше нет. Чтобы поменять ссылку на объект нужно перейти в режим редактирования VBA кода (используйте комбинацию клавиш Alt+F11, если переход не произошёл автоматически, это может быть, например, в том случае, если на модуль с VBA кодом установлен пароль).

Остановите выполнение сценария (кнопка остановки, как у музыкального проигрывателя 🙂 ):

Остановите выполнение сценария

Перейдите на закладку «Tools->References…»

Чтобы поменять ссылку на объект нужно перейти в режим редактирования VBA кода (используйте комбинацию клавиш Alt+F11). Перейдите на закладку "Tools->References..."

Снимите флажок у объекта SfwIII:

Снимите флажок у объекта SfwIII

Найдите объект iScala и установите флажок рядом с его названием:

Найдите объект iScala и установите флажок рядом с его названием

Теперь можно контекстной заменой поменять SfwIII на iScala в коде VBA:

Теперь можно контекстной заменой поменять SfwIII на iScala в коде VBA

В моём отчёте было 2 подобных замены:

В моём отчёте было 2 подобных замены

В результате получилось примерно следующее:

Исправленный VBA код

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

Кстати, у меня это работает только в том случае, если я не просто вошёл в iScala, но и вызвал какую-то подпрограмму (вошёл в определённый пункт меню). Не знаю, так ли это работало в старых версиях iScala, я никогда не делал такие «ухищрения» с получением строки соединения из открытой сессии iScala, так как считаю такие отчёты не вполне полноценными и, если делаю что-то подобное в Excel’е, то только для очень старых версий SQL Server’а, например, SQL 7 или SQL 2000.

Опишу подробно, как мне пришлось проверять работоспособность отчёта не имея текста хранимой процедуры:

Я создал хранимую процедуру с требуемым названием на своей виртуальной машине. В качестве запроса указал первое, что пришло в голову, что-то вроде «select * from SC230100». Увы, данные не подгрузились, выскочила ошибка. Стал разбираться, оказалось, что VBA код содержит некую странную конструкцию, где упоминаются конкретные поля, возвращаемые хранимой процедурой:

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

Я, конечно, не считаю себя великим программистом, но это какая-то ерунда, вся логика, на мой взгляд, должна содержаться в хранимой процедуре, а не обрабатываться VBA кодом «на клиенте». Нужно просто загрузить данные, как их выдаст хранимая процедура, а не «умничать», простите за нетолерантность. Вы должны использовать что-то вроде «ActiveSheet.Cells(7, 1).CopyFromRecordset rstScalaBase». Единственное оправдание, если Вы не можете по каким-то причинам изменить текст самой хранимой процедуры, включив всю логику в неё.

Вы должны использовать что-то вроде "ActiveSheet.Cells(7, 1).CopyFromRecordset rstScalaBase"

Эта конструкция («ActiveSheet.Cells(7, 1).CopyFromRecordset rstScalaBase») прекрасно работает. Впрочем, я модифицировал свою тестовую хранимую процедуру, назвав 2 поля в ней, соответственно, OrderNo и OrderLine и после этого заработал и исходный код.

А теперь не могу не прокомментировать ущербность подхода с загрузкой данных в Excel при открытой сессии iScala (речь именно про отчёты, а не про специальные приложения, используемые для кастомизации).

Уважаемые Дамы и Господа, на дворе конец 2018 года! Excel — это один из лучших продуктов Microsoft, но с 2005 (!!!) года у Microsoft появился замечательный продукт для отчётности, у которого возможности для отчётов покруче, чем у Excel’я. И, раз у Вас есть iScala, значит он у Вас тоже есть, его не нужно покупать отдельно. Разумеется, речь про SSRS (SQL Server Reporting Services). Во-первых, сразу отпадает необходимость поддержки огромного количества локальных файлов, все отчёты SSRS хранятся на сервере, во-вторых, можно управлять доступом, что отсутствует при использовании Excel отчётов, подобных описанному, в-третьих, для этого даже не нужен Excel, в-четвёртых, отчёты можно запускать по расписанию и автоматически получать по электронной почте. Да и никаких проблем при переходе со старой версии на новую, подобных вышеописанным, не будет. Здесь и любые группировки, и автоматические подитоги, и форматирование, и всплывающие подсказки при наведении мыши на поля, и drill down, drill up, drill through, и анализ значения предыдущей строки, и сумма нарастающим итогом, в общем, куча возможностей. А в вышеописанном отчёте подытоги вставляются макросом, фуууу 🙁 И, кстати, отчёт SSRS очень легко сохранить в Excel, если Вы без него совсем не можете 🙂 Я уж молчу о том, что для просмотра отчёта не надо входить в iScala и занимать там рабочее место.

Возможно, то, что отчёт перестал работать, это благо, повод отказаться от устаревших подходов и технологий, которые тянутся «в светлое будущее» только из-за того, что никто не изучает новых возможностей 🙂

Если у Вас возникли какие-либо вопросы, не стесняйтесь их задать.