SSAS 2012 - Using a non-hierarchy member in SSRS report

Question

I'm new to developing reports against Cubes and am having a heck of a time.

I have a cube (multidimensional SSAS 2012) with a Calendar dimension. The dimension has the typical Calendar Hierarchy, plus a Year member that is outside of the hierarchy (not sure what this kind of attribute is called).

I want to use the Year as a report parameter because I don't like the way hierarchy parameters look in SSRS and all the user requires here is the Year. So I set the Year member as a parameter in the Query Designer. Got an error upon viewing the report, so I removed it as a parameter and added it to the query output to see if it helped, but I keep getting that same error:

Query execution failed for dataset 'DateCalendar'. 
Query(1, 368) The ALLMEMBERS function expects a hierarchy expression for 
the argument. A member expression was used.

Note that I haven't created a dataset called 'DateCalendar'.

Here's the MDX the Query Designer spat out:

SELECT 
    NON EMPTY 
        { 
            [Measures].[%Completion] 
        } ON COLUMNS, 
    NON EMPTY 
        { 
            (
                [Physician].[PCP Name].[PCP Name].ALLMEMBERS 
                    * 
                [Physician].[LY Physician Score].[LY Physician Score].ALLMEMBERS 
                    * 
                [Date].[Year].[Year].ALLMEMBERS 
            ) 
        } 
        DIMENSION PROPERTIES MEMBER_CAPTION, 
        MEMBER_UNIQUE_NAME 
    ON ROWS 
FROM [MemberMeasure] 
    CELL PROPERTIES VALUE,
    BACK_COLOR, 
    FORE_COLOR, 
    FORMATTED_VALUE, 
    FORMAT_STRING, 
    FONT_NAME, 
    FONT_SIZE, 
    ONT_FLAGS

I'm very confused and in a big hurry and not having much luck googling an answer. Any help would be greatly appreciated!

As your query is re-formatted it is difficult to see where line 1, column 368 - which is reported as the error location - is. Which expression starts or ends at this position and contains AllMembers? — FrankPl
I know... this is more or less how the Query Designer formatted it. All I did was line wrap it. When I copied it out of the designer, it was all on one line. I don't know what the error is referring to by line 1, column 368. What can I do to help locate that spot? Do I need to run it in SSMS and locate the 368th column? — Anna
To expand on my last question, does line 1, column 368 refer to something in the result set that I need to locate somehow? — Anna
No, line 1, column 368 refers to a position in the query, as it states ("Query(1, 368)"). But if I delete all the line changes from your query, then this position is somewhere in the middle of the word FORMATTED_VALUE which does not make with the text of error message. Anyway, it seems you found the error now, and it seems the message is referring to another query (one for the parameters, as I understand). — FrankPl
I Format this query. and I think ONT_FLAGS parameter has error. — Ardalan Shahgholi

Anna Anna · Accepted Answer · 2014-01-22T20:53:57

I still don't know what the error means, but I think it persisted due to hidden datasets left over from parameters I got rid of. Wow, that's annoying.

SSAS 2012 - Using a non-hierarchy member in SSRS report

2 Answers