How to load dimension with filter itself in MDX Query Generator

Thu, 20 Mar 2014 03:40:51 GMT

Hi all,

I used the MDX Query Generator for Qlikview to fetch the data from cube for my report. the problem is when I filter to just load the [Broadcast Year] with value = 2013-2014. the generated script doesn't include the [Broadcast Year] dimension. I try to insert [Broadcast Year] dimension in Load and CrossJoin section, but it doesn't work . Any suggestion for this case.

Works but doesn't include [Broadcast Year]:

LOAD

"[Measures].[Number Of Units]" as [Number Of Units],

"[Measures].[Gross Actual Cost]" as [Gross Actual Cost],

"[Dim Agency].[Agency Name].[Agency Name].[MEMBER_CAPTION]" as [Agency Name],

"[Dim Buy Type].[Buy Type Name].[Buy Type Name].[MEMBER_CAPTION]" as [Buy Type Name],

"[Dim Client].[Client Name].[Client Name].[MEMBER_CAPTION]" as [Client Name],

"[Dim Client].[Industry Name].[Industry Name].[MEMBER_CAPTION]" as [Industry Name],

"[Dim Date].[Broadcast Year Quarter].[Broadcast Year Quarter].[MEMBER_CAPTION]" as [Broadcast Year Quarter],

"[Dim Date].[Broadcast Year Week].[Broadcast Year Week].[MEMBER_CAPTION]" as [Broadcast Year Week],

SELECT

{

[Measures].[Number Of Units],

[Measures].[Gross Actual Cost]

}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY

CrossJoin(

{[Dim Agency].[Agency Name].Children},

{[Dim Buy Type].[Buy Type Name].Children},

{[Dim Client].[Client Name].Children},

{[Dim Client].[Industry Name].Children},

{[Dim Date].[Broadcast Year Quarter].Children},

{[Dim Date].[Broadcast Year Week].Children})

ON ROWS

FROM [TV Ordered and Final]

WHERE ({ [Dim Unit Size].[Size].&[30]}, { [Dim Date].[Broadcast Year].&[2013-14]});

Failed If insert [Broadcast Year]:

LOAD

"[Measures].[Number Of Units]" as [Number Of Units],

"[Measures].[Gross Actual Cost]" as [Gross Actual Cost],

"[Dim Agency].[Agency Name].[Agency Name].[MEMBER_CAPTION]" as [Agency Name],

"[Dim Buy Type].[Buy Type Name].[Buy Type Name].[MEMBER_CAPTION]" as [Buy Type Name],

"[Dim Client].[Client Name].[Client Name].[MEMBER_CAPTION]" as [Client Name],

"[Dim Client].[Industry Name].[Industry Name].[MEMBER_CAPTION]" as [Industry Name],

"[Dim Date].[Broadcast Year].[Broadcast Year].[MEMBER_CAPTION]" as [Broadcast Year],

"[Dim Date].[Broadcast Year Quarter].[Broadcast Year Quarter].[MEMBER_CAPTION]" as [Broadcast Year Quarter],

"[Dim Date].[Broadcast Year Week].[Broadcast Year Week].[MEMBER_CAPTION]" as [Broadcast Year Week],

SELECT

{

[Measures].[Number Of Units],

[Measures].[Gross Actual Cost]

}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY

CrossJoin(

{[Dim Agency].[Agency Name].Children},

{[Dim Buy Type].[Buy Type Name].Children},

{[Dim Client].[Client Name].Children},

{[Dim Client].[Industry Name].Children},

{[Dim Date].[Broadcast Year].Children},

{[Dim Date].[Broadcast Year Quarter].Children},

{[Dim Date].[Broadcast Year Week].Children})

ON ROWS

FROM [TV Ordered and Final]

WHERE ({ [Dim Unit Size].[Size].&[30]}, { [Dim Date].[Broadcast Year].&[2013-14]});

@Mod: This question should put into "Scripting" Place, but I don't know why It doesn't appear in recent topics, seems no one can see it to support.

topic How to load dimension with filter itself in MDX Query Generator in QlikView

How to load dimension with filter itself in MDX Query Generator