Displaying first instance of data

Hi,

I'm having some trouble with a report I'm trying to generate. It is essentially activity status of a bunch of users that are on the system. The raw data displays the date and time, user, what state they are currently in and what state they changed to and duration.

My problem is I only need the first and last instance for each day. Since a user can go through many states in a day, the data is quite long and to have to filter through the data by hand is quite cumbersome.

Is there a way display the data but just show only the first instance for that date and last instance for the date assuming i have a parameter that is a date range?

ThanksSelect top 1 [fieldnames ...] from [Tablename] .... Order by Primary key desc

delievers the first and

Select top 1 [fieldnames ...] from [Tablename] .... Order by [Primary key] asc

delievers the last entry.|||I'm not quite sure where to put this:

Here is more information on the problem.

I have 3 group names. First is Name, second is time (Day MM/DD/YY), third is time (H:MM)

What I would like to do is for each day, calculate the maximum and minimum of the time

However when I do Minimum (Date) I get the minimum of ALL the days, and not just for that particular date.

For example, my data has 8 users, and has data for about 30 days on each user. Each day a user can have multiple activities, ranging from 8am to 6pm. All I want to calculate or see, is the first time and the last time for that particular day

Any ideas? I'm trying to create a formula using the minimum function but to no avail.|||What is the database you are using?
Post table structures, sample data and the result you want

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