Using DBCalcs with fields that were computed in SQL
These are the circumstances.
1) My dataview contains a field created from an SQL Count of another field.
2) Sometimes the count field value is null because there are no records
satisfying the criteria
3) I am using a DBCalc to display a column total of all the counts in the
summary
The problem is that the report engine is resetting the value returned by the
DBCalc to 0 whenever it encounters a blank count rather than treating it as
zero. I need it to treat them as zeros so that I get a total of the whole
column rather than just the total of the values since the last blank value.
I realise this is probably a bit hard to imagine so here is an example of
what is happening.
Name Count of Categ 1 Count of Categ 2 Count of
Categ 3 Count of Categ 4
----------------------------------------------------------------------------
------------------------
Fred Bloggs 10 10
10
Joe Smith 10 10
10
Dave Harris 10
10 10
----------------------------------------------------------------------------
----------------------------
Totals 0 30
10 20
----------------------------------------------------------------------------
---------------------------
This is a vastly simplified version, I realise I could use variables for
each value in the detail and then acculmulate total values but this really
makes it complicated and hard for my users if they want to do reports
similar to this themselves. I think that the blanks should be treated as
zeros. The blank when zero setting has no effect at all.
TIA
Rhonda Ridge
1) My dataview contains a field created from an SQL Count of another field.
2) Sometimes the count field value is null because there are no records
satisfying the criteria
3) I am using a DBCalc to display a column total of all the counts in the
summary
The problem is that the report engine is resetting the value returned by the
DBCalc to 0 whenever it encounters a blank count rather than treating it as
zero. I need it to treat them as zeros so that I get a total of the whole
column rather than just the total of the values since the last blank value.
I realise this is probably a bit hard to imagine so here is an example of
what is happening.
Name Count of Categ 1 Count of Categ 2 Count of
Categ 3 Count of Categ 4
----------------------------------------------------------------------------
------------------------
Fred Bloggs 10 10
10
Joe Smith 10 10
10
Dave Harris 10
10 10
----------------------------------------------------------------------------
----------------------------
Totals 0 30
10 20
----------------------------------------------------------------------------
---------------------------
This is a vastly simplified version, I realise I could use variables for
each value in the detail and then acculmulate total values but this really
makes it complicated and hard for my users if they want to do reports
similar to this themselves. I think that the blanks should be treated as
zeros. The blank when zero setting has no effect at all.
TIA
Rhonda Ridge
This discussion has been closed.
Comments
groups? Is the group resetting the value? Can you test with RB 7.02 to see
if the problem goes away? Can you send a minimal working example using
DBDEMOS data to support@digital-metaphors.com and we'll research the issue.
Cheers,
Jim Bennett
Digital Metaphors
http://www.digital-metaphors.com
info@digital-metaphors.com
Not using groups.
Cannot test with 7.02 at the moment but am emailing an example.