No grand total if Time Intelligence Calculation is drag on row

Hi,

I've deployed a simple cube with a time dimension on SQL 2005. I added the time intelligence which generated some calculations like Day over Day difference, Month over Month difference. If I drag it to row on a pivot table, the grand total of the measures cannot be shown.

Is it because the calculated member is not aggregatable? Is there any ways to show the grand total as well as those calculation on the pivot table?

Million Thanks.

KJ

Kelvin,

This is the intended behaviour. The calculations are written in such a way as to purposely place "NA" in cells where the calculated value would not make sense. For example, if you think of a YTD calculation in the context of all years, it doesn't really make sense; likewise for a year over year calculation.

-rob

Next/Prev record

If you have an identity column (i.e. 1-N) and you have 5, what is the most
efficent query to get the next (and prev) row? Gaps could happen so there
may not be a 6, but the next in order may be 8 for example. TIA
William Stacey [MVP]I usually use these:
Prev:
SELECT TOP 1 key FROM table WHERE key < current ORDER BY key DESC
Next:
SELECT TOP 1 key FROM table WHERE key > current ORDER BY key
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:uw3Tx1iTGHA.424@.TK2MSFTNGP12.phx.gbl...
> If you have an identity column (i.e. 1-N) and you have 5, what is the most
> efficent query to get the next (and prev) row? Gaps could happen so there
> may not be a 6, but the next in order may be 8 for example. TIA
> --
> William Stacey [MVP]
>
>|||Thanks Remus.
William Stacey [MVP]
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:euthl7jTGHA.2156@.tk2msftngp13.phx.gbl...
|I usually use these:
| Prev:
| SELECT TOP 1 key FROM table WHERE key < current ORDER BY key DESC
| Next:
| SELECT TOP 1 key FROM table WHERE key > current ORDER BY key|||William
create table William(c1 int NOT NULL primary key)
go
insert into William values (1)
insert into William values (2)
insert into William values (3)
insert into William values (4)
insert into William values (5)
insert into William values (6)
declare @.d as int
set @.d=4
select top 1 *,(select top 1 * from William where c1 >@.d order by c1
asc)as n
from William where c1 <@.d order by c1 desc
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:uw3Tx1iTGHA.424@.TK2MSFTNGP12.phx.gbl...
> If you have an identity column (i.e. 1-N) and you have 5, what is the most
> efficent query to get the next (and prev) row? Gaps could happen so there
> may not be a 6, but the next in order may be 8 for example. TIA
> --
> William Stacey [MVP]
>
>