How to add all sites to a BI report

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

1. Create your report, ensuring to include a 'Level # Name' field. 



2. We need to join to another view, Site Information, which will give us the sites 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: Site Information

Click Ok.



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

• Select the 'Level # Name' field from your master query (this will appear with the display name if you have renamed the field). 
• Click and drag the matching 'Level # Name' field from the site information fields to the Sub Query Field area. 
• Name the append.
• Click Save.

8. Voila! 
Your report will now show the sites where there was no data for your range. 

If you want to 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.