sqlserver2005的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定义
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.搞定，运行。



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