0 Replies Latest reply: Jan 21, 2013 5:26 PM by Zhenya Virina RSS

    Creating P&L and Balance Sheet Reports using MDX

      Hi QV community,

       

      I am working a project where I need to build P&L/BS reports in QV. I need to achieve this through MDX. I was able to successfully load information into pivot/straight tables. But as you know pivot does not work for this type of reports.

       

      Our hierarchies spans across different levels, therefore I am forced to create a mapping table that will help QV identify & group accounts into specific categories. 

       

      I am struggling with running 'intervalmatch' statement by combining QV syntax with MDX . My query simply fails.

       

      Does anyone have any suggestions or experience with building hierarchies using MDX & QV?

       

      Any other suggestions on how to go about this?

       

      Please help!

       

      PLTemp:

      SELECT

        { null } on 0,

              ([Account].[H1].[LEV14])on 1

      FROM [Finance];

       

       

      SELECT

       

      { null } on 0,

       

      ([Account].[H1].[LEV14]) on 1

      FROM [Finance];

       

      IntervalMatchTemp:

      INTERVALMATCH ("[Account].[H1].[LEV14]") LOAD [GL Account Start], [GL Account End] RESIDENT Layout;

       

      IntervalMatch:

      Load

      distinct

      ("[Account].[H1].[LEV14]"),

      [GL Account Start] & '|' & [GL Account End] as GLStartEnd

      resident

      IntervalMatchTemp;

       

      drop table IntervalMatchTemp;