When I execute the MDX Query it gives me proper Result.
Later with Same MDX Query I create the Sub Cube and then again execute the same MDX Query on the SubCube it gives me some other result which doesnot contain certain members which are there in SubCube
Output of Query on wholeCube(image1.gif)
Output of Same Query used for creating the SubCube (image2.gif)
Output of Same Query Query on SubCube(image_3.gif) Query without "WITH"
1)
CREATE SUBCUBE KAYYUM_PVA AS SELECT {[Measures].[SPEND_INVOICE_LINE_AMT]} ON 0,{ GENERATE ( { GENERATE ( { CROSSJOIN ( { EXCEPT ( { DRILLDOWNMEMBER ( { HIERARCHIZE ( { [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components], [UN_DIM].[All UN_DIM].[Manufacturing Components and Supplies]} )}, { [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components]} )}, { [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components]} )}, { GENERATE ( { [TIME_DIM].MEMBERS}, { Descendants([TIME_DIM].CurrentMember,[TIME_DIM].Levels(1)), Ancestor([TIME_DIM].CurrentMember,[TIME_DIM].Levels(1))} )} )}, { StrToSet(IIf([UN_DIM].CurrentMember IS [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components].[Fluid and gas distribution] AND [TIME_DIM].CurrentMember IS [TIME_DIM].[All TIME_DIM].[2004] , SetToStr({ CROSSJOIN ( { ( [UN_DIM].CurrentMember)}, { Descendants([TIME_DIM].[All TIME_DIM].[2004],[TIME_DIM].Levels(2),SELF)} )}) , SetToStr({ ( [UN_DIM].CurrentMember, [TIME_DIM].CurrentMember)})))} )}, { StrToSet(IIf([UN_DIM].CurrentMember IS [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components].[Fluid and gas distribution] AND [TIME_DIM].CurrentMember IS [TIME_DIM].[All TIME_DIM].[2004].[Q2 04] , SetToStr({ CROSSJOIN ( { ( [UN_DIM].CurrentMember)}, { Descendants([TIME_DIM].[All TIME_DIM].[2004].[Q2 04],[TIME_DIM].Levels(3),SELF)} )}) , SetToStr({ ( [UN_DIM].CurrentMember, [TIME_DIM].CurrentMember)})))} )}ON 1 FROM KAYYUM_PVA
Query with "WITH"
WITH
SET [DerivedSet0] AS '{ [MEASURES].[SPEND_INVOICE_LINE_AMT]}'
SET [DerivedSet1] AS '{ HIERARCHIZE ( { [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components], [UN_DIM].[All UN_DIM].[Manufacturing Components and Supplies]} )}'
SET [DerivedSet2] AS '{EXCEPT( {DRILLDOWNMEMBER ( { [DerivedSet1]}, { [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components]} )},{ [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components]})}'
SET [DerivedSet5] AS '{ HIERARCHIZE ( { [DerivedSet2]} )}'
SET [DerivedSet6] AS '{ [TIME_DIM].MEMBERS}'
SET [DerivedSet7] AS '{ GENERATE ( { [DerivedSet6]}, { Descendants([TIME_DIM].CurrentMember,[TIME_DIM].Levels(1)), Ancestor([TIME_DIM].CurrentMember,[TIME_DIM].Levels(1))} )}'
SET [DerivedSet11] AS '{ HIERARCHIZE ( { [DerivedSet7]} )}'
SET [DerivedSet12] AS '{ CROSSJOIN ( { [DerivedSet5]}, { [DerivedSet11]} )}'
SET [DerivedSet13] AS '{ GENERATE ( { [DerivedSet12]}, { StrToSet(IIf([UN_DIM].CurrentMember IS [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components].[Fluid and gas distribution] AND [TIME_DIM].CurrentMember IS [TIME_DIM].[All TIME_DIM].[2004] , SetToStr({ CROSSJOIN ( { ( [UN_DIM].CurrentMember)}, { Descendants([TIME_DIM].[All TIME_DIM].[2004],[TIME_DIM].Levels(2),SELF)} )}) , SetToStr({ ( [UN_DIM].CurrentMember, [TIME_DIM].CurrentMember)})))} )}'
SET [DerivedSet14] AS '{ GENERATE ( { [DerivedSet13]}, { StrToSet(IIf([UN_DIM].CurrentMember IS [UN_DIM].[All UN_DIM].[Distribution and Conditioning Systems and Equipment and Components].[Fluid and gas distribution] AND [TIME_DIM].CurrentMember IS [TIME_DIM].[All TIME_DIM].[2004].[Q2 04] , SetToStr({ CROSSJOIN ( { ( [UN_DIM].CurrentMember)}, { Descendants([TIME_DIM].[All TIME_DIM].[2004].[Q2 04],[TIME_DIM].Levels(3),SELF)} )}) , SetToStr({ ( [UN_DIM].CurrentMember, [TIME_DIM].CurrentMember)})))} )}'
SELECT NON EMPTY { [DerivedSet0]} ON COLUMNS , NON EMPTY { [DerivedSet14]} ON ROWS FROM KAYYUM_PVA