none
The Measures Hierarchy is used more than once in a CrossJoin Function

    Question

  • Hi Folks,

    I am encountering a rather frustrating error in my SSRS report. It deals with MDX so I am posting this question in the SSAS forum. I have to create a report which should look something like this.

      YTD1  YTD2
    Product Measure 1 Measure 2 Measure 3 Measure 1 Measure 2 Measure 3
    A
    B
    C
    D            

    I am writing an MDX using cross join to fetch this data, while the MDX works fine in SQL Server Management Studio, as soon as I run in on the MDX Editor in SSRS, I get the error, "The Query must at least have one axis". The MDX that I am using is

    WITH

    SET

     

    [Top 10 Manufacturers] AS

    TOPCOUNT

     

    (

    { [Product].[Manufacturer].[Manufacturer].

    allMembers},

    10,

    (

    [Measures].[SALES VALUES(000)],

    [PERIOD].[MAT].&[MAT01])

    )

     

    SELECT NON EMPTY

     

    CROSSJOIN( {[PERIOD].[Months in Year].&[1],[PERIOD].[Months in Year].&[2]},

    {[Measures].[AVG SALES PRICE],

    [Measures].[AVG SALES PRICE CU],

    [Measures].[SALES VALUES]

    } )

    ON COLUMNS,

    [Top 10 Manufacturers]

    on rows

     

    FROM [MYCUBE]

    P.S. I need both the measures as well as the Hierarchy (MAT) on columns.

    Any help will be greatly appreciated.

    Thanks,

    Suman Sarkar

     

    Friday, September 03, 2010 1:06 PM

Answers

  • Hi Suman,

    Be aware that for SSRS there's no relation between the columns and rows as specified in your mdx-query (dataset) and the location of the measures in the dataregions in your SSRS report

    Adjust your mdx query, simply put  your measures on the column axis and move forward from there.

     

    hth,

    Cees

     


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Friday, September 03, 2010 2:44 PM
  • Hi Suman,

    I have tested it on reporting services and its working as expected without any problem. I have used SSRS 2008.

    Do one thing...

    1) Create a new shared datasource called test

    2) select Microsoft oledb provider for  analysis services 10.0 as OLEDB Provider.

    3) create a new dataset and use the created shared datasource in it and check.

     

    Regards

     Gokul

    Friday, September 03, 2010 2:52 PM

All replies

  • Hi,

    Try this sample query which is created on adventureworks database,

    WITH

     

    SET

    [TOP 10 SubCategories]

    AS TOPCOUNT

    ({[Product].[Product Categories].[Subcategory].

    Allmembers},10,([Measures].[Sales Amount],[Product].[Product Categories].[Category].&[4]))

    SELECT

     

    {[Date].[Calendar].[Calendar Year].&[2001] ,

    [Date].[Calendar].[Calendar Year].&[2002]}

    *

    {[Measures].[Tax Amount],[Measures].[Average Unit Price]}

    ON

     

    COLUMNS,

    [TOP 10 SubCategories]

    ON ROWS

    FROM

     

    [Adventure Works]

     

    Regards

     Gokul

    Friday, September 03, 2010 2:29 PM
  • Hi Gokul,

    Thanks for the early reply. This example will work in SSMS but it will not work in the SSRS MDX query editor. The error that you will get in SSRS editor is " Only measures can be selected On COLUMNS". Can you please check whether this query works in SSRS editor.

    Thanks,

    Suman Sarkar

    Friday, September 03, 2010 2:32 PM
  • Hi Suman,

    Be aware that for SSRS there's no relation between the columns and rows as specified in your mdx-query (dataset) and the location of the measures in the dataregions in your SSRS report

    Adjust your mdx query, simply put  your measures on the column axis and move forward from there.

     

    hth,

    Cees

     


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Friday, September 03, 2010 2:44 PM
  • Hi Suman,

    I have tested it on reporting services and its working as expected without any problem. I have used SSRS 2008.

    Do one thing...

    1) Create a new shared datasource called test

    2) select Microsoft oledb provider for  analysis services 10.0 as OLEDB Provider.

    3) create a new dataset and use the created shared datasource in it and check.

     

    Regards

     Gokul

    Friday, September 03, 2010 2:52 PM
  • Hi Gokul,

    Thanks for the information. I shall test it using the OLEDB Provider. Till now I was using the Analysis Servecis provider.

    I will revert in case of further issues.

    Regards,

    Suman Sarkar

    Friday, September 03, 2010 3:05 PM