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

i ran mdx query on sql 2012 looking products , shipping status

the code 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 [abcd]

 where ( 
             [channel].&[ol], 
             [geography].&[a.9])

the query gives desired output when run in sql. when run same query in while pulling data in powerpivot, dimensions i.e. date , item populated measures not populated. can please me understand how populated measures in powerpivot?

 

hi apoorv,

it hard reproduce issue on test environment. suggest try use simple mdx query check if retrun correct measures data. please see screenshot below:

in addition, can create sql server analysis services trace capture events furhter investigation.
use sql server profiler monitor analysis services:http://technet.microsoft.com/en-us/library/ms174946.aspx

hope helps.

---

elvis long
technet community support

SQL Server  >  Power Pivot