6 сент. 2012 г.

QlikView + 1С без 1С коннектора

Популярность QlikView на российском рынке BI-систем постепенно растет, многие компании используют это ПО для консолидации финансовых данных, сокращения трудозатрат, оперативного контроля и многих других задач. И вполне закономерно, возникла необходимость связки 1С с QlikView, желательно напрямую, без всяких промежуточных выгрузок из базы. Некоторые ИТ-компании уже разработали коммерческие решения, например,  БИТ:Коннектор QlikVIew к 1С или АТК QlikView-1C Коннектор, так или иначе облегчающие этот процесс. В качестве бесплатной альтернативы предлагаю простой способ получения информации из 1С через COM-соединение.
Для экспериментов я использовал демо-базу 1С:Управление торговлей 11. Сначала реализуем тестовую функцию в 1С, открываем Модуль внешнего соединения, пишем код:
Функция TestQlik() Экспорт
 
 Возврат "QlikView успешно подключилось к 1С";
 
КонецФункции 
Теперь в QlikView настраиваем COM-соединение. Открываем редактор макросов, пишем следующий скрипт, подставляем свои параметры подключения к базе:
sub v82connect

set conn = CreateObject("V82.ComConnector")
set v82 = conn.Connect("File=""I:\1c_base\demoTRD"";usr=""QlikView"";")

MsgBox(v82.TestQlik)

end sub 
Обратите внимание на настройки параметров локальной безопасности, установлена в "Разрешить системный доступ". Жмем "Тест", если все сделано правильно, должно появиться сообщение "QlikView успешно подключилось к 1С".
QlikView + 1С без коннектора

Теперь попробуем подгрузить данные из 1С. Сначала сформируем пустую таблицу в скрипте загрузки:
SET ThousandSep=' ';
SET DecimalSep=',';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='# ##0,00р.;-# ##0,00р.';
SET TimeFormat='h:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY h:mm:ss[.fff]';
SET MonthNames='янв;фев;мар;апр;май;июн;июл;авг;сен;окт;ноя;дек';
SET DayNames='Пн;Вт;Ср;Чт;Пт;Сб;Вс';

LOAD * INLINE [
    Data, Firma, Partner, Tovar, Quantity, Summa, Cost
];
Запускаем скрипт, размещаем простую таблицу на листе:

В свойствах документа включаем динамическое обновление данных:

Пишем скрипт загрузки в редакторе макросов:
sub v82query

set conn = CreateObject("V82.ComConnector")
set v82 = conn.Connect("File=""I:\1c_base\demoTRD"";usr=""QlikView"";")

query_1c = "ВЫБРАТЬ" &_
" НАЧАЛОПЕРИОДА(ВыручкаИСебестоимостьПродаж.Период, ДЕНЬ) КАК Data,"& vbNewLine & _
" ПРЕДСТАВЛЕНИЕ(РегистрАналитикаУчетаПоПартнерам.Организация) КАК Firma,"& vbNewLine & _
" ПРЕДСТАВЛЕНИЕ(РегистрАналитикаУчетаПоПартнерам.Партнер) КАК Partner,"& vbNewLine & _
" ПРЕДСТАВЛЕНИЕ(РегистрАналитикаУчетаНоменклатуры.Номенклатура) КАК Tovar,"& vbNewLine & _
" СУММА(ВыручкаИСебестоимостьПродаж.Количество) КАК Quantity,"& vbNewLine & _
" СУММА(ВыручкаИСебестоимостьПродаж.СуммаВыручки) КАК Summa,"& vbNewLine & _
" СУММА(ВыручкаИСебестоимостьПродаж.Себестоимость) КАК Cost"& vbNewLine & _
"ИЗ"& vbNewLine & _
" РегистрНакопления.ВыручкаИСебестоимостьПродаж КАК ВыручкаИСебестоимостьПродаж"& vbNewLine & _
"  ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаУчетаПоПартнерам КАК РегистрАналитикаУчетаПоПартнерам"& vbNewLine & _
"  ПО ВыручкаИСебестоимостьПродаж.АналитикаУчетаПоПартнерам = РегистрАналитикаУчетаПоПартнерам.КлючАналитики"& vbNewLine & _
"  ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаУчетаНоменклатуры КАК РегистрАналитикаУчетаНоменклатуры"& vbNewLine & _
"  ПО ВыручкаИСебестоимостьПродаж.АналитикаУчетаНоменклатуры = РегистрАналитикаУчетаНоменклатуры.КлючАналитики"& vbNewLine & _
"СГРУППИРОВАТЬ ПО"& vbNewLine & _
" НАЧАЛОПЕРИОДА(ВыручкаИСебестоимостьПродаж.Период, ДЕНЬ),"& vbNewLine & _
" ПРЕДСТАВЛЕНИЕ(РегистрАналитикаУчетаПоПартнерам.Организация),"& vbNewLine & _
" ПРЕДСТАВЛЕНИЕ(РегистрАналитикаУчетаПоПартнерам.Партнер),"& vbNewLine & _
" ПРЕДСТАВЛЕНИЕ(РегистрАналитикаУчетаНоменклатуры.Номенклатура)"

