testing mdx query with parameters in SSAS

Question

I'd like to test a MDX query in SSAS. I did the following steps until now.

I opened the SQL Profiler and ran the SSRS report
I found the query I needed (with parameter values) and selected it sql profiler
I pasted the code in ssas
Since my SSRS MDX query used StrToMember and StrToSet functions, I surrounded the parameter values with quotes, e.g. “[Measures].[Return On Average Assets]”.

The query

SELECT 
  {
    [Measures].[Broj Pristapi]
   ,[Measures].[Broj Nalozi]
  } ON 0
 ,
    Except
    (
      StrToSet("[Dim Ucesnici].[Naziv Ucesnik].&;[owiefwjhoef]")
     ,{
        [Dim Ucesnici].[Naziv Ucesnik].&[kihebrgk]
       ,[Dim Ucesnici].[Name].&[blablabla]
      }
    )*
    StrToSet
    ("{ [Dim Web Service Web Method].[Web Service].&;[wsINAPBan],
        [Dim Web Service Web Method].[Web Service].&;[wsNAPSBan] }"
    )*
    StrToSet
    ("{ [Dim Web Service Web Method].[Web Method].[All]
       ,[Dim Web Service Web Method].[Web Method].&;[funNN_loadPP30Ban]
       ,[Dim Web Service Web Method].[Web Method].&;[funNN_loadPP50Ban]}"
    )*
    {
        StrToMember("[Dim Datumi].[Datum ID].&;[20170801]")
      : 
        StrToMember("[Dim Datumi].[Datum ID].&;[20170906]")
    }*
    StrToSet("[Dim Ucesnici].[Opis Tip Ucesnik].&;[Bank]") ON 1
 ,NON EMPTY 
    {
        [Dim Date].[Date].Children*
        [Dim Date].[Month].Children*
        [Dim Date].[MonthName].Children
    } ON 2
 FROM [DW];

I commented out the rest of the lines and just left the mdx query 6.When I ran the query it gave me an error "An MDX expression was expected while a full statement was specified."

I followed the following tutorial for my mdx testing http://prologika.com/how-to-test-ssrs-mdx-queries-in-sql-server-management-studio/

Any idea what the problem might be?

thank you so much ! although it says, Results cannot be displayed for cellsets with more than two axes.? how can i fix this? — Олга Димитровска
You cannot use more than two axis using SSMS. here you are using three 0, 1 and 2. You probably want to just cross join the set on axis 2 with the set on axis 1 and then you will get the desired results. — BICube
SSRS has a Query Designer built in to the Dataset Properties. You can drag and drop to build an MDX query in there. That way you don't have to edit any MDX manually. — StevenWhite

whytheq whytheq · Accepted Answer · 2017-09-07T08:27:42

Are you really using this in your member names? .&;[ ....the semi-colon is reserved for the end of mdx statements. Please take out all the semi-colons:

SELECT 
  {
    [Measures].[Broj Pristapi]
   ,[Measures].[Broj Nalozi]
  } ON 0
 ,
    Except
    (
      StrToSet("{[Dim Ucesnici].[Naziv Ucesnik].&[owiefwjhoef]}")
     ,{
        [Dim Ucesnici].[Naziv Ucesnik].&[kihebrgk]
       ,[Dim Ucesnici].[Name].&[blablabla]
      }
    )*
    StrToSet
    ("{ [Dim Web Service Web Method].[Web Service].&[wsINAPBan],
        [Dim Web Service Web Method].[Web Service].&[wsNAPSBan] }"
    )*
    StrToSet
    ("{ [Dim Web Service Web Method].[Web Method].[All]
       ,[Dim Web Service Web Method].[Web Method].&[funNN_loadPP30Ban]
       ,[Dim Web Service Web Method].[Web Method].&[funNN_loadPP50Ban]}"
    )*
    {
        StrToMember("[Dim Datumi].[Datum ID].&[20170801]")
      : 
        StrToMember("[Dim Datumi].[Datum ID].&[20170906]")
    }*
    StrToSet("[Dim Ucesnici].[Opis Tip Ucesnik].&[Bank]") ON 1
 ,NON EMPTY 
    {
        [Dim Date].[Date].Children*
        [Dim Date].[Month].Children*
        [Dim Date].[MonthName].Children
    } ON 2
 FROM [DW];

testing mdx query with parameters in SSAS

1 Answers