How to perform a cross join?

[psrpsrpsr]

Hi folks, I need to perform the equivalent of a CROSS JOIN in SQL in Report Studio 10.2.2. Here is what I have tried so far:

Query 1:
UserID    YearMonth   Count
01-123    201601        3
01-123    201604        5
01-123    201608        1

Query 2: generates a list of all months from 201601 to 201612 based off of a calendar query subject
YearMonth   
201601
201602
201603
... (and so on)
201612

DESIRED OUTPUT:
UserID    YearMonth   Count
01-123    201601        3
01-123    201602        0
01-123    201603        0
01-123    201604        5
01-123    201605        0
01-123    201606        0
01-123    201607        0
01-123    201608        1
01-123    201609        0
...through 201612

I have tried a full outer join (qry 1 0..1  = qry 2 0..1), and using COALESCE (qry 1.YearMonth, qry 2.YearMonth), but this yields null UserIDs.

I have tried using the 'allow cross product' setting, no change.

Any thoughts on how I can achieve this? Thanks!

[BigChris]

You'd expect the NULL userids for the missing months wouldn't you?

I imagine you'd need to first generate a query/table that looks like

YearMonth   UserID
201601          01-123
201602          01-123
201603          01-123
etc.

And then from there join up to your Query1.

[CognosPaul]

This is one of those cases where DMR shines. If you have this requirement in multiple places, you should consider adding a dimensional layer. This way you'll automatically get the crossjoins you need.

[hespora]

Hi folks, I need to perform the equivalent of a CROSS JOIN in SQL in Report Studio 10.2.2. Here is what I have tried so far:

Hi psr,

in both your queries, insert a data item "Join" with a static value (i always use '1'). Then, inner join on that data item.

[psrpsrpsr]

Thanks for the input all!