0

I have an MDX query written. It is working without any error on SSMS, but when I create a dataset using the same query in SSRS, I get the below error:

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)

The query that I have written is:

WITH 
MEMBER [Measures].[AT] AS
    IIF(( [Measures].[Dato] = "AT"),"AT",NULL)

MEMBER [Measures].[N. Aziende] AS
    IIF(( ( [Mercato].[Mercato].CurrentMember.Properties("NAME") = "Azienda") OR ( [Measures].[N Aziende] = "AT")), NULL, [Measures].[N Aziende])

MEMBER [Measures].[Dato AT] AS
    IIF(( [Measures].[Dato] = "AT"), NULL, [Measures].[Dato])

MEMBER [Measures].[Rank] AS
    IIF(( ( [Mercato].[Mercato].CurrentMember.Properties("NAME") = "Totale Mercato") OR ( [Measures].[N Aziende] = "AT")), NULL, [Measures].[Posizione])

MEMBER [Measures].[Evol %] AS
    IIF(( [Measures].[Dato] = "AT"), NULL, [Measures].[Evol %])

MEMBER [Measures].[Quota %] AS
    IIF(( ( [Mercato].[Mercato].CurrentMember.Properties("NAME") = "Totale Mercato") OR ( [Measures].[N Aziende] = "AT")), NULL, [Measures].[Quota])

MEMBER [Measures].[Peso %] AS
    IIF(( ( [Measures].[N Aziende] = "AT") OR ( [TipoAnno].[Tipo Anno].CurrentMember.Properties("NAME") = "Anno Precedente")), NULL, [Measures].[Dato AT]/([Canali].[Descrizione Canale].[TOTALE],[Measures].[Dato AT]))

MEMBER [Measures].[Pos %] AS
    IIF(( ( ( [Mercato].[Mercato].CurrentMember.Properties("NAME") = "Totale Mercato") OR ( [Measures].[N Aziende] = "AT")) OR ( [TipoAnno].[Tipo Anno].CurrentMember.Properties("NAME") = "Anno Precedente")), NULL, [Measures].[Posizione %])

MEMBER [Measures].[Pos. Ent.] AS
    IIF(( ( ( [Mercato].[Mercato].CurrentMember.Properties("NAME") = "Totale Mercato") OR ( [Measures].[N Aziende] = "AT")) OR ( [TipoAnno].[Tipo Anno].CurrentMember.Properties("NAME") = "Anno Precedente")), NULL, [Measures].[Posizione assoluta])

SELECT  {
        [Measures].[Dato],
        [Measures].[AT],
        [Measures].[N. Aziende],
        [Measures].[Dato AT],
        [Measures].[Rank],
        [Measures].[Evol %],
        [Measures].[Quota %],
        [Measures].[Peso %],
        [Measures].[Pos %],
        [Measures].[Pos. Ent.]
        } ON COLUMNS,
    EXCEPT(
                [Canali].[Descrizione Canale].[Descrizione Canale].MEMBERS,
                {[Canali].[Descrizione Canale].&[00]}) ON ROWS

 FROM ( SELECT  ( {[Tempo].[Periodo].[Mese Anno].&[2012]&[12],
                   [Tempo].[Periodo].[Mese Anno].&[2013]&[12],
                   [Tempo].[Periodo].[Mese Anno].&[2014]&[12],
                   [Tempo].[Periodo].[Mese Anno].&[2015]&[12]}) ON COLUMNS
FROM ( SELECT  ( [GeografiaAgro].[Descrizione Regione ISTAT].&[01]) ON COLUMNS

FROM [AGRO]))
WHERE 
(
[TipoMisura].[Tipo Misura].&[1],
[TipoPeriodo].[Tipo Periodo].&[2],
[Mercato].[Mercato].&[1],
[Aziende].[Descrizione Codice Azienda].&[100 - BASF]
);

I am not able find what is the issue, what changes do I need to bring to the above so that I will work in creating SSRS dataset.

halfer
  • 19,824
  • 17
  • 99
  • 186
Yousuf Sultan
  • 3,055
  • 8
  • 35
  • 63
  • that is a strange one - error msg seems to disagree with the script. I would make the script a lot smaller and incrementally add bits to it; each time testing if you get the error. Make sure the starting point is so simple that there is no error. – whytheq Jun 16 '16 at 16:07
  • SSRS doesn't support multi-level column headers in the query results. You have to design the MDX query to basically return a table so that it can convert it into a dataset with columns. You can't mimic the output from SSMS directly into an SSRS tablix. – StevenWhite Jun 16 '16 at 22:27
  • @StevenWhite I think the point is that there are no multi-level column headers - not that I can see anyhow - or do I need another cup of coffee? – whytheq Jun 17 '16 at 09:32

0 Answers0