Crystal doesn't show correct values

[Former Member]

Hello.

I'm using this sentence in my query to only count as 1 time when a value in a certain column repeats:

Case Row_Number() Over(Partition BY T0.U_CR ORDER BY T0.U_CR) When 1 then 1 else 0 End Conteo,

so for example, if the value in the column U_CR repeats three times, this will create another column named Conteo and in the first value it will show the value 1 and in the other 2 repeated values it will show 0, so it will only count it as 1. This works flawless in SQL Server, but when I move it to crystal it sometimes works good and sometimes don't, when I group some values in some groups it shows it fine but in some it just doesn't, any idea why's happening this?

Thanks in advance!

[Former Member]

Just a guess but could Crystal be pushing your grouping criteria back to the server, and in effect issuing a group by statement. Ihis grouping could split repeating values of U_CR into separate groups. Options -> Database -> Advanced Options has a check box to perform grouping on server. Try turning this off.

[Former Member]

It was already off didn't work turning it on though, any other idea?

Regards.

[Former Member]

Not really.

Perhaps it is the opposite of what I said. Maybe Crystal decides to do a sort to support your grouping and then intercepts your clause and runs it on the grouped results. In that case pushing the grouping back to the server might fix the issue.

I'd also look at the "when reading records" and "when printing" functions. Your code might be making multiple passes of the data to achieve the grouping and so get different results depending on which pass it does your function.