Need Help on Null Values.....

[system]

hi everyone,

I have a report where i display the “Count” of submissions done everyday. So what i have done is, i have created a group with options “for each day”.
(ie) Sample Data,
Date---------Submission
1-Mar-05--------------4
1-Mar-05--------------5
1-Mar-05--------------2
2-Mar-05--------------1
2-Mar-05--------------4
2-Mar-05--------------15
3-Mar-05--------------1
3-Mar-05--------------21
3-Mar-05--------------2
4-Mar-05--------------21
4-Mar-05--------------6
4-Mar-05--------------7
6-Mar-05--------------2
7-Mar-05--------------3
7-Mar-05--------------26
9-Mar-05--------------5
9-Mar-05--------------12

The report that i get is
Date-------Count of Submission
1-Mar-05--------------11
2-Mar-05--------------20
3-Mar-05--------------24
4-Mar-05--------------34
6-Mar-05--------------2
7-Mar-05--------------29
9-Mar-05--------------17

as u c there r no submissions on 5th & 8th mar, i want that a report in such a way that shows there s no submission on 5th & 8th.
(ie) i want the report as below,

Date-----------Count of Submission
1-Mar-05-----------------11
2-Mar-05-----------------20
3-Mar-05-----------------24
4-Mar-05-----------------34
5-Mar-05--------------0
6-Mar-05-----------------2
7-Mar-05-----------------29
8-Mar-05--------------0
9-Mar-05-----------------17

Pls hlp me on this. The fields in the database is inserted when a user makes a submission, so if a user doesn’t make any submissions, then no rows r inserted in the DB.

thanks in advance

– ash

---

nvlashok (BOB member since 2005-10-20)

[system]

Hi Ashok,

You can find lots of posts regarding this issue. But here is what you can do. You have to create additional data provider, say Excel file and having a date column. It should contain all the dates. Now you can link both the data providers by linking the dates. Now use this date( from 2ns data provider) to group you values. It will display empty when there is no data and you can just format the cell to display “0” when empty.

hope this helps.

---

Raja Saripalli (BOB member since 2005-12-14)

[system]

hi raj,

thanks for the info. The DB that i use is Oracle. As per the requirement, i am not allowed to go for another data provider.

can u give me some URL’s where u said i can find posts regarding this.

– ash

---

nvlashok (BOB member since 2005-10-20)

[system]

Hi Ashok,

Try searching displaying Null values…here are some psots…

How to display even blank data

Hi All, I have a strange issue, I have two dimensions and one measure, and I want all dimension values to be displayed even though if there is no measure associated with it. Example: Current Report ========== Uk (dimention1)| ---------- Reading (dimention2)| 100 (measure) Requirement ========== Uk (dimention1)| ---------- Reading (dimention2)| 100 (measure) London (dimention2)| null Many thanks, Uma

Multiple data providers to return NULL values

I need a report that will produce NULL values when there is no activity for a given date period and category. I've created data provider for the date range and that works when adding the metric. Meaning if there was no activity for Jan-06, the result is showin zero on the report as I've used the if ISNULL function for the metric. I've created a third data provider for the distinct categories. However, when I drag the object into the report, only those categories with activity are showing. T…

As per the requirement, i am not allowed to go for another data provider.

So does your requirement even restrict to use multiple data providers?

---

Raja Saripalli (BOB member since 2005-12-14)

[system]

Hi

I’ve finally got my 2 headers to display by doing it the 2 data providers outer join way.

I have table A containing recordids I want to do a count on and a column which links to table B (col is called n)and table B contains the column (called x) I want to group on even if there are no matching records in table A (eg the count is 0) (the master table)

the sql is

select count(A.recordid), B.x
from
A, B
where A.n(+) = B.x
group by B.x

Basically I want my report to say

Group Header(B.x) distinct count of Recordid by Group
Community Call 108
Non Community Call 0

When I do this in Crystal Reports, it is showing as a count of 1 instead of 0- it is counting the rows instead of the values (eg due to the outer join all that is in the 109th row is B.x containing ‘Non Community Call’ and the recoridid is null).

If I run the sql I have written in sqlplus, it is correct!

How can I get 0 to display where there are no recordids against the group ‘Non Community Call’ (B.x)?

Any help is appreciated!

Thanks!
Maria

---

CanNeverThinkOfAName (BOB member since 2007-06-13)