Pulling KPIs from Analysis Services

   1: using Microsoft.AnalysisServices.AdomdClient;

   1: string myConnectionString;

   2:  

   3: AdomdConnection myKPIConnection;

   4:  

   5: CubeDef myCubeDef;

   6:  

   7: myConnectionString = "Data Source=" + @myOlapServer + ";Catalog=\"" + @myOlapDatabase + "\"";

   8:  

   9: myKPIConnection = new AdomdConnection(myConnectionString);

  10:  

  11: myKPIConnection.Open();

  12:  

  13: myCubeDef = myKPIConnection.Cubes[myCube];

  14:  

   1: AdomdCommand myKPICommand;

   2:  

   3: foreach (Kpi k in myCubeDef.Kpis)

   4: {

   5:    myKPICommand = new AdomdCommand();

   6:    myKPICommand.Connection = myKPIConnection;

   7:  

   8:     //build the MDX query that return the KPI Value

   9:  

  10:    myKPICommand.CommandText = "SELECT { strtomember(@Value), strtomember(@Goal), strtomember(@Status), strtomember(@Trend) } ON COLUMNS FROM [" +myCubeDef.Name + "]";

  11:    myKPICommand.Parameters.Clear();

  12:    myKPICommand.Parameters.Add(new AdomdParameter("Value", "KPIValue([" + k.Name + "])"));

  13:    myKPICommand.Parameters.Add(new AdomdParameter("Goal", "KPIGoal([" + k.Name + "])"));

  14:    myKPICommand.Parameters.Add(new AdomdParameter("Status", "KPIStatus([" + k.Name + "])"));

  15:    myKPICommand.Parameters.Add(new AdomdParameter("Trend", "KPITrend([" + k.Name + "])"));

  16:  

  17:    // Execute query

  18:    CellSet cellset = myKPICommand.ExecuteCellSet();

  19:    // Get values for KPIs

  20:    string kpiName = k.Name;

  21:    try {

  22:       string kpiValue = cellset.Cells[0].FormattedValue;

  23:       string kpiGoal = cellset.Cells[1].FormattedValue;

  24:       //Display the Result

  25:       Response.Write("<td><font face=arial size=3><img src=\"images\\kpi_icon.gif\"></td>");

  26:       Response.Write("<td align=right><font face=arial size=3>" + kpiValue + "</td>");

  27:       Response.Write("<td align=right><font face=arial size=3>" + kpiGoal + "</td>");

  28:       Response.Write("<td><center><img src=" + myGraphicFileInfo.GetKpiImage(k.StatusGraphic, Convert.ToDouble(cellset.Cells[2].Value)) + "></center></td>");

  29:  

  30:       //Show the description of the KPI

  31:       if (k.Description != null && k.Description != "")

  32:           Response.Write("<td><center><img src=images\\info.gif title=\"" + k.Description + "\"></center></td>");

  33:       else

  34:           Response.Write("<td></td>");

  35:  

  36:       Response.Write("</tr>");

  37:    } catch (AdomdErrorResponseException myException) {

  38:  

  39:    }

  40: }

   1: myGraphicFileInfo.GetKpiImage(k.StatusGraphic, Convert.ToDouble(cellset.Cells[2].Value), and this is the source of this methode :

   2:  

   3: using System;

   4: using System.Collections;

   5:  

   6: /// <summary>

   7: /// Summary description for GraphicFileInfo

   8: /// </summary>

   9:  

  10: public class GraphicFileInfo {

  11:  

  12:     public GraphicFileInfo(){

  13:        InitializeGraphicFileInfo();

  14:     }

  15:  

  16:     /// <summary>

  17:     /// private class to manage the KPI Icon

  18:     /// </summary>

  19:     

  20:     private class GraphicFileInformation {

  21:  

  22:         public string FileName;

  23:         public int LastFileNumber;

  24:  

  25:         public GraphicFileInformation(string fileName, int LastFileNumber){

  26:             this.FileName = fileName;

  27:             this.LastFileNumber = LastFileNumber;

  28:         }     

  29:     }

  30:  

  31:     private Hashtable graphicFiles;

  32:  

  33:     /// <summary>

  34:     /// Initiatlize the hashtable that contains the icons

  35:     /// </summary>

  36:     

  37:     private void InitializeGraphicFileInfo(){

  38:         graphicFiles = new Hashtable();

  39:         graphicFiles.Add("Standard Arrow", new GraphicFileInformation("Arrow_Beveled", 4));

  40:         graphicFiles.Add("XP Arrow", new GraphicFileInformation("Arrow_XP", 4));

  41:         graphicFiles.Add("Status Arrow - Ascending", new GraphicFileInformation("Arrow_Status_Asc", 4));

  42:         graphicFiles.Add("Status Arrow - Descending", new GraphicFileInformation("Arrow_Status_Desc", 4));

  43:         graphicFiles.Add("Traffic Light - Single", new GraphicFileInformation("Stoplight_Single", 2));

  44:         graphicFiles.Add("Traffic Light - Multiple", new GraphicFileInformation("Stoplight_Multiple", 2));

  45:         graphicFiles.Add("Road Signs", new GraphicFileInformation("Road", 2));

  46:         graphicFiles.Add("Gauge - Ascending", new GraphicFileInformation("Gauge_Asc", 4));

  47:         graphicFiles.Add("Gauge - Descending", new GraphicFileInformation("Gauge_Desc", 4));

  48:         graphicFiles.Add("Thermometer", new GraphicFileInformation("Therm", 2));

  49:         graphicFiles.Add("Cylinder", new GraphicFileInformation("Cylinder", 2));

  50:         graphicFiles.Add("Smiley Face", new GraphicFileInformation("Smiley", 2));

  51:     }

  52:  

  53:     /// <summary>

  54:     /// Return the icon to display

  55:     /// </summary>

  56:     /// <param name="graphicName">Name of the KPI Icon</param>

  57:     /// <param name="value">value of the KPI</param>

  58:     /// <returns>The Icon name to display</returns>

  59:     

  60:     public string GetKpiImage(string graphicName, double value){

  61:     

  62:         GraphicFileInformation graphicFile = (GraphicFileInformation)graphicFiles[graphicName];

  63:     

  64:         int fileNumber = (int)Math.Round(graphicFile.LastFileNumber * (value + 1.0) / 2.0);

  65:     

  66:         if (fileNumber < 0){

  67:             fileNumber = 0;

  68:         } else if (fileNumber > graphicFile.LastFileNumber){

  69:             fileNumber = graphicFile.LastFileNumber;

  70:         }

  71:     

  72:         string fileName = "Images/" + graphicFile.FileName + fileNumber + ".gif";

  73:         return fileName;

  74:     }

  75: }