Статистика по каждому коду номенклатуры. Как правильно построить Query?

[dynamax]

Добрый день, необходим совет.

дело в следующем: заказчик захотел форму в которой будет отображаться расход по подразделениям, центрам, целям (физически). Форма выглядит следующим образом:



У формы три DataSources: InventTrans, InventTransPosting, InventDim.

Я сделал на Datasource InventTrans методу SetupRange (посмотрел в smmActivities), вот код:

public void setupRanges()
{
    ;
    dsForInventTrans                    = InventTrans_ds.query().dataSourceTable(tablenum(InventTrans));


    if(!dsForInventDim)
    dsForInventDim                      = dsForInventTrans.addDataSource(tablenum(InventDim));
    else inventTrans_DS.query().dataSourceTable(tablenum(InventDim));
    dsForInventDim.relations(true);
    dsForInventDim.joinMode(JoinMode::ExistsJoin);

    if(!dsForInventTransPostingPhysical)
    dsForInventTransPostingPhysical     = dsForInventTrans.addDataSource(tablenum(inventTransPosting));
    else inventTrans_ds.query().dataSourceTable(tablenum(inventTransPosting));
    dsForInventTransPostingPhysical.relations(true);
    dsForInventTransPostingPhysical.joinMode(JoinMode::ExistsJoin);

    rangeForItemId          = SysQuery::findOrCreateRange(dsForInventTrans, fieldnum(InventTrans, itemId));
    rangePeriod            = SysQuery::findOrCreateRange(dsForInventTransPostingPhysical, fieldnum(InventTransPosting, TransDate));
}

executeQuery на етом же DataSource :

public void executeQuery()
{

    InventSum       inventSum;
    InventTable     inventTable;
    ;

    inventTrans_DS.setupRanges();

    // Enable/Disable ranges
    if (ItemId && department.value() == true)
    {
        rangeForItemId.enabled(true);
        rangePeriod.enabled(true);
        rangeForItemId.value(itemIdFilter);
        rangePeriod.value(SysQuery::range(StartDate, EndDate));

        dsForInventTrans.addSortField(fieldnum(inventTrans, itemId));
        dsForInventTrans.addSortField(fieldId2Ext(fieldnum(InventTransPosting, Dimension), 1));
        dsForInventTrans.orderMode(OrderMode::GroupBy);
    }
    super();    
}

и вот SQL-запрос, который формируется:

SELECT * FROM InventTrans GROUP BY InventTrans.ItemId ASC, InventTrans.InvoiceId ASC WHERE ((ItemId = N'M0001')) EXISTS JOIN * FROM InventDim WHERE InventTrans.inventDimId = InventDim.inventDimId EXISTS JOIN * FROM InventTransPosting WHERE InventTrans.InventTransId = InventTransPosting.InventTransId AND ((TransDate>={ts '2008-09-01 00:00:00.000'} AND TransDate<={ts '2009-09-28 00:00:00.000'}))

по каким-то причинам не происходит группировка по InventTransPosting.Dimension[1]. Если у кого-нибудь есть идеи, буду благодарен.


P.S. Группировка по Центру и Цели происходит таким-же образом? и как мне лучще всего выяснить Кол-во и Цену?

[lev]

dsForInventTrans.addSortField(fieldId2Ext(fieldnum(InventTransPosting, Dimension), 1));

я предполагаю что вы вот этой строчкой хотите сделать группировку...
не смог найти какой ds присваивается dsForInventTrans, но судя по названию это InventTrans, а не InventTransPosting, т.е. просто опечатка наверно

[lev]

посмотрел внимательнее...

dsForInventTrans.addSortField(fieldnum(inventTrans, itemId));
        dsForInventTrans.addSortField(fieldId2Ext(fieldnum(InventTransPosting, Dimension), 1));

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

[lev]

Цитата:

Сообщение от dynamax

Значит так:

dsForInventTrans.addSortField(fieldnum(inventTrans, itemId));
        //dsForInventTrans.addSortField(fieldId2Ext(fieldnum(InventTransPosting, Dimension), 1));
        dsForInventTrans.orderMode(OrderMode::GroupBy);

        dsForInventTransPostingPhysical.addSortField(fieldId2Ext(fieldnum(InventTransPosting, Dimension), 1));
        dsForInventTransPostingPhysical.orderMode(OrderMode::GroupBy);

какие результаты?
кстати про existjoin тоже правильное замечание..
не понял, заработало или нет..

[Murlin]

Легче по мойму рассказать что такое кубы

