Need to display Zero for months that does not have data

I’m working on a status summary report that is going against a Oracle 9i database. I have a database that has no values for certain months, how do I display “0” for the months that do not have values? The report is prompted on MMMM/YYYY. If the users enters July, 2003, then the report should display months Jan- July with values of zero if there is no data.

I’d appreciate any help. Below is an example of what the report should look like:

Status Summary
December, 2003

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
135 179 125 119 104 92 85 80 78 76 73 74
33 1 1 0 0 0 1 0 1 2 1 6
0 0 0 0 0 0 0 0 0 0 0 0

My Results are showing the following. I’d like to display “Zero”
values for March, April and May

Status Summary
July, 2003

Jan Feb Jun Jul

135 179 92 85
33 1 0 1
0 0 0 0

July, 2003


nristovs (BOB member since 2004-07-23)

First, welcome to BOB. We hope you will find it a valuable resource.

Second, you remember that bright yellow message on the post screen? It suggested that you search first before posting, because it’s possible your question has been discussed before. In this case, it is definitely true … one of the most asked questions, actually. Try a search for “missing month*” or “dropped month*” or “all month*” or “month* without data” or similar. You’ll find some creative solutions if memory serves me correctly.

Again, welcome.


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

you make a first DP on a query file having the 365 days od the years and you make it “no refesh” and “no edit”

than you make a second DP on yours datas and the two queries beeing linked on the month or day, Bo will force the display of all month…

Bernard


bernard timbal :fr: (BOB member since 2003-05-26)

Hi,
What a simple and easy suggestion!
However, it would only work for the full client. Could you suggest something that might work for WebI?
Thanks,
BatCat


BatCat :uk: (BOB member since 2004-05-18)

Change the underlying data and populate your tables with dummy rows for months with no data, for example:

Month         Revenue
August        0.00

Or change your universe to use Outer Joins between your Calendar table and your fact table.

You will find quite some threads if you follow Dwayne’s search suggestions :mrgreen:


Andreas :de: (BOB member since 2002-06-20)

Hi Andreas!

Ahhh must try and see if I can talk them into that one

We have tried numerous ways to do this, but because of the structure of their database and the queries they want it is impossible to set up in a way that works in WebI.

I have already followed Dwayne’s suggestions thank you very much.
Thanks,
BatCat


BatCat :uk: (BOB member since 2004-05-18)

The first DB not necessary has to be done on a query FILE; you can alternatively create a table with 365 days of the year…

So you are not linked to the phisical file on your client.


Christian Konrads :it: (BOB member since 2004-07-21)

The problem is that WEBI currently does not support multiple data providers, so it all has to be handled in one query, or a UNION of queries. Second… is that even the next generation of WebI will not LINK data Providers, which is required in this instance.

It may be poissble to build this in full client and have it refreshed in WebI, or use ZABO, but there is no straight WebI solution without the use of a calendar table or a datamart.


digpen :us: (BOB member since 2002-08-15)