Hello,
I have a set of members, generated with a SET clause at the top of my MDX query.
Once I have this set, what function can I use on an axis to determine the parents (i.e the level above) of these members? Those parents will be a set too as the original set will have many different parents.
Or am I going about this the wrong way?
Thanks in advance
Mgale1
Try something like:
SELECT Generate({...} as Set1, ANCESTORS(Set1.CurrentMember, 1)) FROM MyCube
|||Matt,
Thanks for the reply - unfortunately it gives me an error of "The CURRENT function cannot be invoked in this context because the 'MissedJobs' set is not in the scope."
My full query is :
WITH
MEMBER [Measures].[Incidence TY] AS SUM({[Media].[Media].CURRENTMEMBER},([Timeslice].[Timeslice].&[1], [Measures].[Incidence])),FORMAT_STRING = "#,##0.00",NON_EMPTY_BEHAVIOR=[Measures].[Incidence]
MEMBER [Measures].[MyPublicationValue] AS SUM({[Media].[Media].&[7004]},[Measures].[Incidence TY]) ,NON_EMPTY_BEHAVIOR=[Measures].[Incidence]
SET [MissedJobs] AS Filter(Existing([Job].[Job].CHILDREN),NOT [Measures].[MyPublicationValue])
--Produce Report
SELECT CROSSJOIN
(VISUALTOTALS({[Media].[Media].[All], [Media].[Media].&[7002],[Media].[Media].&[7004]})
,{[Measures].[Incidence TY]
}) ON COLUMNS,
NON EMPTY ANCESTORS([MissedJobs].CurrentMember,1) ON ROWS
FROM jdx_cube
WHERE [Date].[Full Date].&[2007-07-01T00:00:00]:[Date].[Full Date].&[2007-07-30T00:00:00]
The critical dimension here is the Job dimension. It has 2 attributes where the "Advertiser" atrib is the parent of the "Job" atrb. In my Set statement in the WITH section I work out which Jobs I dont have and then I want the query to return the Advertisers that 'parent' the jobs I dont have.
Can you offer anymore help please?
Thanks
Mgale1
Try:
...
NON EMPTY GENERATE([MissedJobs], ANCESTORS([MissedJobs].CurrentMember, 1)) ON ROWS
...
|||Hello Matt,
Thanks for the reply and I am sorry for not getting back to this thread earlier (been ill).
Unfortunately this is not quite what I want and I should have been clearer in my initial question.I want to work out the missed jobs set but then display on the axis the name of the 'parent' advertiser but the value in the cellset should be the value of the missed jobs and NOT the value for the parent (which is what this GENERATE solution gives me).
What I mean is, if Advertiser1 has two jobs (Job11 and Job12) with values of 11 and 12 respectively and my Missed Jobs set calculates that only Job 11 applies, then I want to see "Advertiser1, 11 "on the axis.
The GENERATE approach displays "Advertiser1, 23" which is not what I am after.
Do you have any other ideas please?
With continuing thanks
Mgale1
