Reporting Services 6: 在服务器端报表中筛选数据

   这两天和一个朋友讨论了一些关于报表的问题，问题的焦点集中在如何在报表中显示动态数据，这里所谓的动态数据是指如何筛选数据，数据的行是动态的，而列是固定的，可变列的报表的实际意义并不大。园子里已经有两篇相关的文章——

   使用WebService动态生成DataSet绑定到Reporting Services（小新0574）

   VS2005＋SQL2005 Reporting Service动态绑定报表（Web）（Carlwave-陆飞(Fei.Lu)）

   这两篇文章中，前者是针对ServerReport的，后者是针对LocalReport的。对于LocalReport来说，我在我的RDLC系列随笔的示例中已经指出了：由于LocalReport可以通过this.reportViewer1.LocalReport.DataSources.Add方法添加一个数据源，只需要直接对数据源进行操作就可以实现对数据的筛选。而ServerReport是不可以的，为此，小新0574使用Web Services为报表提供XML类型的数据源，这是一个不错的方法，但是，Web Services本身并不是为这种应用而设计的，这种做法可能带来一定的问题。

   另外，由于之前我发过一篇随笔Reporting Services 2: 参数化报表，不少朋友在我的Blog上反映，SSRS 2005的参数化报表的参数设计的太死了，用户必须将所有的参数都选择一遍才可以查看报表。

   以上算是一个引子，本随笔将分为两个部分（两个示例）解决同质的两个问题：1、在Web项目中使用ServerReport时如何筛选数据；2、如何使参数化报表更方便地筛选数据。为此，本随笔将提供一个使用ServerReport的Web项目示例以及一个参数化报表示例。至于这两个问题如何同质，看完这篇随笔，您就明白了。

   本随笔的两个示例都使用SQL Server 2005示例数据库AdventureWorks，报表数据来源于表HumanResources.Employee。这个表中的字段主要有三种常见的数据类型：文本、数值和日期时间，本随笔讨论的字段的数据类型只限于这三大类数据类型。示例将使用Title、BirthDate和VacationHours三个字段对数据进行筛选。

   先来看一下第一个示例：在Web项目中使用ServerReport并筛选数据，为此，我们需要使用BIDS/VS 2005创建一个报表服务器项目DynamicReport，为其添加共享数据源AdventureWorks.rds，并新建一个报表RptEmployee.rdl。

   接下来，我们为报表添加参数Title、BirthDate和VacationHours，其数据类型分别为String、DateTime和Integer，需要注意的是在这几个参数的创建过程中，要选中“隐藏”和“允许空值”两个默认情况下没有选中的复选框（如图1所示）：“隐藏”复选框被选中，说明报表不会以参数化报表的样式在顶部显示筛选条件控件以及“查看报表”按钮；“允许空值”复选框被选中，说明报表的参数是可以为空的。

图1 新建参数

   然后，我们进入报表RptEmployee.rdl的“数据”选项卡，切换到通用查询设计起界面，在“关系图”窗格中添加表HumanResources.Employee，并勾选EmployeeId、Title、BirthDate、VacationHours、Gender以及MaritalStatus等字段作为输出，然后在“条件”窗格中为Title、BirthDate、VacationHours指定筛选器如图2所示：

图2 “条件”窗格中的“筛选器”

   此时，我们在“SQL”窗口中可以看到如下的SQL语句：

代码1：SQL窗格中的SQL语句
SELECT 
        EmployeeID, 
        Title, 
        BirthDate, 
        VacationHours, 
        Gender, 
        MaritalStatus
  FROM [AdventureWorks].[HumanResources].[Employee]
  WHERE
      ---------------------------------------------------------------------------------
      (Title LIKE N'%' + CASE ISNULL(@Title, '') WHEN '' THEN '' ELSE @Title END + '%')
      ---------------------------------------------------------------------------------
    AND
      ---------------------------------------------------------------------------------
      (BirthDate > CASE ISDATE(@BirthDate) WHEN 0 THEN CONVERT(DATETIME, '1900-01-01 00:00:00', 102) ELSE @BirthDate END)
      ---------------------------------------------------------------------------------
    AND
      ---------------------------------------------------------------------------------
      (VacationHours = CASE ISNUMERIC(@VacationHours) WHEN 0 THEN VacationHours ELSE @VacationHours END )
      ---------------------------------------------------------------------------------

   在上面的语句中，以“@”开头的变量为在报表中定义的参数，语句的作用在于允许用户不指定参数的值，而查询依然可以运行。对于这个语句，不想多说，对于其它数据类型以及其它可能用到运算符，也不想进一步进行剖析，希望大家可以举一反三写出适合自己应用的SQL语句。

   接着，切换到“布局”页面进行简单的报表布局设计，只需要拖入一个表格控件并将数据集中的字段都包含进去就可以了。

   至此，适合我们在Web项目中使用的服务器端报表就设计完成了，将其部署到报表服务器上。

   接下来看一下我们如何在Web项目的页面中使用这个服务器端报表并允许在页面中筛选数据。

   首先，建立一个网站DynamicReportWebApp，按照图3所示对页面Default.aspx进行设计。

