How can I generate a list of dates between two dates?

[psrpsrpsr]

Hi folks, I have a requirement to show the average of a fact field for all 12 months in a particular year. However, the package structure only returns rows where they exist.

How can I generate a list of YearMonth (based off of a date field) so I can ensure that each UserId is returned 12 times, ensuring the proper denominator for the calculation?

Current output:
YearMonth     UserId   Count
201603              1234      12
201611              1234      4          >>> Average=8

Desired output:
YearMonth     UserId    Count
201601              1234       0
201602              1234       0
201603              1234       12
201604              1234       0
201605              1234       0
201606              1234       0
201607              1234       0
201608              1234       0
201609              1234       0
201610              1234       0
201611              1234       4         
201612              1234       0           >>> Average=1.33

Thanks!

[BigChris]

Looks like you might need a calendar table and a package that allows cross product joins.

[psrpsrpsr]

I don't have access to Framework Manager - is there a way to do this using the functionality of Report Studio alone? Thanks

[tjohnson3050]

You can do this with a SQL object:

with dates as (
select #prompt('StartDate','date','current_date')# as  DT from (values('all_level')) "Query1.0"(c1)
union all
select _add_days(DT, 1) as DT from dates where DT < #prompt('EndDate','date','current_date')# )

select DT from dates

[psrpsrpsr]

Interesting - I have not worked with SQL objects yet but I will give this a shot! Thanks for your help folks

[tjohnson3050]

The SQL object will give you a single date field.  In the query subject you can create your yearmonth data item.

Then join that with your fact table and make sure to have an outer join on the fact table side of the join (0..n).