Sql Server 2005 KPI展示控件开发详解
关于sqlserver2005中的KPI特性，相信接触过sqlserver2005的BI的平台
的朋友都有所耳闻。并且跟数据挖掘查看器一样，微软只在
sqlserver2005客户端里面提供了展示界面，并没有把展示的编程接口提
供给我们。所以这些工作只有我们coder自己来做了。
sqlserver2005里的KPI，创建的时候需要设置KPI名称，值表达式，目标
表达式，状态指示器，状态表达式，走向指示器，走向表达式等。
后来通过google得知，原来sqlserver2005自带的那个KPI的查看器，实
现原理是这样的。微软先做了n张图片，图片的路径在（注意：我的
sqlserver2005是安装在D盘的）
D:\Program Files\Microsoft SQL Server\90
\Tools\Binn\VSShell\Common7
\IDE\DataWarehouseDesigner\KPIsBrowserPage\Images
然后，通过状态表达式和走向表达式返回不同的值来决定显示哪张图片
。
原理还是非常简单，还好它没有采用GDI把这些图片画出来，呵呵，不然
小弟只有罢手了。接下来，我们开始coding吧。
step1.定义一个DataGridViewStatusCell（从DataGridViewImageCell继
承）和DataGridViewStatusColumn（从DataGridViewImageColumn继承）
，用于显示状态指示器。
Code
using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Drawing;
using System.ComponentModel;
using System.Reflection;
namespace KPIDisplayer
{
internal class DataGridViewStatusCell : DataGridViewImageCell
{
public DataGridViewStatusCell()
{
}
protected override object GetFormattedValue(object value, int rowIndex, ref DataGridViewCellStyle cellStyle, TypeConverter valueTypeConverter, TypeConverter formattedValueTypeConverter, DataGridViewDataErrorContexts context)
{
string statusgraph = value.ToString().Split(',')[0];
double status = Double.Parse(value.ToString().Split(',')[1]);
if (statusgraph == StatusGraph.Shapes)
{
if (status == -1)
{
return Properties.Resources.Stoplight_Single0;
}
else if (status == 0)
{
return Properties.Resources.Stoplight_Single1;
}
else if (status == 1)
{
return Properties.Resources.Stoplight_Single2;
}
else
{
return Properties.Resources.empty;
}
}
else if (statusgraph == StatusGraph.TrafficLight)
{
if (status == -1)
{
return Properties.Resources.Stoplight_Multiple0;
}
else if (status == 0)
{
return Properties.Resources.Stoplight_Multiple1;
}
else if (status == 1)
{
return Properties.Resources.Stoplight_Multiple2;
}
else
{
return Properties.Resources.empty;
}
}
else if (statusgraph == StatusGraph.RoadSigns)
{
if (status == -1)
{
return Properties.Resources.Road0;
}
else if (status == 0)
{
return Properties.Resources.Road1;
}
else if (status == 1)
{
return Properties.Resources.Road2;
}
else
{
return Properties.Resources.empty;
}
}
else if (statusgraph == StatusGraph.GaugeAscending)
{
if (status == -1)
{
return Properties.Resources.Gauge_Asc0;
}
else if (status == -0.5)
{
return Properties.Resources.Gauge_Asc1;
}
else if (status == 0)
{
return Properties.Resources.Gauge_Asc2;
}
else if (status ==0.5)
{
return Properties.Resources.Gauge_Asc3;
}
else if (status == 1)
{
return Properties.Resources.Gauge_Asc4;
}
else
{
return Properties.Resources.empty;
}
}
else if (statusgraph == StatusGraph.GaugeDescending)
{
if (status == -1)
{
return Properties.Resources.Gauge_Desc0;
}
else if (status == -0.5)
{
return Properties.Resources.Gauge_Desc1;
}
else if (status == 0)
{
return Properties.Resources.Gauge_Desc2;
}
else if (status == 0.5)
{
return Properties.Resources.Gauge_Desc3;
}
else if (status == 1)
{
return Properties.Resources.Gauge_Desc4;
}
else
{
return Properties.Resources.empty;
}
}
else if (statusgraph == StatusGraph.Thermometer)
{
if (status == -1)
{
return Properties.Resources.Therm0;
}
else if (status == 0)
{
return Properties.Resources.Therm1;
}
else if (status == 1)
{
return Properties.Resources.Therm2;
}
else
{
return Properties.Resources.empty;
}
}
else if (statusgraph == StatusGraph.Cylinder)
{
if (status == -1)
{
return Properties.Resources.Cylinder0;
}
else if (status == 0)
{
return Properties.Resources.Cylinder1;
}
else if (status == 1)
{
return Properties.Resources.Cylinder2;
}
else
{
return Properties.Resources.empty;
}
}
else if (statusgraph == StatusGraph.SmileyFace)
{
if (status == -1)
{
return Properties.Resources.Smiley0;
}
else if (status == 0)
{
return Properties.Resources.Smiley1;
}
else if (status == 1)
{
return Properties.Resources.Smiley2;
}
else
{
return Properties.Resources.empty;
}
}
else if (statusgraph == StatusGraph.VarianceArrow)
{
if (status == -1)
{
return Properties.Resources.Arrow_Beveled0;
}
else if (status == -0.5)
{
return Properties.Resources.Arrow_Beveled1;
}
else if (status == 0)
{
return Properties.Resources.Arrow_Beveled2;
}
else if (status == 0.5)
{
return Properties.Resources.Arrow_Beveled3;
}
else if (status == 1)
{
return Properties.Resources.Arrow_Beveled4;
}
else
{
return Properties.Resources.empty;
}
}
else
{
return Properties.Resources.empty;
}
}
}
}
DataGridViewStatusColumn定义
using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
namespace KPIDisplayer
{
internal class DataGridViewStatusColumn : DataGridViewImageColumn
{
public DataGridViewStatusColumn()
{
CellTemplate = new DataGridViewStatusCell();
}
}
}
step2.定义一个DataGridViewTrendColumn（从DataGridViewImageCell
继承）和DataGridViewStatusColumn（从DataGridViewImageColumn继承
），用于显示走向指示器。
DataGridViewTrendCell定义
using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Drawing;
using System.ComponentModel;
using System.Reflection;
namespace KPIDisplayer
{
internal class DataGridViewTrendCell : DataGridViewImageCell
{
public DataGridViewTrendCell()
{
}
protected override object GetFormattedValue(object value, int rowIndex, ref DataGridViewCellStyle cellStyle, TypeConverter valueTypeConverter, TypeConverter formattedValueTypeConverter, DataGridViewDataErrorContexts context)
{
string trendgraph = value.ToString().Split(',')[0];
double trend = Double.Parse(value.ToString().Split(',')[1]);
if (trendgraph== TrendGraph.StandardArrow)
{
if (trend == -1)
{
return Properties.Resources.Arrow_XP0;
}
else if (trend == -0.5)
{
return Properties.Resources.Arrow_XP1;
}
else if (trend == 0)
{
return Properties.Resources.Arrow_XP2;
}
else if (trend == 0.5)
{
return Properties.Resources.Arrow_XP3;
}
else if (trend == 1)
{
return Properties.Resources.Arrow_XP4;
}
else
{
return Properties.Resources.empty;
}
}
else if (trendgraph == TrendGraph.StatusArrowAscending)
{
if (trend == -1)
{
return Properties.Resources.Arrow_Status_Asc0;
}
else if (trend == -0.5)
{
return Properties.Resources.Arrow_Status_Asc1;
}
else if (trend == 0)
{
return Properties.Resources.Arrow_Status_Asc2;
}
else if (trend == 0.5)
{
return Properties.Resources.Arrow_Status_Asc3;
}
else if (trend == 1)
{
return Properties.Resources.Arrow_Status_Asc4;
}
else
{
return Properties.Resources.empty;
}
}
else if (trendgraph == TrendGraph.StatusArrowDescending)
{
if (trend == -1)
{
return Properties.Resources.Arrow_Status_Desc0;
}
else if (trend == -0.5)
{
return Properties.Resources.Arrow_Status_Desc1;
}
else if (trend == 0)
{
return Properties.Resources.Arrow_Status_Desc2;
}
else if (trend == 0.5)
{
return Properties.Resources.Arrow_Status_Desc3;
}
else if (trend == 1)
{
return Properties.Resources.Arrow_Status_Desc4;
}
else
{
return Properties.Resources.empty;
}
}
else if (trendgraph == TrendGraph.SmileyFace)
{
if (trend == -1)
{
return Properties.Resources.Smiley0;
}
else if (trend == 0)
{
return Properties.Resources.Smiley1;
}
else if (trend == 1)
{
return Properties.Resources.Smiley2;
}
else
{
return Properties.Resources.empty;
}
}
else
{
return Properties.Resources.empty;
}
}
}
}
DataGridViewTrendColumn定义
using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
namespace KPIDisplayer
{
internal class DataGridViewTrendColumn : DataGridViewImageColumn
{
public DataGridViewTrendColumn()
{
CellTemplate = new DataGridViewTrendCell();
}
}
}
step3.取得analysis services 中的定义的KPI的信息和KPI的值。
///<summary>
/// 展示结果
///</summary>
public void Display()
{
AdomdConnection myKPIConnection = new AdomdConnection(_connectString);
_kpiDataSet = new KPIDataSet();
KPIDataSet.KPIModelRow kpiRow;
try
{
myKPIConnection.Open();
CubeDef myCubeDef = myKPIConnection.Cubes[this._cubeName];
AdomdCommand myKPICommand;
foreach (Kpi k in myCubeDef.Kpis)
{
myKPICommand = new AdomdCommand();
myKPICommand.Connection = myKPIConnection;
StringBuilder sb = new StringBuilder();
sb.Append("SELECT { ");
sb.Append("KPIValue(\"" + k.Name + "\"), ");
sb.Append("KPIGoal(\"" + k.Name + "\"), ");
sb.Append("KPIStatus(\"" + k.Name + "\"), ");
sb.Append("KPITrend(\"" + k.Name + "\")");
sb.Append("} ON COLUMNS FROM ["+this._cubeName+"]");
myKPICommand.CommandText = sb.ToString();
CellSet cellset = myKPICommand.ExecuteCellSet();
string kpiName = k.Name;
string kpiValue = cellset.Cells[0].FormattedValue;
string kpiGoal = cellset.Cells[1].FormattedValue;
string kpiStatus = cellset.Cells[2].FormattedValue;
string kpiTrend = cellset.Cells[3].FormattedValue;
string kpiStatusGraph = k.StatusGraphic;
string kpiTrendGraph = k.TrendGraphic;
kpiRow = this._kpiDataSet.KPIModel.NewKPIModelRow();
kpiRow.Name = kpiName;
kpiRow.Value = Convert.ToDecimal(kpiValue);
kpiRow.Goal = Convert.ToDecimal(kpiGoal);
kpiRow.Status = kpiStatusGraph + "," + kpiStatus;
kpiRow.Trend = kpiTrendGraph + "," + kpiTrend;
this._kpiDataSet.KPIModel.AddKPIModelRow(kpiRow);
}
}
catch (AdomdErrorResponseException myException)
{
MessageBox.Show(this, "连接Analysis Service服务器出错!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
}
catch (Exception ex)
{
MessageBox.Show(this, "未知错误!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
}
finally
{
myKPIConnection.Close();
}
step4.绑定到DataGridView。
this.kpiDataGridView1.DataSource = this._kpiDataSet.KPIModel;
step5.搞定，运行。
穿拖鞋的蚂蚁 -