Display SQL 2005 KPI status graphic using visual studio 2005

Dear All,

Could anyone help send me a sample mdx code on how i could get the KPI status graphics from MS SQL 2005. I create a cube and add a few KPI's into the cube, on the management studio 2005 I am able to view the graphics e.g. smiley but when i use the mdx command I could only display the KPI status -1, 0 or 1 not the graphics.

The MDX functions does not include the KPI_status_graphics.

Thank you in advance.

Mike Siow siowm@.metierview.com

You can retrieve the KPI_STATUS_GRAPHIC and KPI_TREND_GRAPHIC for a KPI using the MDSCHEMA_KPIS Rowset:

http://msdn2.microsoft.com/en-us/library/ms126258.aspx

>>

MDSCHEMA_KPIS Rowset

Describes the key performance indicators (KPIs) within a database.

...

>>

For example, for the Adventure Works Internet Revenue KPI:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

<RequestType>MDSCHEMA_KPIS</RequestType>

<Restrictions>

<RestrictionList>

<CATALOG_NAME>Adventure Works DW</CATALOG_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<KPI_NAME>Internet Revenue</KPI_NAME>

</RestrictionList>

</Restrictions>

<Properties>

<PropertyList>

<Catalog>Adventure Works DW</Catalog>

<Format>Tabular</Format>

</PropertyList>

</Properties>

</Discover>

--

<return xmlns="urn:schemas-microsoft-com:xml-analysis">

<root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<xsd:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified">

<xsd:element name="root">

<xsd:complexType>

<xsd:sequence minOccurs="0" maxOccurs="unbounded">

<xsd:element name="row" type="row" />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:simpleType name="uuid">

<xsd:restriction base="xsd:string">

<xsd:pattern value="[0-9a-zA-Z]{8}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}" />

</xsd:restriction>

</xsd:simpleType>

<xsd:complexType name="xmlDocument">

<xsd:sequence>

<xsd:any />

</xsd:sequence>

</xsd:complexType>

<xsd:complexType name="row">

<xsd:sequence>

<xsd:element sql:field="CATALOG_NAME" name="CATALOG_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="SCHEMA_NAME" name="SCHEMA_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="CUBE_NAME" name="CUBE_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="MEASUREGROUP_NAME" name="MEASUREGROUP_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_NAME" name="KPI_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_CAPTION" name="KPI_CAPTION" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_DESCRIPTION" name="KPI_DESCRIPTION" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_DISPLAY_FOLDER" name="KPI_DISPLAY_FOLDER" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_VALUE" name="KPI_VALUE" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_GOAL" name="KPI_GOAL" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_STATUS" name="KPI_STATUS" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_TREND" name="KPI_TREND" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_STATUS_GRAPHIC" name="KPI_STATUS_GRAPHIC" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_TREND_GRAPHIC" name="KPI_TREND_GRAPHIC" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_WEIGHT" name="KPI_WEIGHT" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_CURRENT_TIME_MEMBER" name="KPI_CURRENT_TIME_MEMBER" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_PARENT_KPI_NAME" name="KPI_PARENT_KPI_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="ANNOTATIONS" name="ANNOTATIONS" type="xsd:string" minOccurs="0" />

</xsd:sequence>

</xsd:complexType>

</xsd:schema>

<row>

<CATALOG_NAME>Adventure Works DW</CATALOG_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<MEASUREGROUP_NAME>Internet Sales</MEASUREGROUP_NAME>

<KPI_NAME>Internet Revenue</KPI_NAME>

<KPI_CAPTION>Internet Revenue</KPI_CAPTION>

<KPI_DESCRIPTION>Revenue realized through direct sales via the internet.</KPI_DESCRIPTION>

<KPI_DISPLAY_FOLDER>Financial Perspective\Grow Revenue</KPI_DISPLAY_FOLDER>

<KPI_VALUE>[Measures].[Internet Sales Amount]</KPI_VALUE>

<KPI_GOAL>[Measures].[Internet Revenue Goal]</KPI_GOAL>

<KPI_STATUS>[Measures].[Internet Revenue Status]</KPI_STATUS>

<KPI_TREND>[Measures].[Internet Revenue Trend]</KPI_TREND>

<KPI_STATUS_GRAPHIC>Cylinder</KPI_STATUS_GRAPHIC>

<KPI_TREND_GRAPHIC>Standard Arrow</KPI_TREND_GRAPHIC>

<KPI_WEIGHT />

<KPI_PARENT_KPI_NAME />

<ANNOTATIONS />

</row>

</root>

</return>

|||

Hi Deepak,

Thank you.

Mike Siow