在 Group Properties 中添加排序和设置

按表达式=Sum(NetWrittenPremium)"排序订单=Z到A"

它们在过滤器中如下:

Expression = "=Sum(NetWrittenPremium)"运算符 = 前 N值 = 10

实际上我刚刚注意到 Total 行.... 这将无法正确计算总数，并且您无法在 tablix 的过滤器中使用聚合(否则会起作用).>

您***的办法是将其推送回服务器并在那里执行.

我不能直接在你的数据上测试这个，但它应该可以工作，我测试了一些类似的......

SELECT r.* FROM(选择 d.*，dense_rank() OVER(ORDER BY TotalNWP Desc) AS rnk从(选择不同ClassCode、YearNum、MonthNum, SUM(t.NetWrittenPremium) OVER (PARTITION BY ClassCode, YearNum, MonthNum) AS NetWrittenPremium, SUM(t.NetWrittenPremium) OVER (PARTITION BY ClassCode) AS TotalNWPFROM cte_TopClasses t) d) r哪里 rnk 

现在应该不需要在 s-s-rS 中做任何过滤，只需按 rnk 列排序即可.

剩下的唯一问题是如何确定最后一个结果(它们的总数相同)中的哪一个优先于其他结果.你可以做一些事情，比如将 ClassCode 添加到dense_rank 函数中，以按字母顺序选择，但我猜这是由你决定的.

I need to display only top 10 Class based on Total (SUM(Premium)) column. I go to group ClassCode properties --> Filters and setting top 10 by SUM(NetWrittenPremium) but it doesnt work.

I need to display only top 10 and also the total amount should be for only those 10.

Cant understand how to achieve it.

here is my query:

;WITH cte_TopClasses
AS  ( 
SELECT

        c.YearNum,
        c.MonthNum,
        DD.ClassCode,
        ISNULL(SUM(prm.Premium),0) as NetWrittenPremium 

FROM    tblCalendar c
LEFT JOIN  ProductionReportMetrics prm ON c.YearNum = YEAR(prm.EffectiveDate) and c.MonthNum = MONTH(prm.EffectiveDate) 
           AND CompanyGUID = '18E04C99-D796-4CFA-B1E7-28328321C8AD'      
LEFT JOIN [dbo].[Dynamic_Data_GLUnitedSpecialty] DD on prm.QuoteGUID = DD.QuoteGuid 
WHERE   ( c.YearNum = YEAR(GETDATE())-1 and c.MonthNum >= MONTH(GETDATE())+1 ) OR 
        ( c.YearNum = YEAR(GETDATE()) and c.MonthNum <= MONTH(GETDATE()) ) 
GROUP BY    c.YearNum,
            c.MonthNum,
            DD.ClassCode--,prm.Premium

    )
SELECT   ROW_NUMBER() OVER (PARTITION BY ClassCode ORDER BY NetWrittenPremium DESC),*
FROM cte_TopClasses

and my outcome from the query:

@Alan Thanks. Query output look like that:

If I add ClassCode in order by in dense_rank then $142,000 is not gonna be in a query. Which is not good.

Any other ideas? Maybe I can use partition function?

In Group Properties add a sort and set

Sort by expression "=Sum(NetWrittenPremium)"
Order = "Z to A"

Them in the filter as follows:

Expression = "=Sum(NetWrittenPremium)"
Operator = Top N
Value = 10

Actually I've just noticed the Total row.... This will not calculate the total correctly and you cannot use aggregations in filters on the tablix (which would have worked otherwise).

You best bet would be to push this back to the server and do it there.

I can't test this on your data directly but it should work, I tested on someting similar...

SELECT r.* FROM 
    (SELECT d.* 
            , dense_rank() OVER(ORDER BY TotalNWP Desc) AS rnk
        FROM 
            (SELECT DISTINCT
                ClassCode, YearNum, MonthNum
                , SUM(t.NetWrittenPremium) OVER (PARTITION BY ClassCode, YearNum, MonthNum) AS NetWrittenPremium
                , SUM(t.NetWrittenPremium) OVER (PARTITION BY ClassCode) AS TotalNWP
             FROM cte_TopClasses t
            ) d
    ) r
    WHERE rnk <=10

Now there should be no need to do any filtering in s-s-rS, just sorting by the rnk column.

You only remaining problem is how to determine which of the last results (which all have the same total) take precedent over the others. You could do something like adding ClassCode to the dense_rank function to the are chosen alphabetically but that;s for yo to decide I guess.