-
Mdx, Avg with crossjoin and calculated members
Hi mates
WITH
MEMBER [MEASURES].X AS AVG([MEASURES].[ITEM VALUE])
MEMBER MEASURES.PARENTID AS
[DEPARTMENT].[DEPARTMENTS].CURRENTMEMBER.PARENT.NAME MEMBER
MEASURES.CHILDRENID AS
GENERATE(DESCENDANTS([DEPARTMENT].[DEPARTMENTS].CURRENTMEMBER),
[DEPARTMENT].[DEPARTMENTS].CURRENTMEMBER.NAME, " - ")
MEMBER MEASURES.CHILDRENNAME AS
GENERATE(DESCENDANTS([DEPARTMENT].[DEPARTMENT].CURRENTMEMBER),
[DEPARTMENT].[DEPARTMENT].CURRENTMEMBER.NAME, " - ")
SELECT
CROSSJOIN([TIME].YEAR.&[2006],
[TIME].[DATEHIER].[MONTH].MEMBERS,
DESCENDANTS([DEPARTMENT].[2],
[DEPARTMENT].[DEPARTMENTS].CURRENTMEMBER, SELF_AND_BEFORE)) ON ROWS ,
{(MEASURES.PARENTID), (MEASURES.CHILDRENID),
(MEASURES.CHILDRENNAME), [MEASURES].X} ON COLUMNS
FROM OLAPDEV
WHERE ([ITEM].[78])
this MDX Query returns the following result
Year
Month
Department
Department ParentId
Department ChildrenIds
Department ChildrenNames
ItemValue (Measure Value)
This returns sum of Item value for each month,year and deparmemnt.
But I need Avg of Item Values for each month, year and department
please help me
thanks
anand
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|