SSDT Query Builder: MDX Query returning NULL instead of AllMembers
*SSDT – SQL Server Data Tools
*SSMS – SQL Server Management Studio
In SSDT Query Designer getting Null for All members:
One of my customers recently reported this issue so thought of sharing with world……
Demo given below will provide an insight……………
Simple query, where we are doing cross join of allmembers from Product and Date dimension
select non empty ([Product].[Category].allmembers*[Date].[Calendar].[Calendar Year].allmembers ) on 1,
([Measures].[Internet Sales Amount]) on 0
from [Adventure Works]
Output in SSMS: All Products visible from SSMS
In SQL 2012 SSDT we have an option of writing MDX queries directly in SSAS Project.
Pretty simple if you haven’t used it…here you will find Query Writer option in same lines you will find in SSRS Project