分层 SQL 查询、SSRS 2008 和求和列

Question

我正在构建 SSRS 报告，但在弄清楚如何实现它方面遇到了一些困难。

首先，这是数据的基本结构：

• Level 1a
  • 2a 级
  • 2b级
  • 2c 级
    • 3a级
• Level 1b
• Level 1c

在数据库中，每个级别都有一个 id 和一个父 id，其中根（级别 1 的父级）的所有父 id 均为 0。此外，每个级别都有一个包含数值的列。例如，我可能：

Level1a
ParentId = 0
Id = 1
DataValue = 42

Level2a
ParentId = 1
Id = 2
DataValue = 1

目前，我已经实现了一个公共表表达式来获取数据的层次结构和级别：

WITH cteTable(id, parentId, data, level) AS 
(SELECT id, 
        parentId, 
        data, 
        1 AS level 
FROM sampleTable 
WHERE (parentId = 0) 
UNION ALL 
SELECT b.id, 
       b.parentId, 
       b.data, 
       c.level + 1 AS Expr1 
FROM sampleTable AS b 
INNER JOIN cteTable AS c ON b.parentId= p.id
) 
SELECT id, 
       parentId,  
       data, 
       level 
FROM cteTable

因此，使用之前的示例数据，查询的结果将是：

+----+----------+------+-------+
| id | parentId | data | level |
|----+----------+------+-------+
| 1  | 0        | 42   | 1     |
| 2  | 1        | 1    | 2     |
+----+----------+------+-------+

从这里，我需要构建一个报告，将数据汇总到根级别。例如，报告将显示 id 1 的数据总和为 43。我不确定的一件事是级别有多深 - 在某些情况下没有子级别，而在其他情况下树的位置深几层。

我现在正在考虑两个选择。一种是创建一个深入报告，该报告将显示每个级别的数据以及可用的总和。例如：

-Level1a SUM_LEVEL2_AND_LEVEL1a_DATA
   Level2a DATA
   Level2b DATA
  -Level2c SUM_LEVEL3_AND_LEVEL2c_DATA 
      Level3a DATA
Level1b DATA
Level1c DATA

另一种是将数据求和到根并在报表中显示一个简单的表格，仅包含父级总计。例如：

Level1a SUM_LEVEL1A_AND_ALL_CHILDREN_DATA
Level1b SUM_LEVEL1B_AND_ALL_CHILDREN_DATA
Level1c SUM_LEVEL1C_AND_ALL_CHILDREN_DATA

我似乎无法弄清楚如何使用我所做的谷歌搜索来获取深入报告（有一个链接here 这似乎很有用，但我就是无法让它工作）。

更改数据库架构不是一个选项。

有谁知道我可以使用哪些好的资源来开始，或者对如何继续进行有任何想法？

如果我遗漏了什么，请告诉我......

Answer 1

我终于找到了解决这个问题的方法。

公共表表达式 (CTE) 是一个良好的开端。我对其所做的唯一更改是在递归定义中 - 我没有为每个孩子收集直接父母的 id，而是为其分配了祖父母的 id（直接父母拥有的父母 id）。这意味着每个孩子都被分配了最终的父 ID，而不是仅在层次结构中向上移动一级。从那时起，在报告中使用它就非常简单了。

我将 CTE 放在它自己的存储过程中 - 唯一的目的是恢复整个层次结构。从那里，我创建了另一个存储过程，它将 CTE 与另一个存储过程结合起来。此时，我应用了过滤器并返回了最终结果集。