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
