Error messages in MDX SELECT statements and what they mean

Anyone that has tried to learn MDX will know that, when you make a mistake somewhere in your code, the error messages that Analysis Services gives you are pretty unhelpful. It was suggested to me recently while I was teaching an MDX course that I should blog about common error messages and what they actually mean; so here’s a list of a few example queries using Adventure Works that return confusing errors, the error messages themselves, and details on how to solve the problems. I’ve deliberately concentrated on query-related errors rather than calculation-related errors (that can be a future blog post); if you can think of any more errors that I should cover please leave a comment.

1) Query causing error:

SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS 
[Date].[
Calendar Year].MEMBERS ON ROWS
FROM [Adventure Works]

Error message: Query (3, 1) Parser: The syntax for ‘[Date]’ is incorrect.

The first step to solving this fairly simple syntax error is understanding the values in brackets in the error message. (3,1) indicates that the error is at character 1 on the third line of the query, where we have the expression [Date].[Calendar Year].MEMBERS; we should also see a red squiggly underneath this text in SQL Management Studio. There’s nothing wrong with this expression though, apart from the fact that it’s in the wrong place: what has happened is that we’ve forgotten to include a comma after COLUMNS immediately beforehand. If we put one in, the query runs.

Solution:

SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS, 
[Date].[
Calendar Year].MEMBERS ON ROWS
FROM [Adventure Works]

 

2) Query causing error:

SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].MEMBERS.CHILDREN ON ROWS
FROM [Adventure Works]

Error message: Query (3, 1) The CHILDREN function expects a member expression for the 1 argument. A tuple set expression was used.

This is a very common error that people encounter while learning MDX, and it all comes down to understanding the difference between sets, tuples and members. In a lot of situations Analysis Services is very forgiving: if it expects a set and you give it a single member, then it will cast that member into a set with one item in it for example. It can’t do this for you all the time, though, and you do need to understand what kind of object each function returns and/or expects for a parameter. In this case, the problem is that the .CHILDREN function needs to be passed a member and the .MEMBERS function returns a set (strictly speaking, as the error says, it’s a set of tuples); therefore we can’t use the two functions together. If we want to find all of the children of all years, we can use the DESCENDANTS function instead, which can accept a set as its first parameter.

Solution:

SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS,
DESCENDANTS(
[Date].[Calendar].[Calendar Year].MEMBERS
, [Date].[Calendar].[Calendar Semester])
ON ROWS
FROM [Adventure Works]

 

3) Query causing error:

SELECT
[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount] ON COLUMNS,
[Date].[Calendar].[Calendar Year].MEMBERS
ON ROWS
FROM [Adventure Works]

Error message: Parser: The statement dialect could not be resolved due to ambiguity.

Analysis Services supports no less than three query languages: MDX, DMX and a very limited subset of SQL. As a result, when you run a query it needs to work out what query language you’re using and can easily get confused if you make a mistake. In the query above we’ve given a list of the two measures we want to see on the columns axis, but we’ve forgotten to surround this list in braces to turn it into a set – and it’s a set that is required for the axis definition. This is an error that is commonly made by people with a background in SQL, and indeed the problem here is that the error has made the query look a bit too much like SQL or DMX. Putting in braces where they’re needed fixes the problem and removes the ambiguity.

Solution:

SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].MEMBERS
ON ROWS
FROM [Adventure Works]

4) Query causing error:

SELECT
{[Measures].[Internet Sales Amount1], [Measures].[Internet Tax Amount]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].MEMBERS
ON ROWS
FROM [Adventure Works]

Error message: Query (2, 2) The member ‘[Internet Sales Amount1]’ was not found in the cube when the string, [Measures].[Internet Sales Amount1], was parsed.

A fairly straightforward error this: we’ve tried to reference a member that doesn’t exist in our query – it’s the extra 1 on the end of the name that’s the problem. The way to avoid this is to always let Analysis Services generate unique names for you, and you can do this by dragging the member (or any other object) from the metadata pane in SQL Management Studio into the MDX query pane when you’re writing queries. Here, using the correct member unique name solves the problem.

Solution:

SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].MEMBERS
ON ROWS
FROM [Adventure Works]

Note that for dimensions other than the Measures dimension, what happens in this scenario depends on how you’ve set the MDXMissingMemberMode property. By default if you write something that looks like it could be an MDX unique name, but which isn’t actually the unique name of a member on a hierarchy, Analysis Services will simply ignore it. So the following query returns nothing on rows because the year 2909 doesn’t exist in our Calendar hierarchy:

SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS,
{[Date].[Calendar].[Calendar Year].&[2909]}
ON ROWS
FROM [Adventure Works]

And worse, the in this query a genuine syntax error is completely ignored too:

SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].MAMBERS
ON ROWS
FROM [Adventure Works]

