This query produces a SQL of the same data that is in the parts grid.

• Parts are identified using the column IsPart.
• If the row is not a part, it is a line item, variation, or order total.
• Line items totals only include parts directly attached to them. They do not include parts attached to their children.
• The report is only written to run on a single order. If you want to run it on a range or list of orders the query will need to be modified.
• Once you add the query to Control as a New SQL Report, the Parameter Type should be Order.

None. This is a selection query and no data is modified in the running of it.

DECLARE @TransHeaderID INT
SET @TransHeaderID = ;
DECLARE @IncludeVariationRow bit;
SET @IncludeVariationRow = 0;
SELECT FormattedName AS Line,
 CASE WHEN IsTransPart = 1 THEN 'Yes' ELSE 'No' END AS IsPart,
 COALESCE('Part: '+PartCode, NodeName) AS Item,
 CASE WHEN (IsTransPart=0 AND NodeClassTypeID=10100) THEN (SELECT SubTotalPrice FROM TransDetail WHERE ID = OrderTree.NodeID) ELSE NULL END AS CurrentPrice,
 COALESCE(CalculatedValue, SUM(CalculatedValue) OVER(PARTITION BY OrderTree.SortIndex)) AS CalculatedValue,
 COALESCE(EstimatedValue, SUM(EstimatedValue) OVER(PARTITION BY OrderTree.SortIndex)) AS EstimatedValue,
 COALESCE(ActualValue, SUM(ActualValue) OVER(PARTITION BY OrderTree.SortIndex)) AS ActualValue,
 COALESCE(UnitsText, '') AS Units,
 COALESCE(CalculatedCost, SUM(CalculatedCost) OVER(PARTITION BY OrderTree.SortIndex)) AS CalculatedCost,
 COALESCE(EstimatedCost, SUM(EstimatedCost) OVER(PARTITION BY OrderTree.SortIndex)) AS EstimatedCost,
 COALESCE(ActualCost, SUM(ActualCost) OVER(PARTITION BY OrderTree.SortIndex)) AS ActualCost,
 COALESCE(SuggestedPrice, SUM(SuggestedPrice) OVER(PARTITION BY OrderTree.SortIndex)) AS SuggestedPrice,
 COALESCE(ReferenceNumber, 0) AS ReferenceNumber,
 OrderTree.SortIndex +'/'+CAST(IsTransPart AS VARCHAR(1)) AS SortIndex,
 HasChildren, Depth,
 TransPart.ID AS TransPartID,
 TransPart.PartID, TransPart.UnitID,
 NodeID, NodeClassTypeID, OrderTree.TransHeaderID AS TransHeaderID
