new requirement has been added the show top ten results only.

here is the code in analysis services how to do i add top ten restriction to it.

sum( {

NonEmptyCrossJoin(

{[Start Date].firstchild.firstchild.firstchild:[Start Date].[2000].[Quarter...

Thanks Keith

but ended up being slower.

this is going to be a great presentation to our client. if i use around 2 dimensions though, it kicks ass over to old...

thanks steve,

i can't seem to work out how to use that in a calculated member.

in the end we used this in a calculated member

sum( {

NonEmptyCrossJoin(

{[Start Date].[1900].[January]:[Start Date].[2000].[December]},

{[End Date].[2000].[January]:[End Date].[8000].[December]}

) }...

we seem to have data integrity problems with some dates of the data being year 8000 and one in 1900 thats why i had to do that :P.

I think nonemptycrossjoin...

For those interested

Keith has provided me the following solution

with

member [Measures].TotalAcrossDateRange as '( sum( { { [Start Date].[1900].[January]:[Start Date].[2001].[January] } * {

[End Date].[2000].[January]:[End Date].[8000].[December] } } , [Measures].[Total] ))'

select

...

Wow thanks guys!

i'll give them a go and get back to you to see how it goes.

yeah i'll check out the resource kit as well steve.

Thanks

yeah thanks keith,

i've used this but its not what i want.

i need all data with dates greater than begindate and data with dates less than enddate.

couldn't get WHERE clause working with what I wanted to do. Haven't got a example because I can't work out something that works.

its shows up as null when I view the database from sql server enterprise manager, but when i view the cube in analysis services which used the data from the...

nah no default values, its a MS thing, its a long story. for some reason they just decided to make null = 30/12/1899

its the same thing with access databases.

maybe BRIO supports what you are looking for.

http://www.brio.com