RND function Question

[bgul]

Hello - I'm using CR 8.5. I did a search and did not find anything on this issue.

Here's my problem. I need to do a distinct count on a column which features client web ids. Some clients though cannot get cookied and feature 0 as a web id. The person requesting the report has asked that I count each zero id distinctly. So:

0
0
0
123
123
456
456
789

should yield 6 unique ids when counted up via this method.

My proposed solution was to come up with a formula in this manner:

Distinct Counting the following formula:

if {newsLetterTracking.id} = 0 then Rnd else {newsLetterTracking.id}

My problem is that a large majority of the results are duplicated for ids = 0. So the resulting formula output(prior to distinct counting) for the above may look something like this :

0.234
0.234
0.567
123
123
456
456
789

Any ideas on what the issue may be or how to solve it? I have tried seeding with no success, and I have found something on the Crystal Decisions Knowledge Base (

http://support.crystaldecisions.com/library/kbase/articles/c2012617.asp)

but the rate of duplication on mine far exceeds what is discussed in that particular article - I have each "random" number repeating itself 3-5 times for a sample set of 600 or so zeroes.

 

[synapsevampire]

A quick fix might be to bring back a datetimestamp by using a SQL Expression, inclusive of milliseconds, then you could use a rnd*the sql expression, which should supply uniqueness.

An option might be to use a Guid, then you can substitute the guid when a value is 0, which will definitely supply uniqueness.

At any rate, supply the database type and version and someone can work out a means.

-k

 

[bgul]

I am connecting to SQL Server 2000 - apologies for not mentioning this before. Thanks for the suggestions - the timestamp suggestion did not work (I believe b/c of a lack of reseeding/too quick a response time) but I used the NEWID() function encapsulated in a CASE statement and that appears to be working fine now.

Here's the final solution as a SQL Expression:

CASE
WHEN CAST(newsLetterTracking.id as varchar)= '0' THEN convert(varchar(255),newid())
ELSE CAST(newsLetterTracking.id as varchar)
END

Thanks again!

Baris

 

[synapsevampire]

Good approach, I would think that the milliseconds from the getdate() would have worked, but admittedly I didn't test it.

A guid is only 32 characters, so you might save some processing time and space by using:

CASE
WHEN newsLetterTracking.id = 0 THEN convert(varchar(32),newid())
ELSE CAST(newsLetterTracking.id as varchar)
END

Glad that you worked it out.

-k