16 thoughts on “Error messages in MDX SELECT statements and what they mean

  1. Hi Chris, my MDX is as follows:
    Sum
    (PeriodsToDate
    ([Date].[Financial].[Financial Year],[Date].[Financial].CurrentMember),
    [Measures].[Budget]
    )

    And I have got err as follows:
    “The Form View cannot be displayed because the MDX script has the following syntax error: ‘Parser: The end of the input was reached. [Line:0; Column:0]’ To correct this error, click Script View on the toolbar, and then edit the MDX script to correct the syntax.”

    Do you have any idea what that would be? Thank you in advance for your help, Chris.
    Cheers, Shirley

    • Thank you! Query / solution #3 fixed my problem – that’s a gotcha that I won’t forget. Appreciated.

  2. Hi Chris,
    I am trying to filter on a date range and getting the following error.
    Filter:

    SET [FilterDate] as
    filter ([Claims Due Date].[Date].[Date].Members,[Claims Due Date].[Date].MemberValue>=cDate(“04/01/2014”) : [Claims Due Date].[Date].MemberValue<=cDate("11/20/2014")
    )
    Error:
    The Range function expects a member expression for the 1 argument. A string or numeric expression was used.

    Any ideas on what I am doing wrong.

    Regards, Janet

    • Hi Janet,

      I think you need an AND condition here, not a range. Try
      SET [FilterDate] as
      filter ([Claims Due Date].[Date].[Date].Members,[Claims Due Date].[Date].MemberValue>=cDate(“04/01/2014″) and [Claims Due Date].[Date].MemberValue<=cDate("11/20/2014")
      )

      The range operator : takes two members on either side, for example something like this
      [Claims Due Date].&[Date].[01042014] : [Claims Due Date].&[Date].[20112014]
      … which is likely to be a more efficient way of solving the problem than using the filter() function as in the previous example.

      • Thank you for your quick reply, Chris.
        When I use the AND I receive no results yet, when I just use the following I get results that would fall between those two dates.
        SET [FilterDate] as
        filter ([Claims Due Date].[Date].[Date].Members,[Claims Due Date].[Date].MemberValue>=cDate(“04/01/2014”) )

        Your suggestion above is to use the range but as a where clause instead of a function?

      • I tried using this [Claims Due Date].&[Date]>=[20140401] : [Claims Due Date].&[Date]<=[20141120] and received an error " The dimension '[20140401]' was not found in the cube when the string, [20140401], was parsed.

        This however does receive results:
        [Claims Due Date].[Date].&[20140401]
        as does this:
        [Claims Due Date].[Date].&[20141120]

        It seems determined to not allow me to use a range.

      • I think you’re getting confused here between what’s part of the unique name of a member and what’s a value. Can you try something for me? Open a new MDX query window in SQL Server Management Studio, expand the Date hierarchy on the Claims Due Date dimension and drag the member for 1st January 2014 into the query window. Does it display [Claims Due Date].[Date].&[20140401] ? If so, then try
        SET [FilterDate] as [Claims Due Date].[Date].&[20140401] : [Claims Due Date].[Date].&[20141120]

        In this example [Claims Due Date].[Date].&[20140401] is the unique name of the member 1st January 2014, and when you are using : you can only put member unique names on either side. The : gets a set of members from the member on the left hand side to the member on the right hand side. You can’t use operators like > or = with member unique names.

    • I’m sorry. I have been trying so many different scenario’s I wound up giving you some bad information. The actual format is [Claims Due Date].[Date].&[20140401]. One thing I had tried was this SET [FilterDate] as { [Claims Due Date].[Date].&[2014-04-11] : [Claims Due Date].[Date].&[2014-11-20] }, which I see my error from what you sent, I had { }. Removing those brackets works.

      The reason I initially sent this format “04/01/2014” is because I am using parameters that are to be set by the date picker. Now that I have the range piece working, I will work on converting the dates.

      I thank you very much for your assistance and patience. I am VERY new to this and have had no training.

      Regards and thank you, Janet

  3. Hi Chris,
    I am a beginner in MDX ..
    select {([Date].[Calendar].currentmember.name)}on 0
    from [Adventure Works]
    Above query gives me error “Query (1, 8) The function expects a tuple set expression for the 1 argument. A string or numeric expression was used.”

    I can see that it is fixed when I re-frame it as below:
    with member A
    as [Date].[Calendar].currentmember.name
    select A on 0
    from [Adventure Works]
    But I am not able to grasp the difference! If it lets me select the round-about way, why doesn’t it let me select the direct way?

  4. Hi Chris,
    In database, there are many hierarchies we removed one hierarchy and when processed the cube getting the below error in MDX calculations.

    MdxScript ( Data Base Name ) The [ Level] plane was ‘ [ Dimension ] . [Hierarchy ] . [ Level] ‘ not found while parsing the string you see in the cube .
    The END SCOPE statement does not match the opening SCOPE statement .
    MdxScript ( Data Base Name ) END SCOPE statement does not match the opening SCOPE statement match .
    The MDX script contains at least one error

    Is it because of data issue?

    • No, it sounds like you have an MDX calculation (probably a SCOPE statement) that references the hierarchy you have deleted. You either need to put the hierarchy back or rewrite the MDX expression that references it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s