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!
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.
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.
Quote from: psrpsrpsr on 23 Feb 2017 08:50:49 AM
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.
Thanks for the input all!