sql - MDX filtering by multiple dimension members -
problem
i need create report list number of accounts match criteria - simulationdate, statisticperiod, region.
right query looks this:
with member [measures].[count] 1 select [measures].[count] on columns, non empty crossjoin( [account].[account number].allmembers, {[simulationdate].[day].&[10010101]}, {[statisticperiod].[period].&[201201]}, {[region].[code].&[so]} ) on columns [mywh] is cross-dimensional filtering okay?
this more modern using * notation instead of crossjoin function:
with member [measures].[count] 1 select [measures].[count] on columns ,non empty [account].[account number].allmembers* {[simulationdate].[day].&[10010101]}* {[statisticperiod].[period].&[201201]}* {[region].[code].&[so]} on columns [mywh]; i'm assuming custom measure [measures].[count] place-holder?
this table wide if have cross-join on columns might typo:
with member [measures].[count] 1 select [measures].[count] on columns, non empty [account].[account number].allmembers* {[simulationdate].[day].&[10010101]}* {[statisticperiod].[period].&[201201]}* {[region].[code].&[so]} on rows [mywh]; you have added keywords non empty in front of rows cross-join. telling processor exclude rows empty - empty [measures].[count] ....but measure never empty equal 1. following without non empty should return same result:
with member [measures].[count] 1 select [measures].[count] on columns, [account].[account number].allmembers* {[simulationdate].[day].&[10010101]}* {[statisticperiod].[period].&[201201]}* {[region].[code].&[so]} on rows [mywh]; so in terms of filtering aren't doing - sort of filtering need? if replace [measures].[count] actual measure cube , use non empty should see lot less rows:
select [measures].[replacewithactualmeasure] on columns, non empty [account].[account number].allmembers* {[simulationdate].[day].&[10010101]}* {[statisticperiod].[period].&[201201]}* {[region].[code].&[so]} on rows [mywh];
Comments
Post a Comment