Using DBCalc to get a count of distinct values in a group
I have a report in which I have generated a list of customers and
accounts. There are not going to be duplicate accounts in the listing,
but there may very well be duplicate customers. In the group footer
(and summary), I want to give a count of the number of accounts. This
is easy, simply use a dbcalc to give the count on the Account Number
field. I also want to give a count on the number of customers. When I
use a dbcalc to "count" the Customer Number field, I get the same count
as with the accounts. This makes sense, since each row in the dataset
has both an account number and a customer number. What I want, however,
is a distinct count on the customer number. So that if I have 5
accounts belonging to 3 customers, I will get counts of 5 and 3, instead
of 5 and 5.
Is there a way to use a dbcalc to do this, or is there some other
component I can use?
Note: The rows in the data set are not ordered by customer number and
then account number. Due to the nature of the report I have to order
the rows on another (date) field.
accounts. There are not going to be duplicate accounts in the listing,
but there may very well be duplicate customers. In the group footer
(and summary), I want to give a count of the number of accounts. This
is easy, simply use a dbcalc to give the count on the Account Number
field. I also want to give a count on the number of customers. When I
use a dbcalc to "count" the Customer Number field, I get the same count
as with the accounts. This makes sense, since each row in the dataset
has both an account number and a customer number. What I want, however,
is a distinct count on the customer number. So that if I have 5
accounts belonging to 3 customers, I will get counts of 5 and 3, instead
of 5 and 5.
Is there a way to use a dbcalc to do this, or is there some other
component I can use?
Note: The rows in the data set are not ordered by customer number and
then account number. Due to the nature of the report I have to order
the rows on another (date) field.
This discussion has been closed.
Comments
This is not a built-in feature of ReportBuilder. It may be possible to
manually count the distinct customer numbers however it may be a bit tricky
if you are not sorting by the custom number itself. This would need to be
done in the OnCalc of a TppVariable if you would like to do it inside
ReportBuilder.
The easiest option would be to create another query that calculates the
unique count and access that value directly from the report.
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Thank you for the reply. I was thinking that would the case, but I
wanted to check with someone else before abandoning it all together. I
had thought about a variable, but as you mentioned, it is difficult
since the rows are not sorted by customer number. I guess the separate
query option may be all I can do if I want to have that count on the
report.
Thanks again,
Richard Pigford