MDX reports

[GrandMauler]

Ok, some time ago, when I was first researching Reporting Services, I came across a tutorial that showed how to use an MDX data source. I remember the tutorial used a window different from the one on the data tab in Reporting Services platform. It wasn't the Design Mode window.

I have created an MDX query that works both in Proclarity AND AS2005 query window.

However, when I copy the mdx script into the Design Mode window for the Data tab and run it, I get the following error:

TITLE: Microsoft Visual Studio
------------------------------

Query preparation failed.

------------------------------
ADDITIONAL INFORMATION:

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 reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)

------------------------------
BUTTONS:

OK
------------------------------

Here's the MDX script i'm using:

WITH                                                
                                                                                                
MEMBER [MEASURES].[PERCENT W over W] AS '            
IIF(([MEASURES].[MOU] , [WEEK DATE].[WEEK_STARTING].CURRENTMEMBER.PrevMember ) > 0,           
(([MEASURES].[MOU] , [WEEK DATE].[WEEK_STARTING].CURRENTMEMBER ) -                
([MEASURES].[MOU] , [WEEK DATE].[WEEK_STARTING].CurrentMember.PrevMember))                                                                       
/ ([MEASURES].[MOU] , [WEEK DATE].[WEEK_STARTING].CurrentMember.PrevMember), 0)           
',                                                                  
SOLVE_ORDER = 5, FORMAT_STRING = '0.0000%'                                                              
SELECT            
{([WEEK DATE].[WEEK_STARTING].[13 Oct 2007],[MEASURES].[PERCENT W over W]),ORDER({LastPeriods(12,  [WEEK DATE].[WEEK_STARTING].[13 Oct 2007])}, [MEASURES].[MOU]),    
[WEEK DATE].[WEEK_STARTING].CURRENTMEMBER.PROPERTIES("ID", TYPED),BDESC)}                                            ON COLUMNS,
                       
GENERATE(          
TOPCOUNT(          
FILTER([REGIONAL].[REGIONAL HIERARCHY].[OCN].MEMBERS,         
([MEASURES].[MOU], [WEEK DATE].[WEEK_STARTING].[13 Oct 2007] ) > 50000)         
,  50, ([MEASURES].[PERCENT W over W], [WEEK DATE].[WEEK_STARTING].[13 Oct 2007]) ) ,          
          
CROSSJOIN({[REGIONAL].[LATA].[LATA]},   [REGIONAL].[REGIONAL HIERARCHY].CURRENTMEMBER,    
 [REGIONAL].[OCN NAME].[OCN NAME],[REGIONAL].[OCN CLASS].[OCN CLASS],  
{[JURIS].[JURISDICTION].ALLMEMBERS})       
)                                 
                     
ON ROWS                    
FROM [GLBX Test CUBE]

If you have any good links to tutorials for MDX Reports, please feel free to share.

Thanks!

 

[xlbo]

Not sure whether it is this simple but the "view" you may be looking for is the "stored Procedure" option in the query design gui.

Unfortunately we don't use MDX so I have no links for tutorials

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question