List cross join of two dimensions using MDX or DAX

Question

I have a SSRS report which uses SSAS cube as source and report itself is working fine which includes two parameters as cascaded drop-downs and have multiple values in them. Now i would like to setup a data-driven subscription in report-server where i am facing issue with regard to providing cross join of two dimensions which are supposed to feed into report parameters as underlying data-source.

A TSQL equivalent of expected output is:

Select programyear,AssignCounty, convert(varchar(10), tb1.year) + '_' + 
tb2.county 'FileName'  from
(select '[Date].[Program Year].&[' + Convert(varchar(10), [Program Year]) 
+ ']' 'ProgramYear', [Program Year] 'year'
From d_mcaa_date) tb1,
(select '[Plan].[Auto Assignment County].&[' + Convert(varchar(10), [Auto 
Assignment County]) + ']' 'AssignCounty', [Auto Assignment County] 
'county' From d_mcaa_plan) tb2

I am expecting to feed below report parameters as: ProgramYear: [Date].[Program Year].&[13] County: [Plan].[Auto Assignment County].&[Sacramento]

Note that Date and Plan are my dimensions.

I am looking for a MDX/DAX code where i can list as two columns with all values of "Program Year" from date dimension and "Auto Assignment County" from Plan dimension side by side as a cross join. This would formulate a data-set, which in-turn can feed into SSRS report parameters when you setup a subscription.

I did try below code:

select {} on 0, 
{
[Date].[Program Year].Children  
*
[Plan].[Auto Assignment County].Children
}  on 1
from [Model]

But this lacks columns name and subscription did not accept this code.

Here is the image of expected result, although query is TSQL not the expected MDX/DAX.

MoazRub · Answer 1 · 2019-05-02T11:11:22.977

2

I am not sure I understand your problem completely, but to get a cross join you can use the following code

select non empty
{[Date].[Program Year].Children,[Plan].[Auto Assignment County].Children} 
on 0, 
from [Model]

No need to use the row axis, since all you want is a cross join

Edit: Based on image

with 
member measures.t 
as
[Date].[Program Year].currentmember.name + '_'+ [Plan].[Auto Assignment County].currentmember.name

select measures.t on 0,
non empty
([Date].[Program Year].Children,[Plan].[Auto Assignment County].Children)
on 1, 
from [Model]

Edit Based on the comment

with 
member measures.t
as
[Date].[Calendar Year].currentmember.name + '_'+ [Product].[Category].currentmember.name

member measures.t1
as
[Date].[Calendar Year].currentmember.unique_name + '_'+ [Product].[Category].currentmember.unique_name

member measures.t3
as
[Date].[Calendar Year].currentmember.unique_name

member measures.t4
as
[Product].[Category].currentmember.unique_name

select {measures.t3,measures.t4,measures.t,measures.t1}
 on 0,
non empty
([Date].[Calendar Year].Children,[Product].[Category].Children)
on 1
from [Adventure Works]

Result

edited May 02 '19 at 11:11

answered May 01 '19 at 21:41

MoazRub

2,881
2
10
20

1

Thanks! But this seems to be producing results row-wise vs expected columns. And also the query would only allow me to have one element, either year/county to be part of it, but not both. – ramadongre May 01 '19 at 21:47
1

@ramadongre can you share a sample Excel or screenshot of how data should look. – MoazRub May 01 '19 at 22:10
1

@ramadongre i have changed the query – MoazRub May 01 '19 at 22:49
1

Thank you @MoazRub; but note that, a) Query lacks named columns for first two b) cells uniquename including dimension hierarchy is missing (this is needed for the "in" check within SSRS report query (Ex: [Date].[Program Year].&[13]). – ramadongre May 01 '19 at 23:08
2

@ramadongre take a look at the edit with the result attached. .name is just one of the properties. The last example show .uniquename – MoazRub May 02 '19 at 11:12
1

Due to the original intent of the question, i will mark this as accepted answer, although SSRS subscription did not accept this query. – ramadongre May 02 '19 at 16:27

ramadongre · Accepted Answer · 2019-05-03T17:39:52.563

Finally i found that either it is not possible to use MDX to achieve what i want or at least it is easy to to do it in DAX. The below query was accepted as valid query for data-driven-subscription in SSRS:

Evaluate    
    ADDCOLUMNS(
        CROSSJOIN(
                DISTINCT('Plan'[Auto Assignment County]),DISTINCT('Date'[Program 
Year] )
                )
                ,
                "ProgramYeare", CONCATENATE(CONCATENATE("[Date].[Program Year].& 
        [",'Date'[Program Year]),"]"),
                "AssignCounty", CONCATENATE(CONCATENATE("[Plan].[Auto Assignment 
     County].&[",'Plan'[Auto Assignment County]),"]"),
                "FileName", CONCATENATE('Plan'[Auto Assignment County],'Date'[Program 
       Year])
                )

I do have to acknowledge that this was question was beyond about MDX itself but rather workable solution using either MDX/DAX. I have updated question to be so.

List cross join of two dimensions using MDX or DAX

2 Answers2