locked
MDX query runs on SQL but does not populate 'Measures' in Powerpivot RRS feed

  • Question

  • I ran MDX query on SQL 2012 for looking the products and their shipping status

    The code is given below:

                      

    WITH 
    set Date1 AS 
         strtomember( "[Time].[2014].[Q4].[2014-Jun].[" +format(now()-2, "MM") + "/"+format(now()-2, "dd") + "/" +format(now()-2, "yyyy") + "]")

    set Item1 as
    { [Item].[ItemID].ALLMEMBERS}

    SELECT NON EMPTY { [Measures].[Shipped Qty], [Measures].[Shipped Amt], [Measures].[Ordered Qty], [Measures].[Ordered Amt] }
    ON COLUMNS, 
    Date1*[Item1] ON ROWS FROM [ABCD]

     WHERE ( 
                 [Channel].&[OL], 
                 [Geography].&[A.9])

    The query gives desired output when run in SQL. But when I run the same query in while pulling data in PowerPivot, Dimensions i.e. Date and Item are populated but measures were not populated. Can anyone please help me understand how to populated Measures in PowerPivot?

     
    Wednesday, June 11, 2014 7:03 PM

Answers

  • Hi apoorv,

    It was hard to reproduce this issue on my test environment. I would suggest you try to use a simple MDX query to check if it will retrun the correct measures data. Please see the screenshot below:

    In addition, you can also create a SQL Server Analysis Services trace to capture some events for furhter investigation.
    Use SQL Server Profiler to Monitor Analysis Services:http://technet.microsoft.com/en-us/library/ms174946.aspx

    Hope this helps.


    Elvis Long
    TechNet Community Support

    Thursday, June 19, 2014 5:03 AM