AXForum  
Вернуться   AXForum > Блоги > Gustav'ово бложище, или Записки DAX-дилетанта-III
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

Стараюсь писать про Аксапту, хотя частенько тянет в Офис
Оценить эту запись

Вывод в Excel сводной таблицы "пользователи-группы"

Запись от Gustav размещена 21.12.2009 в 14:32

В принципе задача не бог весть какая сложная, хотя и сама по себе достаточно полезная с точки зрения администрирования Аксапты. Я, наверное, и не стал бы постить ее решение, если бы не маленькая изюминка - исходные данные из Аксапты выводятся не на лист Excel, а сразу в кэш сводной таблицы. Точнее, кэшу передается ADODB.Recordset, предварительно сформированный в памяти в Аксапте. Поэтому сообщение можно считать приложением к, не побоюсь этого слова, ставшей популярной теме Поговорим об ADO

Вот джоб, строящий в Excel перекрестную таблицу со строками-пользователями и столбцами-группами:
X++:
#CCADO
#define.xlExternal(2)
#define.xlRowField(1)
#define.xlColumnField(2)

static void Job_showUserGroupInExcelPivot(Args _args)
{
    UserGroupList   userGroupList;
    UserInfo        userInfo;
    UserGroupInfo   userGroupInfo;

    COM             rst, flds, fld;
    COM             wbk, pc, ptb, pf;
    COM             rng = SysExcelApplication::construct().workbooks().add().worksheets().itemFromNum(1).range('A1').comObject();
    ;

    rst = AdoRst::openRecordsetInMemory([
            ['UserId'    , #adVarChar,  5 ],
            ['UserName'  , #adVarChar, 40 ],
            ['GroupId'   , #adVarChar, 10 ],
            ['GroupName' , #adVarChar, 40 ]]);

    flds = rst.Fields();

    while select userGroupList
        join userInfo
            where userInfo.id == userGroupList.userId
        join userGroupInfo
            where userGroupInfo.id == userGroupList.groupId
    {
        rst.AddNew();
            fld = flds.Item('UserId'   ); fld.Value(userGroupList.userId );
            fld = flds.Item('UserName' ); fld.Value(userInfo.name        );
            fld = flds.Item('GroupId'  ); fld.Value(userGroupList.groupId);
            fld = flds.Item('GroupName'); fld.Value(userGroupInfo.name   );
        rst.Update();
    }

    wbk = rng.Parent(); wbk = wbk.Parent(); // ActiveWorkbook

    pc = wbk.PivotCaches(); pc = pc.Add(#xlExternal); // PivotCache
    pc.Recordset(rst); // вот здесь и передаем рекордсет -> кэшу

    ptb = pc.CreatePivotTable(rng); // PivotTable

    pf = ptb.PivotFields('UserName' ); pf.Orientation(#xlRowField   ); pf.Position(1); pf.Subtotals(1,false);
    pf = ptb.PivotFields('UserId'   ); pf.Orientation(#xlRowField   ); pf.Position(2); pf.Subtotals(1,false);

    pf = ptb.PivotFields('GroupId'  ); pf.Orientation(#xlColumnField); pf.Position(1); pf.Subtotals(1,false);
    pf = ptb.PivotFields('GroupName'); // PivotField
    ptb.AddDataField(pf, 'Участие пользователей в группах');
                                       pf.Orientation(#xlColumnField); pf.Position(2); pf.Subtotals(1,false);

    COM::createFromObject(rng.Application()).Visible(true);
}
Как видно, код джоба получился вполне компактным - в первую очередь, за счет того, что везде, где можно, используются свойства сводной таблицы по умолчанию. Признаком вхождения конкретного пользователя в конкретную группу является результат (равный 1) агрегатной функции Count по последнему полю GroupName. Причем, нюанс - сначала это поле помещается в область данных сводной таблицы и лишь заключительным шагом - в область заголовков строк. Использовавшееся вначале решение "в лоб", когда поле первым делом помещалось в область заголовков строк и затем в область данных, приводило к тому, что поле исчезало из заголовков строк. Ну, а почему выбрано именно последнее поле, думаю, понятно - чтобы не делать лишнего присваивания "pf = ...".

Рекомендую также обратить внимание на способ выключения ненужных подитогов для полей при помощи конструкции "Subtotals(1,false)". При записи процедуры макрорекордером в Excel на этом шаге прописывается вариантный массив VBA вида Array(False,False,False...), имитация которого в джобе при помощи COMVariant.safeArray увеличила бы размер его кода раза в два - за счёт подготовительных действий по созданию массива.

Используемый в джобе для построения рекордсета в памяти статический метод openRecordsetInMemory - член некоторого моего класса AdoRst. Метод можно поместить и в Global, либо развернуть как вложенную функцию в самом джобе. Единственный параметр метода - контейнер контейнеров-полей.
X++:
#CCADO
static COM openRecordsetInMemory(container _fields)
{
    COM rst  = new COM('ADODB.Recordset');
    COM flds = rst.Fields();

    int i;
    str fldName;
    int fldType;
    int fldDefinedSize;
    ;

    for (i=1;i<=conLen(_fields);i++)
    {
        [fldName, fldType, fldDefinedSize] = conPeek(_fields, i);

        if (! fldType)
            fldType = #adVarChar;

        if (! fldDefinedSize            &&
           (fldType == #adChar          || // 129
            fldType == #adWChar         )) // 130
        {
            fldDefinedSize = 30;
        }

        if (! fldDefinedSize            &&
           (fldType == #adVarChar       || // 200
            fldType == #adLongVarChar   || // 201
            fldType == #adVarWChar      || // 202
            fldType == #adLongVarWChar  )) // 203
        {
            fldDefinedSize = 255;
        }

        if (fldDefinedSize)
            flds.Append(fldName, fldType, fldDefinedSize);
        else
            flds.Append(fldName, fldType);
    }

    rst.Open();

    return rst;
}
К великому моему сожалению, данный метод передачи рекордсета сводной таблице нельзя применить в случае ActiveX - OWC PivotTable. В его объектной модели, увы, отсутствует объект PivotCache...
Размещено в Без категории
Просмотров 155664 Комментарии 1
Всего комментариев 1

Комментарии

  1. Старый комментарий
    Аватар для Gustav
    К великой моей радости загрузка disconnected рекордсета в OWC PivotTable возможна!!
    Хотя и не через кэш (а нам, в принципе, какая разница?)

    См. Построение сводной таблицы на форме с загрузкой данных из ADODB.Recordset
    Запись от Gustav размещена 17.05.2010 в 19:28 Gustav is offline
 


Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 13:22.