sqlserver2005的KPI展示控件开发详解

发布时间：2020-12-12 15:47:28 所属栏目：MsSql教程来源：网络整理

导读：? 注意：这篇文章其实在几天前我已经写过，但是那是我的第一片文章，写的相当粗糙，很多东西都没有写详细，只是草草的提供了源码的下载，看到我的那篇数据挖掘控件研究点击量有点大，给了我不少安慰，因此决定重写KPI展示这片文章。没有深入了解过的朋友

注意：这篇文章其实在几天前我已经写过，但是那是我的第一片文章，

写的相当粗糙，很多东西都没有写详细，只是草草的提供了源码的下载

，看到我的那篇<数据挖掘控件研究>点击量有点大，给了我不少安慰，

因此决定重写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定义

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,?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定义

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,?"未知错误!",?MessageBoxDefaultButton.Button1);

????????????}

????????????finally

????????????{

????????????????myKPIConnection.Close();

????????????}

step4.绑定到DataGridView。
?this.kpiDataGridView1.DataSource = this._kpiDataSet.KPIModel;

step5.搞定，运行。

完整代码下载地址：
KPIDisplayer.rar

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!