set v8query = v82.NewObject("Query")
v8query.text = query_1c
set result = v8query.Execute

set m = result.Choose

do while m.next
 set sql_string = ActiveDocument.DynamicUpdateCommand("INSERT INTO * "& vbNewLine & _
 "(Data, Firma, Partner, Tovar, Quantity, Summa, Cost) " & vbNewLine & _
 "VALUES (" & m.Data &", "& m.Firma & ", " & m.Partner & ", " & m.Tovar & ", " & m.Quantity & ", " & m.Summa & ", " & m.Cost & ")")
loop

ActiveDocument.GetApplication.Refresh

end sub 
Обратите внимание на написание текста запроса, это связано с особенностями VBScript. Поэтому лучше описание запроса оставлять на стороне 1С, в QlikView передавать только готовый результат. Так же псевдонимы полей в запросе пришлось писать на латинице, кириллицу не воспринимает. Выделяем нашу процедуру в списке редактора, жмем "Тест". Мы увидим что наша пустая таблица заполнилась данными.
QlikView + 1С без коннектора

Теперь можно построить пару диаграмм:
QlikView + 1С без коннектора

При желании можно поставить наш скрипт в выполнение после скрипта загрузки:

39 комментариев:

  1. Анонимный7 мая 2013 г., 17:10

    Невозможно создание объекта контейнером ActiveX: 'V82.ComConnector'

    причем 'V82.Application' срабатывает

    В чем может быть дело?

    ОтветитьУдалить
    Ответы
    1. Попробуй зарегистрировать компоненту comcntr.dll для используемой платформы утилитой regsvr32.exe.
      Лежит в каталоге %ProgramFiles%\1cv82\"нужная версия"\bin\
      Бывает, что когда используется несколько версий платформ, ComConnector будет работать у установленной последней.
      Либо можно посмотреть здесь http://forum.infostart.ru/forum14/topic33442/

      Удалить
  2. Анонимный8 мая 2013 г., 13:57

    Спасибо!
    dll зарегистрировала. Не помогло.
    По поводу второй ссылки - у меня все на локальном компьютере.

    ОтветитьУдалить
    Ответы
    1. а какая версия QlikView? может быть 64x? не исключено, что тогда решение здесь
      http://miko.ru/blogs/SwordBlog/54/

      Удалить
    2. Анонимный15 мая 2013 г., 22:32

      все оказалось проще. 1с была установлена без поддержки сом соединения =)

      Удалить
    3. Ясно, учебная версия 1С похоже ) она ставится без поддержки COM-соединений.

      Удалить
    4. В продолжение темы - у меня dll была установлена не до конца, помог этот совет http://forum.infostart.ru/forum14/topic33442/

      Удалить
  3. Анонимный27 мая 2013 г., 19:02

    Спасибо!!

    помогло за пол-часа понять что к чему! А дальше уже сами :)

    Успехов автору!

    ОтветитьУдалить
    Ответы
    1. Вам спасибо и успехов в разработке :)

      Удалить
  4. Добрый день. А можно ли подключится к 1с вер. 7.7 торговля и склад ?

    ОтветитьУдалить
  5. День добрый, можно. Подключение будет выглядеть примерно так:
    V7 = CreateObject("V77.Application")
    result = V7.Initialize(V7.RMTrade, "/d" + path + " /N" + UserName + " /P" + Password, "")

    Но лучше подключаться из 1С к QlikView. Можно взять за основу функционал из http://start1c.blogspot.ru/2012/10/1-qlikview.html, и переписать его под 7.7.

    ОтветитьУдалить
  6. Доброго времени суток
    Не подскажите в чем может быть проблема:
    1) Изначально было сообщение об ошибке - "Невозможно создание объекта контейнером ActiveX: 'V82.ComConnector'",
    помог Ваш совет из форма http://forum.infostart.ru/forum14/topic33442/

    2) вторая проблема - "Невозможно создание объекта контейнером ActiveX: 'v8query.text'"

    код мароса:


    sub v82query

    set conn = CreateObject("V82.ComConnector")
    set v82 = conn.Connect("File=""D:\1C Base"";usr=""QlikView"";")

    query_1c = "ВЫБРАТЬ" &_
    " К.Наименование Как NameC"& vbNewLine & _
    "ИЗ" & vbNewLine & _
    "Справочник.Контрагенты"& vbNewLine & _
    "Как К"

    set v8query = v82.NewObject("Query")
    v8query.text = query_1c 'ругается на эту строку
    set result = v8query.Execute

    set m = result.Choose

    do while m.next
    set sql_string = ActiveDocument.DynamicUpdateCommand("INSERT INTO * "& vbNewLine & _
    "(NameC) " & vbNewLine & _
    "VALUES (" & m.NameC &")")
    loop

    ActiveDocument.GetApplication.Refresh

    end sub

    ОтветитьУдалить
    Ответы
    1. Добрый день.
      Ваш код у меня выполнился без ошибок.
      На всякий случай посмотрите, что за ошибка написана в строке состояния, вверху в окне редактора макросов.

      Удалить
  7. Спасибо за ответ!

    Ошибка :"Невозможно создание объекта контейнером ActiveX: 'v8query.text'"

    может я что-то не так делаю... На локальную машину установил 1с82 и QlikView (Пытаюсь разобраться с 1с и QlikView 2-й день, раньше не приходилось сталкиваться с данными программами), затем по Вашему примеру создаю соединение и получаю ошибку.
    Возможен ли такой вариант, что у меня не хватает какого-то компонента?

    ОтветитьУдалить
  8. Забыл уточнить моя операционная система Win7, QlikView установил х86

    ОтветитьУдалить
    Ответы
    1. Что за конфигурация 1С?

      попробуйте выполнить следующий код в своем макросе
      ...
      set v8query = v82.NewObject("Query")
      msgbox VarType(v8query) '=9
      msgbox v8query.text 'пустая строка
      ...

      Удалить
  9. Добрый день, Михаил
    Конфигурация 1С- УТП 8.2.16.309
    Попробовал, на строке:
    msgbox v8query.text 'пустая строка
    Ошибка:"Невозможно создание объекта контейнером ActiveX: 'v8query.text'"
    В интернете нечего не могу найти или не там ищу (((

    ОтветитьУдалить
    Ответы
    1. попробуйте тогда так
      ...
      set v8query = v82.NewObject("Query", query_1c)
      set result = v8query.Execute
      set m = result.Choose
      ...

      Удалить
  10. И забыл уточнить
    msgbox VarType(v8query) '=9
    выполняется, результат 9
    Извиняюсь, платформа 8.2.16.368

    ОтветитьУдалить
  11. В строке
    set result = v8query.Execute
    Ошибка: "Невозможно создание объекта контейнером ActiveX: 'v8query.Execute'"

    ОтветитьУдалить
  12. Сейчас попробую на другом компьютере установить QlikView

    ОтветитьУдалить
  13. На WinXP x86 Ваш код (пример) из статьи отработал без проблем!
    На машине которая выдает ошибку, Win7 x64
    Подскажите пожалуйста в каком направлении искать решение проблемы

    ОтветитьУдалить
    Ответы
    1. Как вариант, поиграться с версиями 32x и 64x QlikView.
      Так же проверить/обновить на 64x машине версию Microsoft .Net Framework.
      Помнится, были проблемы у одного человека в начале года с загрузкой,
      пока он до 4.0 не обновился.

      Удалить
  14. Спасибо большое Михаил! Это я изначально сделал (не помогло). Если найду решение своей проблемы, обязательно отпишусь!
    Спасибо за готовое решение и успехов в новых!

    ОтветитьУдалить
  15. Автору МЕГАспасибо, очень полезная статья.

    ОтветитьУдалить
    Ответы
    1. Спасибо за посещение и проявленный интерес )

      Удалить
  16. Большое спасибо за статью. Без нее бы не разобрался.
    На windows server 2012 x64 все отработало.
    Несколько нюансов на которых запнулся.
    1. На х64 сервер достаточно поставить "Компоненты доступа к серверам 1с" и "com-соединение" из инсталяции сервера 1с х64. Саму 1с ставить не нужно.
    2. При выполнении инструкции "INSERT INTO ... VALUES (...)" Поддерживаются только простые типы. Причем строку нужно передавать из 1с в одинарных кавычках: '0555'
    3. Если вы работаете не с результатом запроса, а с таблицей значений, то код обхода таблицы будет примерно такой
    ("Common.OperatingProfit" - процедура получения таблицы значений в общем модуле "common"):

    set result = v82.Common.OperatingProfit("20140310","20140310")
    NumRow = result.Count-1
    'MsgBox(NumRow)
    For I = 0 To NumRow Step 1
    Set m = result.Get(I)
    set sql_string = ActiveDocument.DynamicUpdateCommand("INSERT INTO * (Firm,Month_) VALUES ("&m.Firm & "," & m.Month_ &")")
    Next

    ОтветитьУдалить
  17. Добрый день

    Подскажите пожалуйста, почему может не выводиться таблица?
    Я заметил, что если сделать MsgBox(sql_string) в цикле вывода, то Сообщение будет содержать значение "Ложь", у вас так же?

    ОтветитьУдалить
    Ответы
    1. День добрый.
      Если sql_string = ActiveDocument.DynamicUpdateCommand возвращает false, то скорей всего в свойствах документа не включена возможность динамического обновления, Свойства документа -> Сервер, скрин в статье имеется.
      Так же ссылка https://community.qlikview.com/thread/57706

      Удалить
    2. В свойствах документа галочка стоит напротив пункта "Вкл. динамич. обновл. данных". Есть еще какие-нибудь предположения

      Удалить
    3. Этот комментарий был удален автором.

      Удалить
    4. Вот скрин моих настроек
      http://i11.pixs.ru/storage/5/7/5/QlickViewj_6889173_23011575.jpg

      Удалить
    5. Проверьте еще скрипт загрузки на наличие кода создания таблицы
      LOAD * INLINE ...

      А так же соответствие имен полей таблицы именам полей результата.

      Например, в скрипте загрузки:
      LOAD * INLINE [
      pole1, pole2
      ];

      Работающий тестовый скрипт:
      sub test_01

      set sql_string = ActiveDocument.DynamicUpdateCommand("INSERT INTO * "& vbNewLine & _
      "(pole1, pole2) " & vbNewLine & _
      "VALUES (" & 1 &", "& 2 & ")")

      MsgBox(sql_string)

      end sub

      Если я в скрипте изменю имя поля pole2 на pole3, то получу Ложь.

      Удалить
    6. Спасибо за ответ. Разобрался. Проблема была в запросе. Нельзя в инсерте вставлять текст НЕ заключенный в кавычки.
      Пример (a2 и a3 заключил в кавычки в инсерте, иначе не работало):
      <
      sub v83query

      set conn = CreateObject("V83.ComConnector")
      set v83 = conn.Connect("Srvr = XXX; Ref = XXX; Usr = ""XXX""; Pwd = ""XXX""")

      query_1c = "ВЫБРАТЬ ПЕРВЫЕ 10" &_
      " НАЧАЛОПЕРИОДА(Консолидация_ДвиженияТоваровОбороты.Период, ДЕНЬ) КАК a1,"& vbNewLine & _
      " ПРЕДСТАВЛЕНИЕ(Консолидация_ДвиженияТоваровОбороты.Номенклатура) КАК a2,"& vbNewLine & _
      " ПРЕДСТАВЛЕНИЕ(Консолидация_ДвиженияТоваровОбороты.Подразделение) КАК a3,"& vbNewLine & _
      " Консолидация_ДвиженияТоваровОбороты.КоличествоОборот КАК a4,"& vbNewLine & _
      " ИЗ"& vbNewLine & _
      " РегистрНакопления.Консолидация_ДвиженияТоваров.Обороты(ДАТАВРЕМЯ(2016, 1, 1, 0, 0, 0), ДАТАВРЕМЯ(2016, 1, 5, 0, 0, 0), Запись, ) КАК Консолидация_ДвиженияТоваровОбороты"

      set v8query = v83.NewObject("Query")
      v8query.text = query_1c
      set result = v8query.Execute

      set m = result.Choose

      do while m.next
      set sql_string = ActiveDocument.DynamicUpdateCommand("INSERT INTO * (a1, a2, a3, a4) VALUES (" & m.a1 & ", """ & m.a2 &""", """ & m.a3 &""", " & m.a4 &")")
      MsgBox(sql_string)
      loop

      ActiveDocument.GetApplication.Refresh

      end sub
      >

      Удалить
  18. Здравствуйте, при Тесте Макроса на set sql_string = ActiveDocument.DynamicUpdateCommand() выскакивает ошибка "Объект не поддерживает это свойство или метод" В настройках динамическое обновление включил.
    В чём может быть загвоздка? QlikView 12

    ОтветитьУдалить
    Ответы
    1. День добрый. Разработка в этом направлении больше не ведется.

      Удалить
  19. Михаил, добрый день! А вы можете написать коннектор (php скрипт) для выкачки данных их 1С в виде sql баз и загрузки их в Microsoft Azure?

    ОтветитьУдалить
    Ответы
    1. Приветствую, на php нет. Может лучше задействовать механизмы, заложенные в платформу 1С?

      Удалить