近期學習MS CRM報表開發,順便學習RDLC&RDL報表使用過程的應用(學習資源主要參考MSDN和蠟人張)。
在AdventureWorks庫建立過程：
Create PROCEDURE [dbo].[pt_Employee](
@Title nvarchar(50),
@BirthDate datetime,
@VacationHours smallint
)
as
SELECT EmployeeID, Title, BirthDate, VacationHours, Gender, MaritalStatus
FROM 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)
RDL:
1、建立RptEmployeeProc.rdl RDL檔案,在查詢字元串輸入過程名pt_Employee,如圖:
2、接着為報表添加參數Title、BirthDate和VacationHours，其資料類型分别為String、DateTime和Integer，并設定參數為"隐藏"和"允許空值" !
3、然後在布局設計界面找到相應的資料集拉入相關字段,至此報表設計完成!設計完成時将報表釋出到ReportServer
4、建立RptEmployeeProc.aspx頁,如圖：
前台代碼為：
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>無标題頁</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<rsweb:reportviewer id="rvResult" runat="server" font-names="Verdana" font-size="8pt"
height="192px" processingmode="Remote" width="569px">
<ServerReport ReportPath="/DynamicReport/RptEmployeeProc"></ServerReport>
</rsweb:reportviewer>
<br />
<br />
<span style="font-size: 9pt" mce_style="font-size: 9pt"><strong>頭銜</strong> 包含 </span>
<asp:TextBox ID="txtTitle" runat="server"></asp:TextBox><span style="font-size: 9pt" mce_style="font-size: 9pt">
（文本，例：Production、Sales）<br />
<strong>出生日期</strong> 大于 </span>
<asp:TextBox ID="txtBirthDate" runat="server"></asp:TextBox><span style="font-size: 9pt" mce_style="font-size: 9pt">（日期，例：1980-1-1）<br />
<strong>假期</strong> 等于 </span>
<asp:TextBox ID="txtVacationHours" runat="server"></asp:TextBox><span style="font-size: 9pt" mce_style="font-size: 9pt">（數值，例：83）<br />
</span>
<asp:Button ID="btnQuery" runat="server" OnClick="btnQuery_Click" Text="查詢" Height="19px" Width="89px" /><span
style="font-size: 9pt">
<br />
<br />
</span>
</div>
</form>
</body>
</html>
其中注意reportviewer的processingmode和ServerReport屬性設定。
其背景代碼為：
public partial class RptEmployeeProc : System.Web.UI.Page
{
Microsoft.Reporting.WebForms.ReportParameter[] Paras = new Microsoft.Reporting.WebForms.ReportParameter[3];
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadClearParas();
}
}
//判斷字元串是否為整數
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)
{
//清除所有參數
LoadClearParas();
//設定參數
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);
}
}
}
private void LoadClearParas()
{
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);
}
}
RDLC：
1、建立資料集,将資料集命名為dsEmployee.xsd
在添加TableAdapter時選擇使用現有的存儲過程，在選擇過程的Select 項選擇 pt_Employee過程，然後點選下一步直到完成。
2、添加RDLC報表檔案并命名為EmployeeProc.rdlc,找到剛才dsEmployee的資料集,将相關字段在報表中進行設計
3、建立RptEmployee.aspx頁,其界面設計和RptEmployeeProc.aspx一緻.并增加ObjectDataSource控件,該控件業務對象之前建立的TableAdapter,另外該頁面中reportviewer設定如下：
<rsweb:reportviewer id="rvResult" runat="server" font-names="Verdana" font-size="8pt"
height="192px" processingmode="Remote" width="569px">
<ServerReport ReportPath="/DynamicReport/RptEmployeeProc"></ServerReport>
</rsweb:reportviewer>
其背景代碼為 ：
protected void btnQuery_Click(object sender, EventArgs e)
{
清除所有參數
ObjectDataSource1.SelectParameters.Clear();
ObjectDataSource1.SelectParameters.Add("Title", this.txtTitle.Text.Trim());
ObjectDataSource1.SelectParameters.Add("BirthDate", this.txtBirthDate.Text.Trim());
ObjectDataSource1.SelectParameters.Add("VacationHours", this.txtVacationHours.Text.Trim());
rvResult.LocalReport.Refresh();
}