Excel Pivot Table Is not bringing Data RRS feed

  • Question

  • Hi

    Pivot table is not pulling the data for dimension member combination

    When I extracted the MDX query from SSAS Profiler and tried to execute the same query in SSMS data is not retrieving.

    Pivot Table Query is using the Sub Query , for the same dimension member combination Traditional MDX (With out Sub Queries ) is able to retrieving data.

    Below are some of the example queries

    MDX Query from Excel Pivot Table

    SELECT NON EMPTY Hierarchize({DrilldownLevel({[Time].[All Time]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize({[Account].[Level 01].Members}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS  FROM (SELECT ({[Time].&[5061], [Time].&[5062]})ON COLUMNS , ({[Account].&[1406]})ON ROWS  FROM [RepCube]) WHERE ([Company].&[166],[Scenario].&[1],[Solution].&[1],[Set_of_Books].&[147],[Department].&[1],[Reporting].[Level 1].&[14],[Product].&[101],[Location].&[101],[Measures].[MTD]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

    For same selections The traditional MDX

    select {Hierarchize({{Descendants({[Account].&[1406]},0,SELF)}})} on rows, {Hierarchize({{Descendants({[Time].&[5061],[Time].&[5062]},[Time].[Month],SELF)}})} on columns from RepCube where (([Scenario].&[1],[Company].&[166],[Measures].[MTD],[Department].&[1],[Location].&[101],[Set_of_Books].&[147],[Reporting].[Level 1].&[14],[Solution].&[1],[Product].&[101]),[CalculatedDim].&[1],[Customer].&[1])

    Is there any way that we can control the query generated by Pivot Table in Excel pragmatically (i.e. to change the query formation based on the selections)

    Can any one help us on how to solve this issue



    Friday, July 13, 2012 4:35 PM

All replies

  • I am not sure if it will be helpful.....

    get the PivotCache of Pivottable and use SaveAsOdc method.This will give you an odc file.In this ODC file you can change the query.

    Best Regards,
    Asadulla Javed, Kolkata
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Friday, August 17, 2012 12:57 PM