November 18, 2009 at 8:35 pm
Hi SQL Server Central Forumers,
I'm trying to calculate the Prior Year Amount in a calculated member with the following expression. The cube processes without errors but, the result in PYAmount column when i browse the cube is null value for all rows.
-- PYAmount Calculated member
(
[Measures].[PYAmount],
ParallelPeriod([Date Pay And Benefits].[Fiscal Year].[Fiscal Year]
, 1
, [Date Pay And Benefits].[Fiscal Year].CurrentMember)
)'
But the same expression works fine when I used it to write query on the cube to get previous year sales count. Following is the query for that. I don't understand why the same WITH MEMBER expression used in Calculated Member is not working.
With Member [PYAmount] As
'(
[Measures].[Amount]
, ParallelPeriod([Date Pay And Benefits].[Fiscal Year].[Fiscal Year]
, 1
, [Date Pay And Benefits].[Fiscal Year].CurrentMember)
)'
Select
{[Date Pay And Benefits].[Fiscal Year].[Fiscal Year].[2008]
, [Date Pay And Benefits].[Fiscal Year].[Fiscal Year].[2004].Children} on columns
, {[Measures].[Amount], [Measures].[PY Amount]} on rows
From MyCube
Please correct me if i'm doing anything wrong and please some workable solutions. .
December 12, 2009 at 7:14 am
Try rewriting like this.
(ParallelPeriod([Date Pay And Benefits].[Fiscal Year].[Fiscal Year]
, 1
,[Date Pay And Benefits].[Fiscal Year].CurrentMember)
,[Measures].[Amount])
December 12, 2009 at 1:33 pm
Thanks rewriting the MDX code worked in calculated measure.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply