ParallelPeriod Calculated Member - Don't want to tie to year

I currently have Calculated Member in a cube that shows ParallelPeriod using a hierachy based off the year. So you always get prior year amounts, even while drilling into the hierarchy. Here is this Calculated Member (nothing special). This takes the amount you are on and subtracts the previous years amount.

Code Snippet

[Measures].[Amount] - ([Measures].[Amount], ParallelPeriod([Date].[Year - Quarter - Month].[Year], 1, [Date].[Year - Quarter - Month].CurrentMember))

What I am looking to do is always show the prior period based on where you are in the hierarchy (and do it with one calculated member). So I want to take amount and subtract it by the previous period based on where you are in the hierachy (Q2 2007 - Q1 2007, and not Q2 2007 - Q2 2006). This would be an example.

Hierarchy is Year - Quarter - Month. When looking at Years, you see previous year....when you drill into Quarter, you see the previous Quarter of the same year (so you see Q2, 2007 compared to Q1, 2007 and not Q2, 2006). And so on.

Hope that makes sense. Any help is greatly appreciated.

Would PrevSibling be applicable here.

Steve

|||Try [Measures].[Amount] - ([Measures].[Amount], [Date].[Year - Quarter - Month].PrevMember)
|||

Jeffrey Wang wrote:

Try [Measures].[Amount] - ([Measures].[Amount], [Date].[Year - Quarter - Month].PrevMember)

This seems to work. I knew it was something straightforward. Thank you very much for your help!

Page vs Row locking...

Hello all,

Somewhere on these newsgroups I recall reading that SQL Server 6 and prior
(when they were married with Sybase) used page locking and not row level
locking. Hence you could be locking a lot more records then what you think
when doing an UPDATE or INSERT SQL.

Now I notice that SQL Server 7 and 2000 claim to use row level locking. (As
you can see, I have been out of the SQL arena for some time). So what I'd
like to know if this is all true? Or marketing mumbo-jumbo? Has Microsoft
made changes at the core of their engine to lock rows? I know that other
RDMSs like Interbase have a versioning engine so it was built from the
ground up for concurrence. And I've read that MSs row level locking is a
band-aid on its unchanged core engine, although the author of that message
did not expand further on this.

so I figured I'd ask the experts what the truth is. Any help is much
appreciated.

If indeed it has changed, what sort of test can I run to prove this to my boss?

regards,
-randall sellRandall Sell (randall@.bytewise.nospam.com.au) writes:
> Somewhere on these newsgroups I recall reading that SQL Server 6 and prior
> (when they were married with Sybase) used page locking and not row level
> locking. Hence you could be locking a lot more records then what you think
> when doing an UPDATE or INSERT SQL.
> Now I notice that SQL Server 7 and 2000 claim to use row level locking.
> (As you can see, I have been out of the SQL arena for some time). So
> what I'd like to know if this is all true? Or marketing mumbo-jumbo? Has
> Microsoft made changes at the core of their engine to lock rows? I know
> that other RDMSs like Interbase have a versioning engine so it was built
> from the ground up for concurrence. And I've read that MSs row level
> locking is a band-aid on its unchanged core engine, although the author
> of that message did not expand further on this.

That poster may have been thinking of SQL 6.5, which had a bascially
unchanged engine from 4.x days, but where you could set a table option
to get "insert row locks" which was good for tables with heavy insert
frequency at a hot spot.

But that was very long ago. MS shipped SQL7 in the end of 1998, and SQL7
was almost complete rewrite, and very little of the original Sybase code
survived. There are still page locks in SQL Server, but I have to confess
that I don't know when they are used. Row locks and table locks is what
you usually see. (Table locks when there is no suitable index, or the
query affects the entire table anyway.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thanx for the info Erland
-rs

Erland Sommarskog wrote:

> Randall Sell (randall@.bytewise.nospam.com.au) writes:
>>Somewhere on these newsgroups I recall reading that SQL Server 6 and prior
>>(when they were married with Sybase) used page locking and not row level
>>locking. Hence you could be locking a lot more records then what you think
>>when doing an UPDATE or INSERT SQL.
>>
>>Now I notice that SQL Server 7 and 2000 claim to use row level locking.
>>(As you can see, I have been out of the SQL arena for some time). So
>>what I'd like to know if this is all true? Or marketing mumbo-jumbo? Has
>>Microsoft made changes at the core of their engine to lock rows? I know
>>that other RDMSs like Interbase have a versioning engine so it was built
>>from the ground up for concurrence. And I've read that MSs row level
>>locking is a band-aid on its unchanged core engine, although the author
>>of that message did not expand further on this.
>
> That poster may have been thinking of SQL 6.5, which had a bascially
> unchanged engine from 4.x days, but where you could set a table option
> to get "insert row locks" which was good for tables with heavy insert
> frequency at a hot spot.
> But that was very long ago. MS shipped SQL7 in the end of 1998, and SQL7
> was almost complete rewrite, and very little of the original Sybase code
> survived. There are still page locks in SQL Server, but I have to confess
> that I don't know when they are used. Row locks and table locks is what
> you usually see. (Table locks when there is no suitable index, or the
> query affects the entire table anyway.)
>