Distinct Count issue in Crystal Reports

Hi there,

I'm having a problem with a pretty easy formula which is suppose to just Distinct Count some ID's for a specific period of time:
Each parameter in the formula represents a field in the database, formula bellow should return 3 records... but it returns 7. If I take out the date conditions (so only DistinctCount remains) (7) records will be returned.

If {@.M_Startdate} <= {@.ClientRefDate} and {@.ClientRefDate} <= {@.M_EndDate} Then DistinctCount({@.UniqueRefID})

I also tried to put is in a different way (which express the same thing);
If {@.ClientRefDate} in [{@.M_Startdate} to {@.M_EndDate}] Then DistinctCount({@.UniqueRefID})

It looks like a date validation is performed when I reverse the <= with >=; Formula bellow returns (0) records, if I remove any date condition (7) records are returned.
If {@.M_Startdate} >= {@.ClientRefDate} and {@.ClientRefDate} <= {@.M_EndDate} Then DistinctCount({@.UniqueRefID})

Using WhilePrintingRecords; right at the top does not help I tried already. Also adding a date validation on the main query of the report does not help because whole data will be affected.
Any suggestions?

Thanks,

RobertHi Robert,
Firstly, I see a potential problem here, straight up. Evaluation order of the formulae. You will get inconsistent/unreliable evaluation in formulae that use other formulae if you don't use the EvaluateAfter statement.

If {@.M_Startdate} <= {@.ClientRefDate} and {@.ClientRefDate} <= {@.M_EndDate} Then DistinctCount({@.UniqueRefID})

try this
EvaluateAfter ({@.M_Startdate}
EvaluateAfter ({@.ClientRefDate})
EvaluateAfter ({@.M_EndDate})
EvaluateAfter ({@.UniqueRefID})

If {@.M_Startdate} <= {@.ClientRefDate} and {@.ClientRefDate} <= {@.M_EndDate} Then DistinctCount({@.UniqueRefID})

That way, you are forcing this formula to evaluate the others first.
This may be why the different statements return different results.

Secondly, you are using formulae to provide what I presume are references to data fields (like @.UniqueRefID). I presume there is a pressing reason to do it this way rather than just referencing the underlying data field itself? Going to the underlying data without using a formula is always going to be quicker, and more reliable, as it removes the formulae........

Dave|||Hi Robert,
Firstly, I see a potential problem here, straight up. Evaluation order of the formulae. You will get inconsistent/unreliable evaluation in formulae that use other formulae if you don't use the EvaluateAfter statement.

try this
EvaluateAfter ({@.M_Startdate}
EvaluateAfter ({@.ClientRefDate})
EvaluateAfter ({@.M_EndDate})
EvaluateAfter ({@.UniqueRefID})

If {@.M_Startdate} <= {@.ClientRefDate} and {@.ClientRefDate} <= {@.M_EndDate} Then DistinctCount({@.UniqueRefID})

That way, you are forcing this formula to evaluate the others first.
This may be why the different statements return different results.

Secondly, you are using formulae to provide what I presume are references to data fields (like @.UniqueRefID). I presume there is a pressing reason to do it this way rather than just referencing the underlying data field itself? Going to the underlying data without using a formula is always going to be quicker, and more reliable, as it removes the formulae........

Dave

Hi Dave,

Referencing fields from the database can be quicker sometimes but in this case I'm pressed to use parameters for different calculations. I tried qwith "direct" database fields, result is the same.

Using:
EvaluateAfter ({@.M_Startdate}
EvaluateAfter ({@.ClientRefDate})
EvaluateAfter ({@.M_EndDate})
EvaluateAfter ({@.UniqueRefID})

If {@.M_Startdate} <= {@.ClientRefDate} and {@.ClientRefDate} <= {@.M_EndDate} Then DistinctCount({@.UniqueRefID})
would have to work but unfortunately same result is returned.

Second pass functions require data from more than one record and in this case(WhilePrintingRecords) should evaluate params first... but it does not.

I'll keep trying,

Thanks a bunch,

Robert

Distinct count in case of Custom Rollup formula in a dimension very Urgent.........

how we can calculate the dictinct customer ID in case
of custom rollup formula
BrhanCreate a separate cube for the distinct count measure, and remove any custom
rollup formula in cube editor.
Ohjoo Kwon
"Burhan" <burhanuddin.ahmed@.gonetbpo.com> wrote in message
news:%233m4VeSMFHA.436@.TK2MSFTNGP09.phx.gbl...
> how we can calculate the dictinct customer ID in case
> of custom rollup formula
>
> Brhan
>
>

Display Result in Page Header

Hi!

I've got following problem: I got a formula evaluated whileprintingrecords in page footer. It writes in a global variable the highest number on the page. When I display this variable in page footer it works fine. But I want it to appear on the page header, too! When I display it in page header, it is the old value. Of course, because the variable is written on the end of page.

Is there a possibility to say the page header should be written AFTER the Page Footer? Or that the variable shall be displayed AFTER the page footer?
I tried the EvaluateAfter() function but it didn't work. It just writes the previous value on the top of following page.

I would be glad about some hints.

Greeting and thanks,
MarkusCongratulations, you've run into one of the most unpleasant "features" of Crystal Reports - inability to display running totals in group or page headers. In fact, there are workarounds for squeezing group-level running totals into group headers (by using external views or crosstabs), but for page-level totals, I think you are out of luck.

The reason is, as you correctly mentioned, that pages are formed and all running totals are calculated AND PRINTED during pass 2, but to put the running totals into headers, pass 3 would be required. There is, in fact, pass 3, but it will not calculate anything except page count.|||Thank you for your answer. Although it's not the one I wanted to hear... :-(

Perhaps there is a possibility to locate the field at an absolute position? So I can say: "Okay, you're evaluated in page footer, but displayed 2cm away from top of page"... But I think, this is not possible, too. I've seen that the position relates on section, therefore is no absolute one...

Sad, that CR is far behind freeware tools in this topic... Had one where you could set the evaluation and print time for each field...