CODEWITHSUNDEEP
sqlreporting-servicesssasmdxdax
ramadongre
ramadongre

Reputation: 101

List cross join of two dimensions using MDX or DAX

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.

Expected format of results

Upvotes: 4

Views: 543

Answers (2)

ramadongre
ramadongre

Reputation: 101

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])
                )

enter image description here

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.

Upvotes: 1

MoazRub
MoazRub

Reputation: 2911

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 Edit

Upvotes: 2

Related Questions