我正在使用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] 删除。
我来说两句