Displaying a count of NULL values in crosstabs..

[mrees]

Hello all,

Using CR 9 and CE 9 with SQL2k database.

I have a report with a crosstab that has my client ID for the rows, and counts the number of accounts they each have. It was requested that the crosstabs also shows any clients that don't have any accounts. So basically the SQL table is made up like:

Client AccNo
ABC 123
ABC 456
ABC 789
EFG 987
EFG 654
HIJ NULL

Creating a CrossTab like

No. Accounts
ABC 3
EFG 2
HIJ 0

This works and looks fine in Crystal Reports, but when you come to view it in CE, there is just an empty space for the 0 values. So I get

No. Accounts
ABC 3
EFG 2
HIJ

I assume that this is because I'm trying to count NULL values. Is there a quick way to fix this other than create a formula at my account level to count in the crosstab?

Thanks in advance

mrees

 

[synapsevampire]

you might use something like:

if isnull(accno) then
0
else
1

Then do a sum on that formula.

-k

 

[mrees]

k,

Thanks for that, I thought it would be the case, its just that due to my lazy development method, i'm going to have to change around 20 reports.

Never mind, serves me right for not testing one first.

M