开发自己的SQL2005报表查看SharePoint WebPart（一）
SQL 2005自己提供的报表查看web part功能比较简单，不太适合中国人对报表界面的复杂要求。
这个WebPart的主要功能是提供新的参数录入方式，如日期型改用日历来选择。
第一步：建立一个新的web part
新建一个的dll工程BARreportWebPart
将class1.cs重命名为BARreportWebPart。
增加对System.web的引用，并将类BARreportWebPart从webpart派生。重写Render方法，控制自己的输出。
using System;
using System.Collections.Generic;
using System.Text;
using System.Web.UI;
using System.Web.UI.WebControls.WebParts;
namespace BARreportWebPart
{
public class BARreportWebPart : WebPart
{
protected override void Render(System.Web.UI.HtmlTextWriter writer)
{
writer.Write("This web part is a report view for SQL 2005 reporting services :)");
}
}
}
web part需要强名来注册到GAC中，用命令行 sn -k BARreportWebPart.snk 生成强名文件，在工程属性的签名页指定使用该文件作强名。
web part注册还需要两个注册信息文件,后缀为webpart和dwp。
新建一个xml文件，重命名为BARreportWebPart.webpart，内容为
<?xml version="1.0" encoding="utf-8"?>
<webParts>
<webPart xmlns="http://schemas.microsoft.com/WebPart/v3">
<metaData>
<type name="BARreportWebPart.BARreportWebPart, BARreportWebPart, Version=1.0.0.0, Culture=neutral, PublicKeyToken=6b51eec99acee804" />
<importErrorMessage>Cannot import this Web Part.</importErrorMessage>
</metaData>
<data>
<properties>
<property name="Title" type="string">BARreportWebPart</property>
</properties>
</data>
</webPart>
</webParts>
新建一个xml,重命名为BARreportWebPart.dwp，内容为
<?xml version="1.0" encoding="utf-8"?>
<WebPart xmlns="http://schemas.microsoft.com/WebPart/v2" >
<Title>BAR reports Web Part</Title>
<Description>The BAR report viewer web part</Description>
<Assembly>BARreportWebPart, Version=1.0.0.0, Culture=neutral, PublicKeyToken=6b51eec99acee804</Assembly>
<TypeName>BARreportWebPart.BARreportWebPart</TypeName>
<!-- Specify initial values for any additional base class or custom properties here. -->
</WebPart>
到现在，一个没有功能的web part已经完成了。下一步，我们需要把它配置到sharepoint站点上。
第二步 配置sharepoint，注册这个web part
编译工程，将文件拷贝到sharepoint服务器的GAC中。
修改sharepoint根站点的web.config（C:\Inetpub\wwwroot\wss\VirtualDirectories\80\web.config）,将BARreportWebPart的dll添加到SafeControls中
<SafeControls>
.......
<SafeControl Assembly="BARreportWebPart, Version=1.0.0.0, Culture=neutral, PublicKeyToken=6b51eec99acee804" Namespace="BARreportWebPart" TypeName="*" Safe="True" />
</SafeControls>
打开sharepoint站点，进入根站点的Site Settings，在Galleries中选择web part进入Web Part Gallery。
选择upload document,上传BARreportWebPart.webpart文件。一个新的web part被注册了。
需要重新启动IIS让这些设置生效
在（一）中，已经完成了一个sharepoint Web part的开发和部署。但是我们的web part还没有任何功能。现在，我们要开始将SQL reporting的报表显示功能加进来了。
访问SQL reporting报表有很多方法，这里我们选用的是调用Web service的方式。
在工程中增加对SQL reporting service的引用, http://ctc-bar:81/ReportServer/reportservice.asmx(ctc-bar:81是报表服务器名称和段口号)
为了简化对报表服务器的调用，我们新建一个ReportAdapter类负责对web service的调用。
using System;
using System.Collections.Generic;
using System.Text;
namespace BARreportWebPart
{
public class ReportAdapter
{
ReportingServer.ReportingService ReportSvr = null;
string reportServerURL = null;
string reportPath = null;
string format = "HTML4.0";
string zoom = "100";
string deviceInfo = "<DeviceInfo></DeviceInfo>";
string encoding = null;
string mimeType = null;
ReportingServer.ParameterValue[] outParameters = null;
ReportingServer.Warning[] warnings = null;
string[] streamIDs = null;
public ReportAdapter(string serverURL,string path)
{
reportServerURL = serverURL;
reportPath = path;
ReportSvr = new ReportingServer.ReportingService();
ReportSvr.Url = reportServerURL;
ReportSvr.Credentials = System.Net.CredentialCache.DefaultCredentials;
}
public ReportingServer.ReportParameter[] GetParameters()
{
return ReportSvr.GetReportParameters(reportPath, null, false, null, null);
}
public byte[] RenderReport(ReportingServer.ParameterValue[] parameterValues)
{
return ReportSvr.Render(reportPath, format, null, deviceInfo, parameterValues, null, null, out encoding, out mimeType, out outParameters, out warnings, out streamIDs);
}
}
}
这个类的构造函数用来指定要访问的报表serverURL是服务器的地址,path是报表的路径
GetParameters()可以返回报表的参数，我们可以自己构造参数输入的UI控件。
RenderReport用来返回查询报表的结果。
然后，我们在web part的render方法中调用ReportAdapter来查询报表
using System;
using System.Collections.Generic;
using System.Text;
using System.Web.UI;
using System.Web.UI.WebControls.WebParts;
namespace BARreportWebPart
{
public class BARreportWebPart : WebPart
{
public BARreportWebPart()
{
}
protected override void Render(System.Web.UI.HtmlTextWriter writer)
{
string reportServerURL = "http://ctc-bar:81/ReportServer/ReportService.asmx";
string reportPath = "/BARreports/EBCdetailList";
ReportAdapter rsAdapter = new ReportAdapter(reportServerURL, reportPath);
ReportingServer.ReportParameter[] parameters = rsAdapter.GetParameters();
ReportingServer.ParameterValue[] parameterValues = null;
if (parameters.Length > 0)
{
parameterValues = new ReportingServer.ParameterValue[parameters.Length];
for (int i = 0; i < parameters.Length; i++)
{
parameterValues[i] = new ReportingServer.ParameterValue();
parameterValues[i].Name = parameters[i].Name;
parameterValues[i].Label = parameters[i].Prompt;
}
parameterValues[0].Value = "2007-1-1";
parameterValues[1].Value = "2007-3-1";
}
System.Text.Encoding enc = System.Text.Encoding.UTF8;
byte[] result = rsAdapter.RenderReport(parameterValues);
string htmlResult = enc.GetString(result);
//htmlResult = htmlResult.Replace(reportServerURL.Replace("/ReportService.asmx", "?"), "http://" & Request("SERVER_NAME") & Request("SCRIPT_NAME") & "?Report=");
writer.Write(htmlResult);
}
}
}
我们在（一）里完成了web part的实现
在（二）里完成了通过调用SQL2005 reporting services的web service，查询并显示报表
现在，我们要给Web part增加属性来设置报表服务器的地址和报表路径
首先，为了能够在SharePoint中设置属性，需要增加对Microsoft.SharePoint的引用，原来我们的 BARreportWebPart是从System.Web.UI.WebControls.WebParts.WebPart继承的，现在要改为从 Microsoft.SharePoint.WebPartPages.WebPart继承。否则你在Sharepoint web part属性设置里面看不到你的自定义属性
然后，增加两个字符类型的属性：ReportServerURL，ReportPath。具体的attribute设置请参考
如何给Web part增加自定义属性Creating a Web Part with Custom Properties(http://msdn2.microsoft.com/en-us/library/ms948927.aspx)
这是最后的代码
using System;
using System.Collections.Generic;
using System.Text;
using System.ComponentModel;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;
using Microsoft.SharePoint.WebPartPages;
namespace BARreportWebPart
{
[DefaultProperty("Text"),
ToolboxData("<{0}:BARreportWebPart runat=server></{0}:BARreportWebPart>"),
XmlRoot(Namespace = "BARreportWebPart")]
public class BARreportWebPart : Microsoft.SharePoint.WebPartPages.WebPart//System.Web.UI.WebControls.WebParts.WebPart
{
string _reportServerURL="";// = "http://ctc-bar:81/ReportServer/ReportService.asmx";
string _reportPath = "";// = "/BARreports/EBCdetailList";
public BARreportWebPart()
{
}
protected override void Render(System.Web.UI.HtmlTextWriter writer)
{
if (string.Empty == ReportServerURL.Trim() || null == ReportServerURL
|| string.Empty == ReportPath.Trim() || null == ReportPath)
{
writer.Write("please set the ReportServerURL, ReportPath");
return;
}
string reportServerURL = ReportServerURL;
string reportPath = ReportPath;
ReportAdapter rsAdapter = new ReportAdapter(reportServerURL, reportPath);
ReportingServer.ReportParameter[] parameters = rsAdapter.GetParameters();
ReportingServer.ParameterValue[] parameterValues = null;
if (parameters.Length > 0)
{
parameterValues = new ReportingServer.ParameterValue[parameters.Length];
for (int i = 0; i < parameters.Length; i++)
{
parameterValues[i] = new ReportingServer.ParameterValue();
parameterValues[i].Name = parameters[i].Name;
parameterValues[i].Label = parameters[i].Prompt;
}
parameterValues[0].Value = "2007-1-1";
parameterValues[1].Value = "2007-3-1";
}
System.Text.Encoding enc = System.Text.Encoding.UTF8;
byte[] result = rsAdapter.RenderReport(parameterValues);
string htmlResult = enc.GetString(result);
//htmlResult = htmlResult.Replace(reportServerURL.Replace("/ReportService.asmx", "?"), "http://" & Request("SERVER_NAME") & Request("SCRIPT_NAME") & "?Report=");
//writer.Write(htmlResult);
}
// Creates a custom property :
// This property will be displayed as a text box in the
// property pane.
// Create a custom category in the property sheet.
//[Category("Custom Properties")]
// Assign the default value.
[DefaultValue("http://ctc-bar:81/ReportServer/ReportService.asmx")]
// Property is available in both Personalization
// and Customization mode.
[WebPartStorage(Storage.Personal)]
// The caption that appears in the property sheet.
[FriendlyNameAttribute("ReportServerURL")]
// The tool tip that appears when pausing the mouse pointer over
// the friendly name in the property pane.
[Description("Report Server URL: (like http://ctc-bar:81/ReportServer/ReportService.asmx )")]
// Display the property in the property pane.
[Browsable(true)]
[XmlElement(ElementName = "ReportServerURL")]
// The accessor for this property.
public string ReportServerURL
{
get
{
return _reportServerURL;
}
set
{
_reportServerURL = value;
}
}
// Assign the default value.
[DefaultValue("")]
// Property is available in both Personalization
// and Customization mode.
[WebPartStorage(Storage.Personal)]
// The caption that appears in the property sheet.
[FriendlyNameAttribute("ReportPath")]
// The tool tip that appears when pausing the mouse pointer over
// the friendly name in the property pane.
[Description("Report Path")]
// Display the property in the property pane.
[Browsable(true)]
[XmlElement(ElementName = "ReportPath")]
// The accessor for this property.
public string ReportPath
{
get{
return _reportPath;
}
set {
_reportPath = value;
}
}
}
}
这一节，我们给Web part增加报表查询参数。为了演示，这里只增加两个查询参数：开始时间和结束时间。这两个参数都是日期型的数据，为了方便用户操作，我使用了client端的日历脚本来实现日期输入。
与上一节相比，我们需要增加两部分工作
第一步：构造日期输入控件
我的页面布局大概是这样的：
| ||||
| 报表显示区 | ||||
| 状态来栏 |
为了加入参数，我在构造函数里创建了一组table，将控件分别加入到各单元格中。
下面为代码
protected string _reportServerURL = "" ; // = "http://ctc-bar:81/ReportServer/ReportService.asmx";
protected string _reportPath = "" ; // = "/BARreports/EBCdetailList";
// UI controls
protected HtmlInputText _txtStartDate = BuildTextBox( "txtStartDate" ); protected HtmlButton _btnStartDate = new HtmlButton (); protected HtmlInputText _txtEndDate = BuildTextBox( "txtEndDate" ); protected HtmlButton _btnEndDate = new HtmlButton (); protected Button _cmdOK = new Button (); protected HtmlGenericControl ReportView = new HtmlGenericControl (); protected Label _lblStatus = new Label (); protected Label _lblError = new Label (); protected HtmlInputHidden ctrlPrefix = new HtmlInputHidden (); protected string canlander_JSP = "" ;
#region
{
canlander_JSP = BARreportWebPart.Properties.
initailize the web controls public BARreportWebPartClass() Resources .Canlander_JSP; //<input type=hidden id="ctrlPrefix" name="ctrlPrefix" value='<asp:Literal Runat=server ID=ctrlPrefix></asp:Literal>'>
ctrlPrefix.ID =
ctrlPrefix.Name =
ctrlPrefix.Value =
WebPart_Load();
}
"ctrlPrefix" ; "ctrlPrefix" ; "<asp:Literal Runat=server ID=ctrlPrefix></asp:Literal>" ; /// <summary>
/// load the web part UI controls
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
{
protected void WebPart_Load() // build up the table that is our UI
Label ParameterName; TableRow tr; TableCell cell; //build the main table
t2.ID =
t2.CssClass =
t2.Width =
t2.Height =
Table t2 = new Table (); "TblMain" ; "mainTable" ; new Unit (100, UnitType .Percentage); new Unit (100, UnitType .Percentage); //add the top table
tr =
tr.ID =
t2.Rows.Add(tr);
cell =
cell.ID =
t2.Rows[0].Cells.Add(cell);
t2.Rows[0].Cells[0].VerticalAlign =
t2.Rows[0].Cells[0].HorizontalAlign =
new TableRow (); "TblMainRow1" ; new TableCell (); "TblMainRow1Cell1" ; VerticalAlign .Middle; HorizontalAlign .Left; //add the content table
t2.Rows.Add(
t2.Rows[1].Cells.Add(
t2.Rows[1].Cells[0].VerticalAlign =
t2.Rows[1].Cells[0].HorizontalAlign =
new TableRow ()); new TableCell ()); VerticalAlign .Middle; HorizontalAlign .Left; //add the bottom table
t2.Rows.Add(
t2.Rows[2].Cells.Add(
t2.Rows[2].Cells[0].VerticalAlign =
t2.Rows[2].Cells[0].HorizontalAlign =
Controls.Add(t2);
new TableRow ()); new TableCell ()); VerticalAlign .Middle; HorizontalAlign .Left; /// build top table
tblTop.ID =
tblTop.Width =
tblTop.Height =
tr =
tr.ID =
tblTop.Rows.Add(tr);
cell =
cell.ID =
tblTop.Rows[0].Cells.Add(
tblTop.Rows[0].Cells[0].VerticalAlign =
tblTop.Rows[0].Cells[0].HorizontalAlign =
ParameterName =
ParameterName.Text =
_txtStartDate.Attributes.Add(
_btnStartDate.ID =
_btnStartDate.InnerText =
Table tblTop = new Table (); "TblTop" ; new Unit (100, UnitType .Percentage); new Unit (100, UnitType .Percentage); new TableRow (); "TblTopRow1" ; new TableCell (); "TblTopRow1Cell1" ; new TableCell ()); VerticalAlign .Middle; HorizontalAlign .Center; new Label (); "From" ; "ondblclick" , "show_cele_date(this,'','',this)" ); "btnStartDate" ; "Calendar" ; //_btnStartDate.Attributes.Add("onclick", "show_cele_date(this,'','',document.getElementById('" + ClientID_txtStartDate + "'))");
tblTop.Rows[0].Cells[0].Controls.Add(ParameterName);
tblTop.Rows[0].Cells[0].Controls.Add(_txtStartDate);
tblTop.Rows[0].Cells[0].Controls.Add(_btnStartDate);
tblTop.Rows[0].Cells.Add(
tblTop.Rows[0].Cells[1].VerticalAlign =
tblTop.Rows[0].Cells[1].HorizontalAlign =
ParameterName =
ParameterName.Text =
_txtEndDate.Attributes.Add(
_btnEndDate.InnerText =
_btnEndDate.Attributes.Add(
tblTop.Rows[0].Cells[1].Controls.Add(ParameterName);
tblTop.Rows[0].Cells[1].Controls.Add(_txtEndDate);
tblTop.Rows[0].Cells[1].Controls.Add(_btnEndDate);
tblTop.Rows.Add(
tblTop.Rows[1].Cells.Add(
tblTop.Rows[1].Cells[0].VerticalAlign =
tblTop.Rows[1].Cells[0].HorizontalAlign =
_cmdOK.Text =
tblTop.Rows[1].Cells[0].Controls.Add(_cmdOK);
_cmdOK.Click +=
new TableCell ()); VerticalAlign .Middle; HorizontalAlign .Center; new Label (); "To" ; "ondblclick" , "show_cele_date(this,'','',this)" ); "Calendar" ; "onclick" , "show_cele_date(this,'','',GetObject('" + _txtEndDate.ClientID + "'))" ); new TableRow ()); new TableCell ()); VerticalAlign .Middle; HorizontalAlign .Center; "View Report" ; new EventHandler (OnOKClick); //add the top table into table main
t2.Rows[0].Cells[0].Controls.Add(tblTop);
//build bottom table
tblBottom.Width =
tblBottom.Height =
tblBottom.Rows.Add(
tblBottom.Rows[0].Cells.Add(
tblBottom.Rows[0].Cells[0].VerticalAlign =
tblBottom.Rows[0].Cells[0].HorizontalAlign =
_lblStatus.Text =
tblBottom.Rows[0].Cells[0].Controls.Add(_lblStatus);
Table tblBottom = new Table (); new Unit (100, UnitType .Percentage); new Unit (100, UnitType .Percentage); new TableRow ()); new TableCell ()); VerticalAlign .Middle; HorizontalAlign .Center; "" ; //add the bottom table into main table
t2.Rows[2].Cells[0].Controls.Add(tblBottom);
//build the content table
Table tblContent = new Table (); //Step 1:
tblContent.Rows.Add(
tblContent.Rows[0].Cells.Add(
ReportView.InnerText =
tblContent.Rows[0].Cells[0].Controls.Add(ReportView);
new TableRow ()); new TableCell ()); "report view region" ; //add the content table into main table
t2.Rows[1].Cells[0].Controls.Add(tblContent);
Controls.Add(ctrlPrefix);
}
/// <summary>
/// Build a new textbox that has standard props.
/// </summary>
/// <param name="id"> The id for the textbox. </param>
/// <returns> A TextBox </returns>
{
t.ID = id;
}
private static HtmlInputText BuildTextBox( string id) HtmlInputText t = new HtmlInputText (); return t;#endregion
第二步：注册javascript脚本来实现日历输入。
为了更加友好的用户操作，需要利用javascript来实现日期输入。javascript脚本很长，所以我把脚本保存在资源文件中，这样，对脚本的修改大大方便了。具体原理参见我的另一篇文章http://blog.csdn.net/yanwei100/archive/2007/03/15/1530528.aspx
由于脚本是运行在一个自定义web控件中，所以需要解决ClientID的问题，具体方法参考我的另外一篇文章。
下面是代码
#region
{
RegisterJSP();
regist Java script protected override void OnLoad( EventArgs e) // set ctrlPrefix's value
string name = this .ClientID; string [] elm = ctrlPrefix.ClientID.Split( '_' ); //get an array of values
ctrlPrefix.Value = ctrlPrefix.ClientID.Replace(elm[elm.Length - 1],
"" ); //get the last element and replace from the id
}
{
base .OnLoad(e); void RegisterJSP() //read jsp file from resource
String csname1 = "CalendarScript" ; Type cstype = this .GetType(); // Get a ClientScriptManager reference from the Page class.
ClientScriptManager cs = Page.ClientScript; // Check to see if the client script is already registered.
{
cs.RegisterClientScriptBlock(cstype, csname1, canlander_JSP,
}
if (!cs.IsClientScriptBlockRegistered(cstype, csname1)) false ); //add the getprefix function
strbld.Append(
strbld.Append(
strbld.Append(
strbld.Append(
strbld.Append(
strbld.Append(
strbld.Append(
strbld.Append(
strbld.Append(
strbld.Append(
strbld.Append(
strbld.Append(
strbld.Append(
strbld.Append(
strbld.Append(
{
cs.RegisterClientScriptBlock(cstype, csname2, strbld.ToString(),
}
}
{
{
{
ClientID_txtStartDate = ctrl.ClientID;
}
{
_btnStartDate = (
}
}
{
_btnStartDate.Attributes.Add(
}
}
StringBuilder strbld = new StringBuilder (); "<script language ='javascript' type='text/javascript' >" ); "function getCrtlPrefix() {" ); "var prefix; " ); "var objCrtlPrefix = document.getElementById('" + ctrlPrefix.ClientID + "');" ); "if (objCrtlPrefix)" ); "prefix = objCrtlPrefix.value;" ); "return prefix;}" ); "function GetObject(ctrlName) {" ); "var prefix = getCrtlPrefix();" ); "var objCrtl = document.getElementById(prefix + ctrlName);" ); "if (objCrtl)" ); "return objCrtl;" ); " else " ); " alert('not found!');} " ); "</script>" ); string csname2 = "getCrtlPrefixScript" ; if (!cs.IsClientScriptBlockRegistered(cstype, csname2)) false ); protected override void OnPreRender( EventArgs e) string ClientID_txtStartDate= "" ; HtmlButton _btnStartDate = null ; foreach ( Control ctrl in Controls[0].Controls[0].Controls[0].Controls[0].Controls[0].Controls[0].Controls) if ( "txtStartDate" == ctrl.ID) if ( "btnStartDate" == ctrl.ID) HtmlButton )ctrl; if ( null != _btnStartDate) "onclick" , "show_cele_date(this,'','',document.getElementById('" + ClientID_txtStartDate + "'))" ); base .OnPreRender(e);#endregion
最后，将用户输入的日期加入到web service的查询参数中：
{
||
{
writer.Write(
}
ReportingServer.
ReportingServer.
{
parameterValues =
{
parameterValues[i] =
parameterValues[i].Name = parameters[i].Name;
parameterValues[i].Label = parameters[i].Prompt;
}
protected override void Render(System.Web.UI. HtmlTextWriter writer) base .Render(writer); if ( string .Empty == ReportServerURL.Trim() || null == ReportServerURL string .Empty == ReportPath.Trim() || null == ReportPath) "please set the ReportServerURL, ReportPath" ); return ; string reportServerURL = ReportServerURL; string reportPath = ReportPath; ReportAdapter rsAdapter = new ReportAdapter (reportServerURL, reportPath); ReportParameter [] parameters = rsAdapter.GetParameters(); ParameterValue [] parameterValues = null ; if (parameters.Length > 0) new ReportingServer. ParameterValue [parameters.Length]; for ( int i = 0; i < parameters.Length; i++) new ReportingServer. ParameterValue (); DateTime startDate = new DateTime ( DateTime .Now.Year, DateTime .Now.Month,1); DateTime endDate = startDate.AddMonths(1); try
{
startDate =
}
DateTime .Parse(_txtStartDate.Value); catch {} try
{
endDate =
}
parameterValues[0].Value = startDate.ToString(
DateTime .Parse(_txtEndDate.Value); catch {} "yyyy-MM-dd" ); //"2007-1-1";parameterValues[1].Value = endDate.ToString(
"yyyy-MM-dd" ) ; //"2007-3-31";parameterValues[2].Value =
parameterValues[3].Value =
parameterValues[4].Value =
}
System.Text.
"0,1,2,3,4,5,6,7,8" ; "Government,Enterprise,Partner,International" ; "Requested,Confirmed,Cancelled,Closed" ; Encoding enc = System.Text. Encoding .UTF8; byte [] result = rsAdapter.RenderReport(parameterValues); string htmlResult = enc.GetString(result); //htmlResult = htmlResult.Replace(reportServerURL.Replace("/ReportService.asmx", "?"), "http://" & Request("SERVER_NAME") & Request("SCRIPT_NAME") & "?Report=");
writer.Write(htmlResult);
}
214
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
