CODEWITHSUNDEEP
sqlreporting-servicesssasmdx
Anna
Anna

Reputation: 91

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

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!

Upvotes: 1

Views: 1518

Answers (2)

tember
tember

Reputation: 1496

Not sure if this question is still an issue but I had the same error.

The problem for me was a typo in the name of one the members. For example:

[Date].[Year].[Year].ALLMEMBERS

was [Date].[Fiscal Year].[Fiscal Year].ALLMEMBERS

So make sure all the levels of your members are spelt correctly and match exactly is what in the currently deployed version of the cube.

Upvotes: 1

Anna
Anna

Reputation: 91

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.

Upvotes: 1

Related Questions