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
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.
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
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
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.