SSRS mdx report: use dimension on columns

Question

This simple mdx query do work in SSMS:

SELECT  
CrossJoin({[Measures].[Qnt]},
          {[Sales_step].CHILDREN}) ON COLUMNS,
[City] ON ROWS  
FROM [SALES_PIPE]

But it fails to run within Visual Studio for rdl report. An error accures:

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)

I've found another syntax to retrieve results, but it's cols are hardcoded and script is slow:

WITH 
MEMBER [Measures].[Contacts] AS 
       CASE WHEN [Sales_step].CURRENTMEMBER IS [Sales_step].&[contact]               
       THEN [Measures].[Qnt] ELSE null END
MEMBER [Measures].[Clients] AS 
       CASE WHEN [Sales_step].CURRENTMEMBER IS [Sales_step].&[client]               
       THEN [Measures].[Qnt] ELSE null END
MEMBER [Measures].[Funded] AS 
       CASE WHEN [Sales_step].CURRENTMEMBER IS [Sales_step].&[funded]               
       THEN [Measures].[Qnt] ELSE null END

SELECT {[Measures].[Contacts],
        [Measures].[Clients],
        [Measures].[Funded]} ON COLUMNS,

NON EMPTY     
FILTER(crossjoin({[City].CHILDREN},                            
                 {[Sales_step].CHILDREN}),
       [Measures].[Contacts] > 0 OR
       [Measures].[Clients] > 0 OR      
       [Measures].[Funded] > 0) ON ROWS 

FROM [SALES_PIPE]

The part, which makes it very slow - is not calculated members, it is crossjoin of City and Sales_step dimensions on rows, which I have to make (othewise, with City dim on rows only, - I get null results on columns). Any suggestions how can I make my mdx faster?

Answer 1

Does this give the same results? Is it any quicker?

WITH 
  MEMBER [Measures].[Contacts] AS 
    Sum
    (
      IIF
      (
        [Sales_step].CurrentMember IS [Sales_step].&[contact],
       ,[Measures].[Qnt]
       ,null
      )
    ) 
  MEMBER [Measures].[Clients] AS 
    Sum
    (
      IIF
      (
        [Sales_step].CurrentMember IS [Sales_step].&[client]
       ,[Measures].[Qnt]
       ,null
      )
    ) 
  MEMBER [Measures].[Funded] AS 
    Sum
    (
      IIF
      (
        [Sales_step].CurrentMember IS [Sales_step].&[funded]
       ,[Measures].[Qnt]
       ,null
      )
    ) 
  SET [yourMeasures] AS 
    {
      [Measures].[Contacts]
     ,[Measures].[Clients]
     ,[Measures].[Funded]
    } 
  SET [CitySales] AS 
    [City].Children * [Sales_step].Children 
  SET [CitySalesNonEmpty] AS 
    Union
    (
      NonEmpty([CitySales],[Measures].[Contacts])
     ,NonEmpty([CitySales],[Measures].[Clients])
     ,NonEmpty([CitySales],[Measures].[Funded])
    ) 
/*
//>>>>Alternative syntax for above union>>
  SET [CitySalesNonEmpty] AS 
      NonEmpty([CitySales],[Measures].[Contacts])
     +NonEmpty([CitySales],[Measures].[Clients])
     +NonEmpty([CitySales],[Measures].[Funded])
//>>>>
*/
SELECT 
  [yourMeasures] ON COLUMNS
 ,NON EMPTY 
    [CitySalesNonEmpty] ON ROWS
FROM [SALES_PIPE];