Блог

Я работаю над отчетом о взрыве дерева спецификации. Это одна из тех вещей, где у нее много применений, которые подпадают под одну общую идею: упростить визуализацию дерева спецификаций с информацией, которая интересует инженеров и бухгалтеров. Я работаю в SQL Server 2008r2 для MS Dynamics AX 2009.

Запрос выполняется нормально. Отчет выполняется нормально. Рекурсия выполняется нормально. Но чего не хватает, так это свертки веса. В AX поле веса обычно является удобным полем в главном элементе, которое обычно не используется ни для какого типа бизнес-логики. Однако мы используем его как фактор в нашем программном обеспечении CPQ, поэтому он должен быть несколько точным. Я пытаюсь добавить видимость к свертке веса, чтобы помочь в идентификации и коррекции весов в наших продуктах.

Поскольку у меня уже есть вес на единицу и вычисленный общий вес на строку спецификации в моем наборе данных (и они извлекаются правильно), моими первыми попытками было выполнить агрегацию в отчете SSRS. Однако я попытался использовать SUM(Fields!TotalWeight.Value, "BOMTreeDS", recursive) , в результате чего все уровни спецификации от текущей строки до всех глубин были подведены промежуточные итоги. Итак, если моя спецификация имеет 5 уровней {0,1,2,3,4}, и я смотрю на что-то на уровне 2, тогда он добавит веса из этой записи в 2, ее дочерние элементы в 3, а также дочерние элементы в 4. Это нехорошо. Веса в 4 уже учтены на уровне 3, и я определенно не хочу включать собственный вес элемента.

Итак, затем я попытался использовать групповую переменную с именем «GUID» в единственной и неповторимой группе «Подробности», в которой я выполняю рекурсию. И я попробовал формулу SUM(IIF(Variables!GUID.Value = Fields!PARENTGUID.Value, Fields!TotalWeight.Value, 0), "BOMTreeDS") . Я был довольно оптимистичен в этом отношении, потому что он вернул бы сумму весов только для непосредственных дочерних элементов. Я использовал переменную group, потому что не был уверен, как управлять сдвигом области видимости от этой строки ко всем строкам в наборе данных внутри функции IIF. В любом случае, это не сработало. Visual Studio сообщает мне, что мне не разрешено использовать групповые переменные внутри агрегатных функций.

Затем я переключил свое внимание на свой SQL-запрос в наборе данных. Если SSRS не хочет играть красиво, я могу сделать это в SQL Server. Я использую CTE для создания рекурсии и создаю идентификаторы GUID на лету.

Это довольно длинный запрос, но это фрагмент, выполняющий взрыв спецификации…

 WITH Hierarchy AS
(
    SELECT 
        CAST('' AS NVARCHAR(50)) AS PARENTGUID,
        CAST(NEWID() AS NVARCHAR(50)) AS GUID,
        PhysItem.*,
        CAST('' AS NVARCHAR(10)) AS ColorID,
        CAST(1 AS NUMERIC(28, 12)) AS BOMQTY,
        CAST(1 AS NUMERIC(28, 12)) AS QTY,
        0 AS Level
    FROM PhysItem
    WHERE 
        PhysItem.Item = @ItemId AND
        PhysItem.Color = @ColorId

    UNION ALL

    SELECT
        CAST(Hierarchy.GUID AS NVARCHAR(50)) AS PARENTGUID,
        CAST(NEWID() AS NVARCHAR(50)) AS GUID,
        PhysItem.DATAAREAID,
        PhysItem.Item,
        PhysItem.Color,
        PhysItem.ItemName,
        PhysItem.ItemGroup,
        PhysItem.ItemType,
        _Component.UNITID,
        CASE
                WHEN PhysItem.ItemGroup = 'PKG' OR PhysItem.ItemType = 'Service' THEN CAST(0 AS NUMERIC(28, 12))
                WHEN _Component.UNITID = 'lb' THEN 1
                ELSE PhysItem.NETWEIGHT
            END AS NETWEIGHT,
        CASE
                WHEN _Component.ITEMBOMID IS NOT NULL AND _Component.ITEMBOMID <> '' THEN _Component.BOMID
                ELSE PhysItem.BOMID
            END AS BOMID,
        _Invent.INVENTCOLORID,
        CAST(_Component.BOMQTY/_Component.BOMQTYSERIE AS NUMERIC(28, 12)),
        CAST(Hierarchy.QTY * _Component.BOMQTY/_Component.BOMQTYSERIE AS NUMERIC(28, 12)),
        Hierarchy.Level   1
    FROM dbo.BOM _Component
        INNER JOIN Hierarchy ON
            _Component.BOMID            = Hierarchy.BOMID
            AND _Component.DATAAREAID   = Hierarchy.DATAAREAID
        INNER JOIN dbo.INVENTDIM _Invent ON
            _Component.INVENTDIMID      = _Invent.INVENTDIMID
            AND _Component.DATAAREAID   = _Invent.DATAAREAID
        INNER JOIN PhysItem ON
            Hierarchy.DATAAREAID        = PhysItem.DATAAREAID
            AND _Component.ITEMID       = PhysItem.Item
            AND _Invent.INVENTCOLORID   = PhysItem.Color
)
 

Как вы можете видеть, я создаю идентификаторы GUID с помощью CAST(NEWID() КАК NVARCHAR(50)). Однако теперь у меня возникла новая проблема. Я создал подзапрос для суммирования дочерних элементов, и сравнения из GUID в PARENTGUID завершаются неудачно. Это особенно сбивает с толку по нескольким причинам.

1. Я вижу значения и вижу, что они совпадают.
2. Я указываю длину NVARCHAR на 50, поэтому это не должно быть проблемой длины.
3. SSRS успешно сопоставляет идентификаторы GUID с идентификаторами PARENTGUID в отчете и точно отображает спецификацию.

Так что да… все это для получения многоуровневого отчета о весах. На мой взгляд, я хотел бы видеть для каждого элемента значение, которое в данный момент установлено в главном элементе, и сумму весов ближайших дочерних элементов рядом с ним. Итак, кто-то может просмотреть отчет и сказать: «хммм … этот вес выглядит не совсем правильно».

Мысли?