An MDX query delivers results okay in SSMS, but the same query gives and error when used as SSRS source

Question

Hi,

I have an MDX query that I run in SSMS, and it delivers the results no problem. When the same query is however used in SSRS Query Designer I get the following error:

"Query preperation failed. The Query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies nor should it have any dimension other than the measures dimension.."

The query is shown below. The reason why the measures are in the rows is because I am trying to get the measures to act as row lables in a table in SSRS (they cant be on columns, they must be on rows). Can this query be tweaked to work as in SSRS Query Designer, or is there another way in which I can get my measures lables to act as if they are dimensions so that I can slice them on the rows?

 SELECT NON EMPTY { [Measures].[ed03_n_seen_thresh_flag]
 , [Measures].[ed03_d_seen_thresh_flag]
 , [Measures].[ed04_perc_admitted_flag]
 , [Measures].[ed04_n_admitted_flag]
 , [Measures].[ed04_d_admitted_flag]
 , [Measures].[ed03_perc_seen_thresh_flag]
 , [Measures].[ed02_perc_mh_flag]
 , [Measures].[ed02_d_mh_flag]
 , [Measures].[ed02_n_mh_flag] } ON ROWS, 
 NON EMPTY { ([Gen_Hospital].[Hierarchy].[Gen Hospital Desc].ALLMEMBERS *  
			  [Time Intelligence].[Time Intel Desc].[Time Intel Desc].ALLMEMBERS ) } 
 DIMENSION PROPERTIES MEMBER_CAPTION, 
 MEMBER_UNIQUE_NAME ON COLUMNS FROM [Cube_SAH_POC2] 
 CELL PROPERTIES VALUE, 
 BACK_COLOR, 
 FORE_COLOR, 
 FORMATTED_VALUE, 
 FORMAT_STRING, 
 FONT_NAME, 
 FONT_SIZE, 
 FONT_FLAGS

Answer 1

Check if this helps

http://www.robkerr.com/2011/02/ssas-mdx-measures-on-rows-in-ssrs.html

---

vinu

Answer 2

This at least clarifies that it is NOT possible, which seems strange as you would think pivoting would be a fundamental thing to do. The SQL solution is not really an option as we would like to leverage the strength in the cube. I have reverted to creating a seperate structire to deal with this, and then use perspectives to ring fence.