1 Reply Latest reply: Mar 19, 2014 7:07 AM by jay chen RSS

    How to load dimension with filter itself in MDX Query Generator

      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]});