SELECT { [COGS], [Sales], [Margin] } ON COLUMNS, {CROSSJOIN( [Market].Levels(1).ALLMEMBERS, [Year].Levels(1).ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, LEVEL_NUMBER ON ROWS FROM Sample.Basic CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING
The updated query is:
SELECT NON EMPTY { [COGS], [Sales], [Margin] } ON COLUMNS, NON EMPTY {CROSSJOIN( [Market].Levels(1).ALLMEMBERS, [Year].Levels(1).ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM Sample.Basic CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING
I highlighted the differences. Once I removed PARENT_UNIQUE_NAME from the dimension properties, the old report ran. I don't know where the NON EMPTY statement comes from except it may be a tweak added by the Microsoft developers to improve the XML/A for Essbase reports. Here is the finished report (from the preview screen in Visual Studio):
Posts
2 comments:
Tim,
Have you done any testing using Report Builder 2 (or BIDS) & Essbase 11.1.1?
I got the Connection, Dataset and some basic reporting working, but are running into endless issues when trying to do something just a little bit more complex:
- Essbase measures that has an alias that differs from the name of the measure returns a "null" value in my reports.
- Ampersand ("&") characters in my measure names causes errors in SSRS
- Keep on getting "Server sent unrecognizable response" type errors when bringing some measures\dimensions into my report.
- Query Designer throws an error when trying to edit an existing report query and I have to revert to modifying raw MDX.
- Editing raw MDX in Report Builder 2 throws errors all over the place
I'm suspecting that some of this has to do with invalid characters being passed in the XML, and hope there is some magic setting that will fix all of these, either on the Essbase side or on the SSRS side.
When you have a second and get around to playing with this, let me know if you see the same problems. I'm busy opening tickets with MS and hope for the best.
I don't know when I will have time to try that out.. One thought though.. Did you try the MDX syntax in the EAS editor?
Tim
Post a Comment