0

Error Adding MDX Query for an ESSBASE Table

Hello,

I have written my first MDX query to create a new table within an Essbase schema, as shown below. 

SELECT NON EMPTY 
CROSSJOIN ({[Qtr1],[Qtr2],[Qtr3],[Qtr4]},
CROSSJOIN({[Actual]},
CROSSJOIN({[2018]},
CROSSJOIN({[Periodic]},
CROSSJOIN({[Total_Location]},
CROSSJOIN({[FM ICP Top]},
CROSSJOIN({[USD Total],[CAD Total],[AUD Total],[FM Entity Curr Total]},
CROSSJOIN({[Financial_Statement_Detail]},
CROSSJOIN({[Total_Department]},
CROSSJOIN({[Total_Source]},
CROSSJOIN({[Revenue]} )))))))))))

I am given an error message, "Syntax error in input MDX query on line 12 at token ')' ".  I have spent an hour counting "(" and ")", and I am sure I have 11 of each.  So there must be something else that is wrong. Can you please tell me what the syntax error is, and how to fix it?

Thank you, Kevin

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Kevin, the problem is in the very last CROSSJOIN. A  CROSSJOIN pulls together two MDX sets. In your last CROSSJOIN you only have one set. If you remove the last CROSSJOIN and 1 of the ending parentheses, you should be fine. I am answering this from my mobile device now, but I can post an updated version from my computer later tonight. 

    Like
  • Kevin,

    Here is an updated MDX statement. Please note that I expanded that statement to include ROW, COLUMN, and DATA SOURCE designations. Please let me know if you have further issues. You can email me at mike.nader@incorta.com .

     

    SELECT NON EMPTY 
    CROSSJOIN ({[Qtr1],[Qtr2],[Qtr3],[Qtr4]},
    CROSSJOIN({[Actual]},
    CROSSJOIN({[2018]},
    CROSSJOIN({[Periodic]},
    CROSSJOIN({[Total_Location]},
    CROSSJOIN({[FM ICP Top]},
    CROSSJOIN({[USD Total],[CAD Total],[AUD Total],[FM Entity Curr Total]},
    CROSSJOIN({[Financial_Statement_Detail]},
    CROSSJOIN({[Total_Department]},
    CROSSJOIN({[Total_Source]},{[Revenue]} ))))))))))
    On Rows,
    {[XXXX]} on Columns
    FROM [App_Name].[DB_Name]

    Like
    • Mike Nader 

      Thank you Mike.

      I know better than anyone else that I do not know what I am doing yet. I brought in all the Essbase dimensions as tables. What I have to do now, I think, is an MDX table to join the dimensions. I look at the crossjoin like a fix statement, to connect a subset of the data. So I tried very similar to what you gave me. I expect the FROM to be supplied by the wizard, but it complains either way.  It still says I have a syntax error on the ')' on the last crossjoin line.

       

      SELECT NON EMPTY 
      CROSSJOIN ({[Qtr1],[Qtr2],[Qtr3],[Qtr4]},
      CROSSJOIN({[Actual]},
      CROSSJOIN({[2018]},
      CROSSJOIN({[Periodic]},
      CROSSJOIN({[Total_Location]},
      CROSSJOIN({[FM ICP Top]},
      CROSSJOIN({[USD Total],[CAD Total],[AUD Total],[FM Entity Curr Total]},
      CROSSJOIN({[Financial_Statement_Detail]},
      CROSSJOIN({[Total_Department],{[Total_Source]} )))))))))
      On Columns,
      {[Revenue]} on Rows
      FROM [App_Name].[DB_Name]

       

      I will be away next week, so perhaps we can continue in September, if you are busy this morning.

      Thank you, Kevin

      Like
    • Kevin Jacklin Kevin, in this case, the from is not supplied by the wizard. With the Essbase connector, the wizard handles the dimension extracts. For the data, you need to add a table to the schema, select Essbase as the type, and enter the complete MDX statement in the box (including the FROM portion with you APP/DB name specified). Also, please note that this query is only going to return 32 lines of data. Assuming that is your intention, it can be written much simpler. Please try the query below. If it does not work, send me an email mike.nader@incorta.com and I can hop on a quick webcast with you.

       

      SELECT NON EMPTY 
      CROSSJOIN ({[Qtr1]:[Qtr4]},
      CROSSJOIN ({([Actual],[2018],[Periodic],[Total_Location],[FM ICP Top],[Financial_Statement_Detail],[Total_Department],[Total_Source])},
      {[USD Total],[CAD Total],[AUD Total],[FM Entity Curr Total]})))
      ON ROWS,
      {[Revenue])} ON COLUMNS
      FROM [App_Name].[DB_Name]

      Like
Like Follow
  • 3 yrs agoLast active
  • 4Replies
  • 220Views
  • 3 Following

Product Announcement

A new community experience is coming! If you would like to have beta access to provide feedback, please contact us at community@incorta.com.