I'm using a pivot table against a database view that uses a left join. As intended I get records from the parent table even if not matched in the join. When I create a pivot table report for this view, the field I'm summing on shows empty cells instead of zeros.
I would like the pivot report itself to fill in these empty sums with zeros. Or if not possible, apply the equivalent of an 'isnull()' mechanism on the empty numeric fields due to the left join. To reiterate, I do not want to filter out the missing record values, I want the resulting displayed sum to show as zeros instead of empty.
The screenshot that is showing zeros is to show what I'd like to achieve, but it's an artificial sample because I'm inserting dummy data into the joined table just to convey what I'm looking to achieve. Inserting artificial records won't be an option in real life.
The screenshot with the yellow highlights shows the empty (missing because of left join) cells I would like to display as zeros.
Seems a fairly basic reporting task. Any suggestions on how this might be accomplished? Thanks in advance for any advice.