[Bishop]

Вам нужно "перестроить" запрос: InventTransPosting join InventTrans join InventDim.
Группировку наложите на поля InventTransPosting - ItemId и необходимую аналитику.
И все будет OK.

В текущем варианте вы вообще используете exists join для InventTransPosting и хотите увидеть какие-то данные этой таблицы - это некорректно.

Хотя... моя подсказка неверна - из InventTransPosting однозначно в InventTrans не перейти в случае разбиения лота на несколько проводок. Так что так не делайте ))
Если не нужна складская аналитика (я в вашем случае не увидел необходимости ее применения), то переделайте запрос в InventTrans join InventTransPosting, кол-во - сумма по полю InventTrans.Qty, стоимость - сумма по полю InventTrans.CostAmountPosted плюс сумма по InventTrans.CostAmountAdjustment.

[TasmanianDevil]

Повезло Вам, что он вообще заработал ...
Хотите группировку по полям InvnetTransPosting ?
Тогда извольте и dsForInventTransPostingPhysical'у указать sortMode аналогично InventTrans'у. И поле для группировки добавлять на нем, а не InventTrans'е (именно тут повезло, что у InventTrans'а оказалось поле с таким же кодом, как у Dimension[1] у InventTransPosting - обратите внимание group by InventTrans.InvoiceId, который Вы вообще не добавляли )

[dynamax]

Значит так ? :

dsForInventTrans.addSortField(fieldnum(inventTrans, itemId));        
        dsForInventTrans.orderMode(OrderMode::GroupBy);

        dsForInventTransPostingPhysical.addSortField(fieldId2Ext(fieldnum(InventTransPosting, Dimension), 1));
        dsForInventTransPostingPhysical.orderMode(OrderMode::GroupBy);

Вот SQL-запрос:

SELECT * FROM InventTrans GROUP BY InventTrans.ItemId ASC WHERE ((ItemId = N'M0001')) EXISTS JOIN * FROM InventDim WHERE InventTrans.inventDimId = InventDim.inventDimId EXISTS JOIN * FROM InventTransPosting GROUP BY InventTransPosting.Dimension[1] ASC WHERE InventTrans.InventTransId = InventTransPosting.InventTransId AND ((TransDate>={ts '2008-09-01 00:00:00.000'} AND TransDate<={ts '2009-09-28 00:00:00.000'}))

вроде бы все правильно, но в форме вижу только одну строчку, как-будто группирую по InvnetTrans.ItemId


Не увидел замечание про ExistJoin....

[dynamax]

JoinMode::InnerJoin

и SQL-запрос:

SELECT * FROM InventTrans GROUP BY InventTrans.ItemId ASC WHERE ((ItemId = N'M0001')) JOIN * FROM InventDim WHERE InventTrans.inventDimId = InventDim.inventDimId JOIN * FROM InventTransPosting GROUP BY InventTransPosting.Dimension[1] ASC WHERE InventTrans.InventTransId = InventTransPosting.InventTransId AND ((TransDate>={ts '2008-09-01 00:00:00.000'} AND TransDate<={ts '2009-09-28 00:00:00.000'}))

Корректное количество строк, но подразделение теперь не видно...

[lev]

ну у Вас же группировка, значит будут видны данные только в тех полях, по которым выполнена группировка. Значит вам нужно сделать суммирование количества, которое должно выводиться...
(sum(Qty))

P.S. напишите какое поле, какой таблицы должно показывать кол-во в Вашей форме

[dynamax]

Цитата:

Сообщение от lev

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

Я выполняю группировку по InventTrans.ItemId, TransPosting.Dimension[1], количество строк в форме правильное, но не отобржаются подразделения (InventTransPosting.Dimension[1]).

[TasmanianDevil]

Цитата:

Сообщение от dynamax

Я выполняю группировку по InventTrans.ItemId, TransPosting.Dimension[1], количество строк в форме правильное, но не отобржаются подразделения (InventTransPosting.Dimension[1]).

А вот это уже подлый ExistsJoin поработал

[dynamax]

Цитата:

Сообщение от TasmanianDevil

А вот это уже подлый ExistsJoin поработал

но у меня JoinMode::InnerJoin....

[lev]

вот пример:

qbds.addSelectionField(fieldNum(InventTrans, Qty), SelectionField::Sum);

[Vals]

Посмотрите Номенклатура/Запросы/Статистика, возможно наведёт вас на мысль.
И OLAP, насколько я помню, под подобную задачу там есть уже готовые примеры.

