Reporting Services 参数约束 [英] Reporting Services Parameter Constraint

问题描述

我有一份报告服务 (SQL 2008) 报告，其中包含两个日期/时间参数 - begindate 和 enddate.我需要将结束日期限制为与开始日期相同的月份和年份.这似乎应该是一件容易的事情，但我无法弄清楚.

I have a reporting services (SQL 2008) report with two Date/Time parameters - begindate and enddate. I need to constrain enddate to the same month and year as begindate. This seems like it should be an easy thing to do, but I cannot figure it out.

目前，我正在检查传递给存储过程的参数，如果两个日期时间参数不在同一个月份和年份，则会引发错误.我正在寻找一种更优雅的方式来实现这一点.

Currently, I am checking the parameters passed to the stored procedure and raising an error if the two datetime parameters are not in the same month and year. I am looking for a more elegant way of accomplishing this.

推荐答案

可以检查参数表达式中的EndDate值，如果不正确，设置为StartDate + 1 Month.
类似的东西:

You can check the EndDate value in parameter expression, and if it's incorrect, set it to StartDate + 1 Month.
Something like:

= IIF(DateDiff(DateInterval.Month, Parameters!StartDate.Value, Parameters!EndDate.Value) = 0, Parameters!EndDate.Value, AddDate(DateInterval.Month, 1, Parameters!StartDate.Value))

如果你只是想通知用户，你可以放置一些带有适当格式(红色大字体)的隐藏文本框和关于日期参数不正确范围的消息.在隐藏表达式集中

If you just want notify user, you can place some hidden text box with appropriate formatting (red big font) and message about date parameters incorrect range. In Hidden expression set

= (DateDiff(DateInterval.Month, Parameters!StartDate.Value, Parameters!EndDate.Value) <> 0)

此外，您可以将这两个操作与自定义代码结合起来:

Also, you can combine both actions with custom code:

Public DateMessage As String

Public Function ValidateDate(StartDate As DateTime, EndDate As DateTime) As DateTime
  Dim ResultDate As DateTime
  If (DateDiff(DateInterval.Month, StartDate, EndDate) <> 0) Then
    ResultDate = AddDate(DateInterval.Month, 1, StartDate)
    DateMessage = String.Format("End Date parameter value {0} 
      was out of range and was changed to {1}", EndDate, ResultDate)
  Else
    ResultDate = EndDate
  End If
End Function

然后，在参数值表达式中:

Then, in Parameter value expression:

= Code.ValidateDate(Parameters!StartDate.Value, Parameters!EndDate.Value)

在 tbDateParameterMessage 文本框的 Value 属性中:

In Value property of tbDateParameterMessage textbox:

= Code.DateMessage

在隐藏属性表达式中:

= String.IsNullOrEmpty(Code.DateMessage)

编辑但如果您想停止运行报告，请使用此自定义代码:

EDIT But if you want to stop report running, use this custom code:

Public Function CheckDate(SDate as Date, EDate as Date) as Integer
    Dim msg as String
    msg = ""
    If (SDate > EDate)  Then
        msg="Start Date should not be later than End Date"
    End If
    If msg <> "" Then
        MsgBox(msg, 16, "Parameter Validation Error")
        Err.Raise(6,Report) 'Raise an overflow
    End If
End Function

摘自 SQLServerCentral 论坛.

这篇关于Reporting Services 参数约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！