SQL 根据参数显示按周或月分组的 SSRS 报告数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25454025/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Show data on SSRS report grouped week or month based on parameter
提问by user2896521
I'm trying to achieve the appropriate grouping to be shown on SSRSreport which is driven by the 'weekly' or 'monthly' parameter defined in SSRS(not a argument for sproc). For that I'm using following in the Category Groups expression for the field called "Date" (format is '2014-03-01' as example):
我正在尝试实现在SSRS报告中显示的适当分组，该分组由中定义的“每周”或“每月”参数驱动SSRS（不是 sproc 的参数）。为此，我在名为“日期”的字段的类别组表达式中使用以下内容（格式为“2014-03-01”作为示例）：
=IIF(
Parameters!date_range.value="Weekly",
DATEPART("week", Fields!Date.Value),
DATEPART("month", Fields!Date.Value)
)
This results in the following exception:
这导致以下异常：
The Value expression for the field ‘Date' contains an error: Argument 'DateValue' cannot be converted to type 'Date'. (rsRuntimeErrorInExpression). An error has occurred during report processing. (rsProcessingAborted)
字段“日期”的值表达式包含错误：参数“日期值”无法转换为“日期”类型。(rsRuntimeErrorInExpression)。报告处理期间发生错误。(rsProcessingAborted)
Why?
为什么？
回答by M.Ali
The easiest way to achieve this is to first of all write your query which pulls forward the results like this.
实现这一点的最简单方法是首先编写您的查询，它会像这样向前推结果。
SQL Server Query
SQL Server 查询
SELECT DATEPART(MONTH, Date_Column) AS [Monthly]
,DATEPART(WEEK, Date_Column) AS [Weekly]
,SUM(Some_Column) AS Total
FROM Table_Name
GROUP BY DATEPART(MONTH, Date_Column)
,DATEPART(WEEK, Date_Column)
SSRS Report
SSRS 报告
Add a Matrix Data region. Drag and drop Totalcolumn to DATA.
添加矩阵数据区域。将Total列拖放到DATA.
Create a Parameter say GROUP ONof Texttype, and provide values
创建参数发言权GROUP ON的Text类型，并提供价值
1) Weekly
2) Monthly
Now below in ROW GROUPSpane, right click the only visible Row Group and goto GROUP PROPERTIESIn GROUP ONsection put following expression.
现在在ROW GROUPS窗格下方，右键单击唯一可见的行组，然后转到GROUP PROPERTIES在GROUP ON部分中输入以下表达式。
=IIF(Parameters!Groupby.Value = "Monthly", Fields!Monthly.Value, Fields!Weekly.Value)
Use the exactly same Expression on Data region ROWSsection.
在数据区域ROWS部分使用完全相同的表达式。
For Column name you can use the following Expression...
对于列名，您可以使用以下表达式...
=IIF(Parameters!Groupby.Value = "Monthly", "Monthly", "Weekly")
and you are good to go.
你很高兴去。
Important Note
重要的提示
SSRS is a cool tool for data presentation, not so cool when it comes to Data manipulation, to get better performance do all sorts of Data Manipulation closer to source (database, SQL Server).
SSRS 是一个很酷的数据展示工具，在数据操作方面就不那么酷了，为了获得更好的性能，可以在更接近源（数据库、SQL Server）的地方进行各种数据操作。
All of the presentation stuff should be handled on SSRS.
所有的演示内容都应该在 SSRS 上处理。
回答by user219454
I recommend using DATEPART, but I have discovered myself that DATEPART in SSRS does not always work as described in DATEPART (SSIS Expression). Here are some examples with specific syntax that worked for me:
我建议使用 DATEPART，但我发现自己在 SSRS 中的 DATEPART 并不总是像DATEPART (SSIS Expression) 中描述的那样工作。以下是一些对我有用的具有特定语法的示例：
- =DATEPART("yyyy", Fields!Date.Value) for year.
- =DATEPART("m", Fields!Date.Value) for month.
- =DATEPART("ww", Fields!Request_Date.Value) for week.
- =DATEPART("yyyy", Fields!Date.Value) 年份。
- =DATEPART("m", Fields!Date.Value) 月份。
- =DATEPART("ww", Fields!Request_Date.Value) 一周。
回答by nshah
Try something like:
尝试类似：
Format(Fields!date.Value,"yyyy-MM-dd")
Is this SQL Server 2014?
这是 SQL Server 2014 吗？
LinkIn SQL Server 2014, DATEPART implicitly casts string literals as a datetime2 type. This means that DATEPART does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.
Link在 SQL Server 2014 中，DATEPART 将字符串文字隐式转换为 datetime2 类型。这意味着当日期作为字符串传递时，DATEPART 不支持 YDM 格式。您必须将字符串显式转换为 datetime 或 smalldatetime 类型才能使用 YDM 格式。
回答by whytheq
So following from @nshah suggestion convert the expression to this:
因此，遵循@nshah 的建议，将表达式转换为：
=IIF(
Parameters!date_range.value="Weekly",
DATEPART("week", format(Fields!Date.Value,"yyyy-MM-dd")),
DATEPART("month", format(Fields!Date.Value,"yyyy-MM-dd"))
)
