Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MDX ParallelPeriod in Calculated Member Expand / Collapse
Author
Message
Posted Wednesday, November 18, 2009 8:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 7, 2014 1:28 PM
Points: 23, Visits: 85
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. .



Post #821336
Posted Saturday, December 12, 2009 7:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 6:46 AM
Points: 16, Visits: 214
Try rewriting like this.

(ParallelPeriod([Date Pay And Benefits].[Fiscal Year].[Fiscal Year]
, 1
,[Date Pay And Benefits].[Fiscal Year].CurrentMember)
,[Measures].[Amount])
Post #833325
Posted Saturday, December 12, 2009 1:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 7, 2014 1:28 PM
Points: 23, Visits: 85
Thanks rewriting the MDX code worked in calculated measure.
Post #833399
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse