How to add All Months to a Report

In the following example, I am creating a report to show the number of events for the last 12 months, including months where there are no events. 

1. Create your report, ensuring to include a 'Month Start Date' field and filtering to your required date range. 



2. To format the 'Start Month Date' format, click the drop-down arrow on the column and navigate to 'Format' and then click 'Edit'.

3. Change the display to your preference, change the 'Date Format' to 'Other' from the drop-down list and enter the 'Custom Date Format'. In this example, I have entered 'MMM yy' which represents the first 3 letters of the month and the last 2 numerals of the year. 

MMM : Jan, Feb, Mar
MMMM : January, February, March
mm : 01, 02, 03
yyyy : 2016, 2017, 2018
yy : 16, 17, 18

4. We need to join to another view, the Date Warehouse, which will give us the months we are missing. 
This involves the use of an append subquery. 
On the top left-hand corner of the screen, click the + button and change the following settings;

• Type: Append
• Style: Advanced
• Data Source: Vault Data
• View: Date Warehouse

Click Ok.



5. Change the Join type to 'Full Outer Join'. 

• Select the 'Month Start Date' field from your master query (this will appear with the display name if you have renamed the field). 
• Click and drag the 'Month Start Date' field from the date warehouse fields to the Sub Query Field area. 
• Click and drag the date field to the filters area.
• Name the append.
• Click Save.

6. Click the filters 'Advanced settings' 

7. Change the operand to 'Link to Filter'. 
Define the link to the Date filter used in the master query. 

8. Voila! 
Your report will now show the months where there was no data for your date range. 
If you add 0's where the data is null see below. 

Changing Nulls to Zeros

1. Click the drop-down arrow on your metric field you wish to convert.
Click 'Advanced Function'.

2. Type 'null' into the search field to bring up the 'Null to Zero' function.
Click 'Null to Zero' and click Save.

3. This will convert the null values to 0.