Question
When building a report I noticed some data is missing. How can I include 0 values in Explore reports?
Answer
There are a few ways to include 0 values in reports. The most popular are listed below. Use the one that suits your report configuration.
Time-based reports
If a report is time-based and there is no data displayed for NULL values of time attribute dimensions, like dates, weeks, or months, you can use the Time axis result manipulation.
To enable the Time axis
- In report builder, click Result Manipulation ().
- Select Time axis from the list.
- Click Use Time axis checkbox; and then click Apply.
Reports with attributes on columns and rows
When a report has attributes on both columns and rows it sometimes has null values. This is especially visible in tables because some of the cells are empty. Use the Result metric calculation to replace these empty cells with 0 values.
For example, your report looks at the number of tickets created by year and group. It has Tickets in metrics, Ticket created - Year in columns, Ticket group in rows, and some cells are empty.
To create the result metric calculation
- In report builder, click Result Manipulation ().
- Select Result metric calculation from the list, then click Add a new metric.
- Enter a name for your result metric calculation.
- In the Formula box, from the Insert menu select the metric you need. In this example, it is COUNT(Tickets), then add "+0" to the formula. Your final formula will be:
COUNT(Tickets)+0
- When you are finished, click Clear used metric checkbox and then +Add.