FROM
(
 SELECT
 CASE WHEN GrandChildItem.ID IS NOT NULL THEN SPACE(4 * 4)
 ELSE CASE WHEN ChildItem.ID IS NOT NULL THEN SPACE(4 * 3)
 ELSE CASE WHEN TopItem.ID IS NOT NULL THEN SPACE(4 * 2)
 ELSE CASE WHEN TV.ID IS NOT NULL THEN SPACE(4 * 1)
 ELSE CASE WHEN TH.ID IS NOT NULL THEN SPACE(4 * 0)
 END END END END END +
 COALESCE(GrandChildItem.LineItemNumber+'. '+GrandChildItem.GoodsItemCode,
 ChildItem.LineItemNumber+'. '+ChildItem.GoodsItemCode,
 TopItem.LineItemNumber+'. '+TopItem.GoodsItemCode,
 TV.VariationName,
 'Order '+CAST(TH.OrderNumber AS VARCHAR(12))) AS FormattedName,
 COALESCE(GrandChildItem.GoodsItemCode, ChildItem.GoodsItemCode, TopItem.GoodsItemCode, TV.VariationName, 'Order '+CAST(TH.OrderNumber AS VARCHAR(12))) AS NodeName,
 COALESCE(GrandChildItem.ID, ChildItem.ID, TopItem.ID, TV.ID, TH.ID) AS NodeID,
 COALESCE(GrandChildItem.ClassTypeID, ChildItem.ClassTypeID, TopItem.ClassTypeID, TV.ClassTypeID, TH.ClassTypeID) AS NodeClassTypeID,
 1 AS IsActive, -- just kept for compatibility
 CASE WHEN COALESCE(GrandChildItem.ChildItemCount, ChildItem.ChildItemCount, TopItem.ChildItemCount, 1) > 0 THEN 1 ELSE 0 END AS HasChildren,
 COALESCE(GrandChildItem.ID - GrandChildItem.ID + 3, ChildItem.ID - ChildItem.ID + 2, TopItem.ID - TopItem.ID + 1, 0, 0) AS Depth,
 COALESCE(GrandChildItem.GoodsItemCode, ChildItem.GoodsItemCode, TopItem.GoodsItemCode) AS ProductName,
 ('Order '+CAST(TH.OrderNumber AS VARCHAR(12)) + ' ' + CAST(TH.Description AS VARCHAR(100)) ) AS Category1Name,
 TV.VariationName AS Category2Name,
 COALESCE(ChildItem.GoodsItemCode, TopItem.GoodsItemCode) AS Category3Name,
 ChildItem.GoodsItemCode AS Category4Name,
 GrandChildItem.GoodsItemCode AS Category5Name,
 COALESCE(CAST(TH.OrderNumber AS VARCHAR(12)), '') + '/' +
 COALESCE(RIGHT('0000000'+CONVERT(VARCHAR(8), TV.SortIndex), 8) + '/', '') +
 COALESCE(RIGHT('0000000'+ltrim(CONVERT(VARCHAR(8), TopItem.LineItemIndex)), 8)+'/', '') +
 COALESCE(RIGHT('0000000'+ltrim(CONVERT(VARCHAR(8), ChildItem.LineItemIndex)), 8)+'/', '') +
 COALESCE(RIGHT('0000000'+ltrim(CONVERT(VARCHAR(8), GrandChildItem.LineItemIndex)), 8)+'/', '') AS SortIndex,
 TH.ID AS TransHeaderID,
 TV.ID AS TransVariationID,
 TopItem.ID AS TopItemID,
 ChildItem.ID AS ChildItemID,
 GrandChildItem.ID AS GrandChildItemID,
 COALESCE(CASE TH.TransactionType WHEN 1 THEN 'Ord ' WHEN 2 THEN 'Est ' ELSE 'Other ' END + CAST(TH.OrderNumber AS VARCHAR(10))+'\\', '') +
 COALESCE(TV.VariationName + '\\', '') +
 COALESCE(TopItem.LineItemNumber + '\\', '') +
 COALESCE(ChildItem.LineItemNumber + '\\', '') +
 COALESCE(GrandChildItem.LineItemNumber + '\\', '') FormattedPath
 FROM TransHeader AS TH
 JOIN (SELECT 1 AS IsHeader UNION SELECT 0) TempH ON 1=1 -- create two rows for each of the above
 LEFT JOIN TransVariation AS TV ON TV.ParentID = TH.ID AND TempH.IsHeader = 0
 JOIN (SELECT 1 AS IsVariation UNION SELECT 0) TempV ON 1=1
 LEFT JOIN TransDetail AS TopItem ON TopItem.ParentID = TH.ID AND TopItem.VariationID = TV.ID AND TempV.IsVariation = 0
 JOIN (SELECT 1 AS IsTopItem UNION SELECT 0) TempTI ON 1=1
 LEFT JOIN TransDetail AS ChildItem ON ChildItem.ParentID = TopItem.ID AND ChildItem.VariationID = TV.ID AND TempTI.IsTopItem = 0
 JOIN (SELECT 1 AS IsChildItem UNION SELECT 0) TempCI ON 1=1
 LEFT JOIN TransDetail AS GrandChildItem ON GrandChildItem.ParentID = GrandChildItem.ID AND GrandChildItem.VariationID = TV.ID
 JOIN (SELECT 1 AS IsGrandChildItem UNION SELECT 0) TempGCI ON 1=1
 WHERE (TH.ID = @TransHeaderID) AND (IsHeader+IsVariation+IsTopItem+IsChildItem+IsGrandChildItem=1)
 AND ((IsHeader=1 AND TV.ID IS NULL)
 OR (IsVariation=1 AND TopItem.ID IS NULL)
 OR (IsTopItem=1 AND TopItem.ID IS NOT NULL)
 OR (IsChildItem=1 AND ChildItem.ID IS NOT NULL)
 OR (IsGrandChildItem=1 AND GrandChildItem.ID IS NOT NULL)
 )
 AND (@IncludeVariationRow = 1 OR IsVariation = 0)
) OrderTree
JOIN (SELECT 1 AS IsTransPart UNION SELECT 0) TempTP ON 1=1 -- create two rows for each of the above
FULL OUTER JOIN
(
 SELECT *
 FROM TransPart
 WHERE TransPart.TransHeaderID = @TransHeaderID
 ) AS TransPart
 ON TransPart.TransDetailID = OrderTree.NodeID AND OrderTree.NodeClassTypeID = 10100 AND IsTransPart = 1
LEFT JOIN Part ON Part.ID = TransPart.PartID
LEFT JOIN Inventory ON Inventory.ID = Part.InventoryID
WHERE (IsTransPart=0 OR TransPart.ID IS NOT NULL)
ORDER BY SortIndex, IsTransPart

• Entered : 1/8/2010
• Version : Control 4.0+