Hi,
i need to get the first and last nonempty VALUEs on columns, so i tried
to use NonEmptyCrossJoin function but i found out that this function
return a set and i need a value
(correct me if i'm wrong). So, I need a function to get the first &
last values.

Any suggestions will be appreciated,
Thanx a lot.

Abobakr Nasr

Hi Abobakr,

Can you give an idea of the context in which you want to return the
first and last non empty values - what should the results look like?

Here's a sample query for Adventure Works, which returns the first and
last non empty values of the first measure which is on rows:

Sum(Head(NonEmptyCrossJoin(StrToSet("Axis(0)"),
Head(StrToSet("Axis(1)")), 1)),
StrToSet("Axis(1)").Item(0))
Member [Measures].[LastNonEmpty] as
Sum(Tail(NonEmptyCrossJoin(StrToSet("Axis(0)"),
Head(StrToSet("Axis(1)")), 1)),
StrToSet("Axis(1)").Item(0))

select [Date].[Calendar].[Month].Members on columns,
{[Measures].[Order Quantity],
[Measures].[FirstNonEmpty],
[Measures].[LastNonEmpty]} on rows
from [Adventure Works]
where [Product].[Product Categories].[Product Name].&[462]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***