Fill empty cells in pivot table with 0

[system]

I have a data provider like this

branch year month value
HQ 2006 12 1000
HQ 2006 11 9000
HQ 2006 06 8000
HQ 2006 01 9000

I create a pivot table: year, month as columns, branch as row, value in body. It shows only values, where month and year matches.
How could the pivot table also show a zero where no combination exists ?

Any help is greatly appreciated,

Martin

PS: Using isEmpty(value) doesn’t seem to do the trick :?

---

mb_prof (BOB member since 2005-11-25)

[system]

Hi Martin,

this Reporter’s FAQ should answer your question

And I think there are some other solutions that can be found in BOB’s search

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Also look at this link

no data to fetch

I have a report that I am trying to get information for. Sometimes the report returns data and sometimes it returns the message no data to fetch. We still need a report to show all zeros even if there is no data in the sales fields. There is customer ID's and we need the report to at least return the customer ID and zeros for the sales field. I know updating the table with "0"'s would be ideal but that is not an option at this point. Is there a way to query the table and return the customer…

---

sujit_patange (BOB member since 2005-07-19)

[system]

@marekchladny

Hi Martin,

this Reporter’s FAQ should answer your question

And I think there are some other solutions that can be found in BOB’s search

I was hoping to find another solution as for creating a second DP

---

mb_prof (BOB member since 2005-11-25)

[system]

I take it you mean crosstab. Pivot Table is MS Excel terminology.

One way of doing it is to create the crosstab, and overwrite the formula in the body with just 0. Then you can apply an alerter that says if Measure > 0 then display the variable Measure. And if Measure < 0 Then display the variable Measure. Refer attached example.
eg.rep (40.0 KB)

---

jac (BOB member since 2005-05-10)