在SSRS中按顺序对数字列进行排序

smul86

我正在使用Microsoft SQL Server 2012和Visual Studio2012。在我的SQL代码中,我开发了AgeBucket列,该列基本上将计算请求的开始日期和结束日期之间的天数。我的代码如下(对于冗长的代码,我事先表示歉意。)

它可以正常工作,当我尝试将其引入SSRS报告的数据集中时,它也可以工作,但是在列中,数字显示为乱序。它显示6-10作为最后一列,而按AgeBucket分组时,所有其他列似乎都是按顺序排列的。我意识到SSRS可能正在查看每列的第一个数字,这就是为什么6-10被排在最后的原因。

我的问题是,如何在SSRS中对这些数字进行排序?我的ColumnGroups中有AgeBucket列,但不确定最好的排序方式以按顺序在正确的列中获取信息。

         CASE WHEN 
            CASE WHEN A.[EndDate] > A.[StartDate] THEN C2.[BusinessCalendarDay] - C1.[BusinessCalendarDay]  
                 WHEN A.[EndDate] IS NULL and A.[StartDate] IS NOT NULL THEN C3.[BusinessCalendarDay] - C1.[BusinessCalendarDay]
                 WHEN A.[EndDate] = A.[StartDate] THEN 1
         END BETWEEN 0 AND 6
         THEN '0-5'
         WHEN 
            CASE WHEN A.[EndDate] > A.[StartDate] THEN C2.[BusinessCalendarDay] - C1.[BusinessCalendarDay]  
                 WHEN A.[EndDate] IS NULL and A.[StartDate] IS NOT NULL THEN C3.[BusinessCalendarDay] - C1.[BusinessCalendarDay]
                 WHEN A.[EndDate] = A.[StartDate] THEN 1
         END BETWEEN 6 AND 10
         THEN '6-10'
         WHEN
            CASE WHEN A.[EndDate] > A.[StartDate] THEN C2.[BusinessCalendarDay] - C1.[BusinessCalendarDay]  
                 WHEN A.[EndDate] IS NULL and A.[StartDate] IS NOT NULL THEN C3.[BusinessCalendarDay] - C1.[BusinessCalendarDay]
                 WHEN A.[EndDate] = A.[StartDate] THEN 1
         END BETWEEN 11 AND 15
         THEN '11-15'
         WHEN
            CASE WHEN A.[EndDate] > A.[StartDate] THEN C2.[BusinessCalendarDay] - C1.[BusinessCalendarDay]  
                 WHEN A.[EndDate] IS NULL and A.[StartDate] IS NOT NULL THEN C3.[BusinessCalendarDay] - C1.[BusinessCalendarDay]
                 WHEN A.[EndDate] = A.[StartDate] THEN 1
         END BETWEEN 16 AND 20
         THEN '16-20'
         WHEN
            CASE WHEN A.[EndDate] > A.[StartDate] THEN C2.[BusinessCalendarDay] - C1.[BusinessCalendarDay]  
                 WHEN A.[EndDate] IS NULL and A.[StartDate] IS NOT NULL THEN C3.[BusinessCalendarDay] - C1.[BusinessCalendarDay]
                 WHEN A.[EndDate] = A.[StartDate] THEN 1
         END BETWEEN 21 AND 25
         THEN '21-25'
         WHEN
            CASE WHEN A.[EndDate] > A.[StartDate] THEN C2.[BusinessCalendarDay] - C1.[BusinessCalendarDay]  
                 WHEN A.[EndDate] IS NULL and A.[StartDate] IS NOT NULL THEN C3.[BusinessCalendarDay] - C1.[BusinessCalendarDay]
                 WHEN A.[EndDate] = A.[StartDate] THEN 1
         END BETWEEN 26 AND 30
         THEN '26-30'
         WHEN
            CASE WHEN A.[EndDate] > A.[StartDate] THEN C2.[BusinessCalendarDay] - C1.[BusinessCalendarDay]  
                 WHEN A.[EndDate] IS NULL and A.[StartDate] IS NOT NULL THEN C3.[BusinessCalendarDay] - C1.[BusinessCalendarDay]
                 WHEN A.[EndDate] = A.[StartDate] THEN 1
         END BETWEEN 31 AND 35
         THEN '31-35'
         WHEN
            CASE WHEN A.[EndDate] > A.[StartDate] THEN C2.[BusinessCalendarDay] - C1.[BusinessCalendarDay]  
                 WHEN A.[EndDate] IS NULL and A.[StartDate] IS NOT NULL THEN C3.[BusinessCalendarDay] - C1.[BusinessCalendarDay]
                 WHEN A.[EndDate] = A.[StartDate] THEN 1
         END BETWEEN 35 AND 40
         THEN '35-40'
         WHEN
            CASE WHEN A.[EndDate] > A.[StartDate] THEN C2.[BusinessCalendarDay] - C1.[BusinessCalendarDay]  
                 WHEN A.[EndDate] IS NULL and A.[StartDate] IS NOT NULL THEN C3.[BusinessCalendarDay] - C1.[BusinessCalendarDay]
                 WHEN A.[EndDate] = A.[StartDate] THEN 1
         END > 40
         THEN '41+'
    END AS AgeBucket
    FROM CAST(A.StartDate AS DATE) = C1.[CAL_DT]        
    LEFT JOIN [dbo].[DT_DIM] AS C2 
    ON CAST(A.EndDate AS DATE) = C2.[CAL_DT]        
    LEFT JOIN [dbo].[DT_DIM] AS C3 
    ON CAST(GETDATE() AS DATE) = C3.[CAL_DT]
