January 3, 2011 at 6:53 am
I have a project scenario in which one measure is to be calculated based on "Master Project Code". The "Master Project Code" , "Project Code" and "Project Name" belong to the same dimension.Below is the query.
"with member [measures].[a]
as ([Measures].[Master Fund Variable Amount]
,[project].[Master Project code].currentmember)
select {[measures].[a]} on columns,
([Project].[Master Project Code].children,
[Project].[Project Code].CHILDREN,
[Project].[Project Name].CHILDREN
) ON ROWS
from
(select [Project].[Master Project Code].&[123456] on columns from [project])
where [fiscal date].[year].&[2010]"
1) When only "Master Project Code" and "Project Code" are selected on rows , the query works fine and brings the data based on "Master Project Code" (as mentioned in the member function)
2) When all the three attributes are selected then the measure is getting calculated based on the "Project Code" and not on the "Master Project Code" .
The "Master Project Code" and the "Project Code" are related based on hierarchy."Master Project Code" is one level above "Project Code" in the project hierarchy.
We need suggestion on how to show the measure based on "Master Project Code" when all three attributes are selected?
January 4, 2011 at 2:36 am
The best way to do this is to create the correct attribute relationship Project_code -> projectname.
In the query use the DIMENSION PROPERTIES [Project].[Project Code].[Project Name] statement. This will be much faster to.
"with member [measures].[a]
as ([Measures].[Master Fund Variable Amount]
,[project].[Master Project code].currentmember)
select {[measures].[a]} on columns,
([Project].[Master Project Code].children,
[Project].[Project Code].CHILDREN
) DIMENSION PROPERTIES [Project].[Project Code].[Project Name] , MEMBER_CAPTION
ON ROWS from
(select [Project].[Master Project Code].&[123456] on columns from [project])
where [fiscal date].[year].&[2010]"
If you create the correct hierarchy you can even eliminate the "[Project].[Master Project Code].children" part in you're query.
Hope this helps
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply