Question

I have an SSRS grid report with a header row that SUM()s select child rows.

在问询中,一辆挂板的桌子与许多部分的桌子合并,即车面表上的一个小时数部分重复。我在小时手机上使用了“Hideplicas”SSRS特征,以直观的方式加以固定。问题是,虽然复制件是隐蔽的,但<代码>SUM(<>SUM(>功能(关于报告,而不是问询)仍然将复制件列入时段。

因此,我可能有以下表格和标题。时间段(和Shft)的重复是隐蔽的,但是仍然(不可容忍)列入传票。

SHIFT   HOURS     PART   ON HAND
=====   =======   ====   =======
        SUM:  8          SUM: 90
=====   =======   ====   =======
1st           3   X12Z        20
                  J23Z        10
2nd           1   Y36P        30
3rd           1   Q90F        30

我正在简化实际报告,这样以不同方式显示数据就是一种选择。而且,这些已经属于一个集团,因此我不敢肯定我能够增加另一个集团。我如何获得<代码>)功能,以退还所显示时间的总和?

EDIT:

我已经对我自己的问题作了彻底的答复,但如果能够改进这一或我的答案,请认为可以自由表达你的批评意见。如果仍有答案可以大大改进我的方法,我将接受这些答案。

Answer 1

鉴于这种格式在一份报告中大概是常见的,我预计会有更多的答案,或至少有更好的办法这样做。但是,我的方法如下:

Rather than implement a cursor and loop through the table, run this update on the original results in a stored procedure. It clears the values of duplicated fields (i.e., shift, hours) where the current row s part isn t the part of the first row returned for this Shift. If there is an order by in the original query, include it in the nested select:

update @tempResults
set    shift = null, -- Shift is a duplicated field.
       hours = null, -- Hours is a duplicated field.
where  part <> -- The part number identifies the duplicates.
       (
           select top 1 part
           from  #tempresults t
           where #tempresults.shift = t.shift -- Parts were grouped by shift.
           -- order by    the same fields from the original select statement.
       )

Essentially, the formula is to:

Nullify the fields in the "parent" table (the 1 in the 1:m relationship).
Set the update s where using the "child" table s identifying field.
Set the select s where using the parent table s identifying field.

Answer 2

Use a stored procedure. Proceed as you started placing your result in a temp table #X. Open a cursor on your result and walk the rows. Whenever you do not get a change in your identifying columns, set the hours to null. Then return your result and your report should work as is.

友情链接