汉诺威拳

您是正确的-因为您的字段是文本字段,所以它按第一个字符排序。

我只会在SSRS中您小组SORT使用IIF语句

=IIF(Fields!AgeBucket.Value = "0-5",   1, 
 IIF(Fields!AgeBucket.Value = "6-10",  2, 
 IIF(Fields!AgeBucket.Value = "11-15", 3, 
 IIF(Fields!AgeBucket.Value = "16-20", 4, 
 IIF(Fields!AgeBucket.Value = "21-25", 5, 
 IIF(Fields!AgeBucket.Value = "26-30", 6, 
 IIF(Fields!AgeBucket.Value = "31-35", 7, 
 IIF(Fields!AgeBucket.Value = "36-40", 8, 
 IIF(Fields!AgeBucket.Value = "41+", 9, 10)

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

如何在C ++中从对数字进行排序到按字母顺序进行排序

在 linux 中按字母数字顺序对数字进行优先排序

MySQL-按字母顺序对数字进行排序

用数字按字母顺序对数组进行排序

如何按数字顺序对数据路径进行排序?

按r中的精确数字顺序对数据集进行排序

按字母顺序对数据表进行字母排序,然后按R中的数字值排序

Excel按数字对数字列进行排序

按R中的特定顺序对数据帧中的列进行排序

Numpy Python:按字母顺序对数组的列进行排序

按给定列表中的ID顺序对数据进行排序

在R中按特定顺序对数据进行排序

Javascript在Oracle中按顺序对数组进行排序

R:按字母数字列对数据框中的所有列进行排序

按数字列对数据框进行排序

按数字顺序对包含文本和数字的列进行排序

按数字对数字进行排序

按特定顺序对数组进行排序

按定义的顺序对数组进行排序

按特定顺序对数组进行排序

按特定顺序对数组进行排序

C ++:如何按数字顺序对数字字符串向量进行排序?

在数组perl中按数字对数字和按字母顺序排序

如何按范围对数字列表进行排序并按时间顺序对结果进行分组

根据一列中的值(以另一列为条件)按顺序对数据进行排序

按匹配日期对数字进行排序

按数字对数据框进行排序

如何在外壳脚本中按字母顺序对第二列进行排序,然后按数字排序?

如何在数据透视表中按字母数字顺序对列数据进行排序?