ssas - MDX query to pivot table based on condition -

i'm trying write mdx query pivot table.

similar query in rdbms this:

select  stats_date         ,isnull(sum(clicks), 0) clicks         ,isnull(sum(case when ad_type in (1,3) clicks end), 0) keyword_clicks         ,isnull(sum(case when ad_type in (2,3) clicks end), 0) direct_clicks    stats_table (nolock)   stats_date between '2015-06-01' , '2015-06-30' group stats_date 

i've 2 dimensions [dim time] & [dim ad type]

i've tried below mdx query this:

with         member [measures].[clicks keyword]              iif         (           [dim ad type].[ad type].currentmember [dim ad type].[ad type].&[1]          ,[measures].[clicks]          ,0         ) select  {           [measures].[clicks]          ,[measures].[clicks keyword]         } on columns         ,{            [dim time].[calendarhierarchy].[date]*[dim ad type].[ad type].[ad type]         }  on rows    [cm_stats_cube]   ([dim time].[month].&[201506]:[dim time].[month].&[201506]}) 

sample output of mdx query looks this:

                       clicks       clicks keyword 20150501    invalid    (null)       0              20150501    unknown    200          0 20150501    keyword    500          0 20150501    ads        300          300 20150502    invalid    (null)       0 20150502    unknown    400          0 20150502    keyword    600          0 20150502    ads        500          500 

but want group stats_date , expected output is:

            clicks      clicks keyword 20150501    1000        300 20150502    1500        500     

similar example testing in [adventure works] cube database:

with    member [measures].[internet sales amount us]       iif( [customer].[customer geography].currentmember [customer].[customer geography].[country].&[united states]          ,[measures].[internet sales amount]          ,null        ) select {          [measures].[internet sales amount]         ,[measures].[internet sales amount us]        } on 0        ,non empty{[date].[calendar].[date]} on 1 [adventure works]   {[date].[date].&[20050701]:[date].[date].&[20050702]} 

you don't need bother cross-join [dim time].[calendarhierarchy].[date]*[dim ad type].[ad type].[ad type]

with    member [measures].[clicks keyword]      iif     (       [dim ad type].[ad type].currentmember [dim ad type].[ad type].&[1]      ,[measures].[clicks]      ,0     )  select    {     [measures].[clicks]    ,[measures].[clicks keyword]   } on columns  ,{[dim time].[calendarhierarchy].[date]} on rows [cm_stats_cube]    [dim time].[month].&[201506] : [dim time].[month].&[201506]; 

also suggest using null rather 0 in iif function - should tidy result , speed things up:

with    member [measures].[clicks keyword]      iif     (       [dim ad type].[ad type].currentmember [dim ad type].[ad type].&[1]      ,[measures].[clicks]      ,null   //<<<<<<<<<<<<<<<<< better use null rather 0     )  select    {     [measures].[clicks]    ,[measures].[clicks keyword]   } on columns  , non empty  //<<<<<<<<<<<<<<<<< if clicks , clicks keyword both null respective row excluded   {[dim time].[calendarhierarchy].[date]} on rows [cm_stats_cube]    [dim time].[month].&[201506] : [dim time].[month].&[201506]; 

---

edit

i'd not read script in enough detail - apologies. can just aggregate set of 2 tuples:

with    member [measures].[clicks keyword]      sum     (      {       ([dim ad type].[ad type].&[1],[measures].[clicks])      ,([dim ad type].[ad type].&[3],[measures].[clicks])      }     )  select    {     [measures].[clicks]    ,[measures].[clicks keyword]   } on columns  , non empty  //<<<<<<<<<<<<<<<<< if clicks , clicks keyword both null respective row excluded   {[dim time].[calendarhierarchy].[date]} on rows [cm_stats_cube]    [dim time].[month].&[201506] : [dim time].[month].&[201506]; 

the advwrks example posted single tuple:

with    member [measures].[internet sales amount us]         (         [customer].[customer geography].[country].&[united states]        ,[measures].[internet sales amount]       ) select {          [measures].[internet sales amount]         ,[measures].[internet sales amount us]        } on 0        ,non empty{[date].[calendar].[date]} on 1 [adventure works]   {[date].[date].&[20050701]:[date].[date].&[20050702]} 

if wanted add in canada there seem 3 viable alternatives:

1.

with    member [measures].[internet sales amount & canada]         (         [customer].[customer geography].[country].&[united states]        ,[measures].[internet sales amount]       )     +       (         [customer].[customer geography].[country].&[canada]        ,[measures].[internet sales amount]       ) 

2.

    member [measures].[internet sales amount & canada]      aggregate     (       {         [customer].[customer geography].[country].&[united states]        ,[customer].[customer geography].[country].&[canada]       }      ,[measures].[internet sales amount]     )  

3. (switch sum)

with     member [measures].[internet sales amount & canada]      sum     (       {         (           [customer].[customer geography].[country].&[canada]          ,[measures].[internet sales amount]         )        ,(           [customer].[customer geography].[country].&[united states]          ,[measures].[internet sales amount]         )       }     )