图3 Default.aspx布局设计

   然后，在Default.aspx.cs中添加以下代码：

代码2：Default.aspx.cs中的代码
    //判断字符串是否为整数
    private bool IsInteger(string s)
    {
        string pattern = @"^\d*$";
        return System.Text.RegularExpressions.Regex.IsMatch(s, pattern);
    }

    //判断字符串是否为一个合法的日期
    private bool IsDate(string s)
    {
        string pattern = @"^((\d{2}(([02468][048])|([13579][26]))[\-\/\s]?((((0?[13578])|(1[02]))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\-\/\s]?((0?[1-9])|([1-2][0-9])))))|(\d{2}(([02468][1235679])|([13579][01345789]))[\-\/\s]?((((0?[13578])|(1[02]))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\-\/\s]?((0?[1-9])|(1[0-9])|(2[0-8]))))))(\s(((0?[1-9])|(1[0-2]))\:([0-5][0-9])((\s)|(\:([0-5][0-9])\s))([AM|PM|am|pm]{2,2})))?$";
        return System.Text.RegularExpressions.Regex.IsMatch(s, pattern);
    }
        
    protected void btnQuery_Click(object sender, EventArgs e)
    {
        //清除所有参数
        Microsoft.Reporting.WebForms.ReportParameter[] Paras = new Microsoft.Reporting.WebForms.ReportParameter[3];
        Paras[0] = new Microsoft.Reporting.WebForms.ReportParameter("Title", new string[1] { null});
        Paras[1] = new Microsoft.Reporting.WebForms.ReportParameter("BirthDate", new string[1] { null });
        Paras[2] = new Microsoft.Reporting.WebForms.ReportParameter("VacationHours", new string[1] { null });
        this.rvResult.ServerReport.SetParameters(Paras);

        //设置参数
        if (this.txtTitle.Text.Trim() != string.Empty)
        {
            Paras = new Microsoft.Reporting.WebForms.ReportParameter[1];
            Paras[0] = new Microsoft.Reporting.WebForms.ReportParameter("Title", this.txtTitle.Text.Trim());
            this.rvResult.ServerReport.SetParameters(Paras);
        }
        if (this.txtBirthDate.Text.Trim() != string.Empty)
        {
            if (this.IsDate(this.txtBirthDate.Text.Trim()))
            {
                Paras = new Microsoft.Reporting.WebForms.ReportParameter[1];
                Paras[0] = new Microsoft.Reporting.WebForms.ReportParameter("BirthDate", this.txtBirthDate.Text.Trim());
                this.rvResult.ServerReport.SetParameters(Paras);
            }
        }
        if (this.txtVacationHours.Text.Trim() != string.Empty)
        {
            if (this.IsInteger(this.txtVacationHours.Text.Trim()))
            {
                Paras = new Microsoft.Reporting.WebForms.ReportParameter[1];
                Paras[0] = new Microsoft.Reporting.WebForms.ReportParameter("VacationHours", this.txtVacationHours.Text.Trim());
                this.rvResult.ServerReport.SetParameters(Paras);
            }
        }
    }

    代码2的主要部分是查询按钮的Click事件，在该事件中，我们首先做的是清除报表的所有参数，在服务器端报表设计的时候，我们已经指出了所有参数都是允许空值的，当参数为空值的时候，该参数不参与运算，事实上，如果我们把筛选条件的运算符和字段的数据类型结合起来考虑，可以知道并不一定需要把参数清除为空值，例如，上述日期时间参数BirthDate上的运算符为“>”，我们把参数BirthDate设置为DateTime.MinValue（{0001-1-1 0:00:00}），从业务逻辑上来看也是可行的。但是，我们还是应该尽量把不参加运算的参数清空为空值，一来可以减少SQL Server的运算，二来更符合通用的业务逻辑。需要注意的是，ReportParameter的构造函数中的value参数为一个字符串，而报表设计时指定的参数类型又不局限于字符串，从页面设置的参数传递到报表的过程中存在一个数据类型的转换。清空参数为空值的时候，应该使用ReportParameter的第四个构造函数，即代码2中所示的：Paras[1] = new Microsoft.Reporting.WebForms.ReportParameter("BirthDate", new string[1]{ null });。至于为什么，在未设置参数之前，监视一下this.rvResult.ServerReport.GetParameters()的返回值就知道了。而在指定一个非空参数时，可以使用ReportParameter的第三个构造函数，见代码2中的“//设置参数”部分。另外，至于以前有朋友提到的二次查询（在结果中筛选）也是可以实现的，在本段的开始，我们描述了清除报表所有参数的方法，之所以要清除报表的所有参数是因为每次设置后的参数都被ServerReport保存了，如果我们不清除或选择性地清除参数，就可以实现二次筛选的功能了。

   好了，来看一下程序的运行结果：

