display the column with no data to zero.

[system]

Hi,

I have report requirement to display - department, regions and their sales.
This my report output.
D1------R1-------------100
D1------R2-------------200
D1----- R3-------------300
D1------R4-------------400
D2------R1-------------100
D2------R2-------------200
D3------R1-------------200

But the user wants to display all the reion for the department and show zero when there is no sales.

Like

D1------R1-------------100
D1------R2-------------200
D1----- R3-------------300
D1------R4-------------400
D2------R1-------------100
D2------R2-------------200
D2----- R3-------------0
D2------R4-------------0
D3------R1-------------200
D3----- R2-------------0
D3------R3-------------0
D3------R4-------------0

The region are fixed and department can increase or decrease in future.

Any suggestion to do in report level will be great.

Thanks in advance

---

ThanksForHelp (BOB member since 2006-09-25)

[system]

Hi,

This Reporter’s FAQ may help:

FAQ: Reporter

How can I include data for all months in a crosstab, even if a particular month has no data? One way to do this is to create a second data provider using only the months. Then link (or “merge”) the second data provider (query) with the first data provider. Use the month column from the second data provider in the cross tab. This should bring in all months even if there were no data for that month. However this technique does not work if you have sections in your report. Dave Rathb…

---

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

[system]

Try this formula for SALES:
If (IsNull([SALES]);“0”;[SALES])

---

rktarapore (BOB member since 2006-11-14)

[system]

In universe you have to edit the relationships…you must change “=” to “*=”.

Ex:

Dim.RegionID = Fact.RegionID to Dim.RegionID *= Fact.RegionID

Dim.DepartmentID = Fact.DepartmentID to Dim.DepartmentID *= Fact.DepartmentID

---

bsb.mano (BOB member since 2009-11-17)

[system]

Thanks for all the feedbacks.

I created another query with just dimension and used that in the report.

I don’t have access to universe changes. so would not try.But,the formula didn’t work.

Thanks again.

---

ThanksForHelp (BOB member since 2006-09-25)

[system]

Can you access custom sql report?

There you can change the relationships.

---

bsb.mano (BOB member since 2009-11-17)