[dynamax]

Кол-во, цена, значение - все отображается нормально. Но подразделение (центр затрат, цель), к сожалению остаются незаполнены

и еще, если добавляю такой код:

inventTrans_DS.setupRanges();

    // Enable/Disable ranges
    if (ItemId && department.value() == false && costCenter.value() == false && purpose.value() == false)
    {
        rangeForItemId.enabled(true);
        //rangeForDepartment.enabled(false);
        rangePeriod.enabled(true);
        rangeForItemId.value(itemIdFilter);
        rangePeriod.value(SysQuery::range(StartDate, EndDate));

        dsForInventTrans.addSortField(fieldnum(inventTrans, itemId));
        dsForInventTrans.orderMode(OrderMode::GroupBy);
        info(strfmt("%1", 'First'));
    }

    else if (ItemId && department.value() == true && costCenter.value() == false && purpose.value() == false)
    {
        rangeForItemId.enabled(true);
        //rangeForDepartment.enabled(true);
        rangePeriod.enabled(true);
        rangeForItemId.value(itemIdFilter);
        rangePeriod.value(SysQuery::range(StartDate, EndDate));

        dsForInventTrans.addSortField(fieldnum(inventTrans, itemId));
        dsForInventTrans.orderMode(OrderMode::GroupBy);

        dsForInventTransPostingPhysical.addSortField(fieldId2Ext(fieldnum(InventTransPosting, Dimension), 1));
        dsForInventTransPostingPhysical.orderMode(OrderMode::GroupBy);


        dsForInventTrans.addSelectionField(fieldnum(InventTrans, Qty), SelectionField::Sum);
        info(strfmt("%1", 'Second'));
    }

то при активации/деактивации подразделения запрос остается одинаковым, хотя отладчик приземляется правильно

[dynamax]

вернулся вчера опять к теме, переменил методу executeQuery(). Теперь выглядит вот-так:

public void executeQuery()
{
    Query query                                                 = new Query();
    QueryRun                queryRun;
    QueryBuildDatasource    dsForInventTrans                    = query.addDataSource(tablenum(InventTrans));
    QueryBuildDatasource    dsForInventTransPosting             = dsForInventTrans.addDataSource(tablenum(InventTransPosting));

    QueryBuildRange         rangeForItemId                      = dsForInventTrans.addRange(fieldnum(InventTrans,ItemId));
    QueryBuildRange         rangeForPeriod                      = dsForInventTransPosting.addRange(fieldnum(InventTransPosting, TransDate));
    ;

    dsForInventTransPosting.relations(true);
    dsForInventTransPosting.joinMode(JoinMode::InnerJoin);
    
    rangeForItemId.enabled(true);
    rangeForPeriod.enabled(true);
    rangeForItemId.value(itemIdFilter);
    rangeForPeriod.value(SysQuery::range(StartDate, EndDate));
        
        
    //ItemId
    dsForInventTrans.addSortField(fieldnum(inventTrans, itemId));
    dsForInventTrans.addSelectionField(fieldnum(InventTrans, Qty), SelectionField::Sum);
    dsForInventTrans.addSelectionField(fieldnum(InventTrans, CostAmountPosted), SelectionField::Sum);
    dsForInventTrans.orderMode(OrderMode::GroupBy);
        
    //Dimension
    dsForInventTransPosting.addSortField(fieldId2Ext(fieldnum(InventTransPosting, Dimension), 1));
    dsForInventTransPosting.addSelectionField(fieldId2Ext(fieldnum(InventTransPosting, Dimension), 1));
    dsForInventTransPosting.orderMode(OrderMode::GroupBy);
    
    this.query(query);
    super();

}

Это SQL-запрос:

SELECT SUM(Qty), SUM(CostAmountPosted) FROM InventTrans GROUP BY InventTrans.ItemId ASC WHERE ((ItemId = N'M0003')) JOIN Dimension[1] FROM InventTransPosting GROUP BY InventTransPosting.Dimension[1] ASC WHERE InventTrans.InventTransId = InventTransPosting.InventTransId AND ((TransDate>={ts '2008-10-01 00:00:00.000'} AND TransDate<={ts '2009-10-14 00:00:00.000'}))

Вроде все правильно, но подразделение остается пустым. Если делаю refresh - F5, то показываются подразделения. Вернее, то одно на все строки, то другое, хотя их несколько. Где я ошибаюсь?

[dynamax]

Вопрос снимается!

я перегрузил init() и еxecuteQuery() второрго dataSource. Все работает!