图4 示例1运行结果

   第一部分Demo下载
   注：解压本示例，部署DynamicReport文件夹中的报表服务器项目，并运行DynamicReportWebApp文件夹中的网站。

   我的随笔Reporting Services 2: 参数化报表提供的参数化报表中，每次查看报表时，需要填写所有参数才能够进行处理，即不能忽略报表参数进行数据筛选，也正因为如此，终端用户无法使用参数化报表进行二次查询。

   我们在上面所涉及到的服务器端报表也可以看作是一个参数化报表，只不过这些参数隐藏了，最终用户无法在报表运行时利用这些参数进行数据的筛选，那么如果将上述的这些参数显示，会出现什么结果呢？

   我们把报表服务器项目DynamicReport中的报表RptEmployee.rdl中的三个参数Title、BirthDate和VacationHours的“隐藏”复选框反选掉并为参数添加一些“提示”信息，如图5所示。

图5 修改后的参数

   保持RptEmployee.rdl的其它部分不变（主要是“数据”页面中的数据），会得到什么样的结果呢？在预览状态，我们得到图6所示的界面。

图6 报表预览界面（点击小图看大图）

   这个参数化报表基本上已经可以满足我们的需求了，就是说解决了忽略参数查询以及二次查询的问题。我们可以发现，每个参数的后面都添加了一个复选框，用于指定参数是否为空，这是由于我们在参数设置时选中了“允许空值”的对话框，怎么说呢，这种设计并不是一种很好的交互。当我们将VacationHours参数后面的NULL复选框反选后，而并不在前面的文本框中填写任何数字的时候，我们会得到“提供给报表参数“VacationHours”的值对其类型无效。”的错误，这个错误并不是由于我们对报表的数据源使用特殊的SQL语句造成的，事实上，在执行该SQL语句之前这个错误已经抛出了，也就是说，报表处理过程中会先对参数的值进行判断，然后才从数据库中取数据（废话！不然就不能将参数包含在筛选器中了），而参数VacationHours的数据类型为Integer，String.Empty是无法正确转换为一个Integer的。看来这里还存在着继续探索的空间。

   继续修改报表RptEmployee.rdl，将参数的数据类型都设置为String，同时反选“允许空值”复选框，此时我们得到的预览界面如图7所示。

图7 报表预览界面（点击小图看大图）

   此时，所有参数的取值都使用文本框控件输入，尽管这在交互上可能是一种倒退，但是至少此时无论赋予参数何种值或不赋值都不会引起上一步骤中的类型转换错误；如果给BirthDate赋予一个非法的日期字符串，BirthDate参数的取值将不作为筛选条件参加运算，这是由选择报表数据的SQL语句决定的。

   总体来说，本随笔提供的参数化报表示例已经可以对报表进行基本上符合要求的数据筛选了，有人可能会说Microsoft在这一部分上做的还是不够，对此，我的看法是：参数化报表只是Microsoft提供的一个简单的数据筛选方案，这种方案不能过于复杂，简单也有简单的美。如果你认为这种方案无法满足你的需求，还是使用本文的第一种方案吧，自己写的代码更容易将解决问题的主动权控制在自己手中。

   第二部分Demo下载

   本随笔提供的两个示例只能说是采用了一个变通的方法来扩展服务器端报表本身并不具备的功能，而主要方法是对选择数据的SQL语句作了一些手脚，事实上，对于选择报表数据的SQL语句进行充分发掘可以实现一些意想不到的功能，前一段时间写的RDL(C) Report Design Step by Step 3: Mail Label也在此列。