Distinct Count in Crystal 6.0 1

[Kmatlock]

I'm trying to get a distinct count of production work orders for our second quarter and getting mixed results.  I'm get different results when I do a distinct count for the whole period compared to doing a distinct count for each month and adding them up.  We had the same problem on another report a couple of months back.  Any ideas?

 

[MalcolmW]

Don't use the distinct count - it is flaky.&nbsp;&nbsp;It works in v8.<br>Use a formula to find distinct work orders, or do your distinct count in SQL and pass the result. <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[Kmatlock]

Thanks for the tip Malcolm.&nbsp;&nbsp;Do you have an example that might work?

 

[MalcolmW]

Are you using SQL or a local database? <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[Kmatlock]

We are using Btrieve

 

[MalcolmW]

Creating running totals using a formula:<br>If you have suppressed data, or your data is based on a formula that occurs WhilePrintingRecords, you should create a running total formula rather than using the Running Total Select Expert.<br><br>When you create a running total manually, you need to create three formulas:<br><br>a summary formula <br>a reset formula to set a variable to zero <br>a display formula to display the variable. <br><br>The reset formula typically goes in a group header, and is typically formatted to suppress printing.<br>The display formula typically goes in the corresponding group footer.<br>The summary formula calculates the value, depending on the criteria.&nbsp;&nbsp;Usually this formula is suppressed as well.<br><br>Calculating a distinct count for work orders is easiest if the data is grouped by work order.&nbsp;&nbsp;Then, just put the summary formula in the group header for work orders.&nbsp;&nbsp;<br>//SummaryFormula<br>NumberVar CountWorkOrdersMonth ;<br>NumberVar CountWorkOrdersTotal ;<br>CountWorkOrdersMonth := CountWorkOrdersMonth + 1 ;<br>CountWorkOrdersTotal := CountWorkOrdersTotal + 1 ;<br><br>//Reset formula in group header<br>NumberVar CountWorkOrdersMonth := 0 ;<br><br>//Display formula in group footer<br>WhilePrintingRecords ;<br>NumberVar CountWorkOrdersMonth ;<br><br>//Display formula in report summary<br>WhilePrintingRecords ;<br>NumberVar CountWorkOrdersTotal ;<br><br>Note that if you have the same work order in two months, it will be counted twice in the Total.&nbsp;&nbsp;If this is a problem, you will have to use a subreport placed in the report footer, that has an outside group of work orders to get the distinct count<br><br><br> <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[Kmatlock]

Thanks for all of the help Malcolm.&nbsp;&nbsp;I think this is going to work for me.

 

[MalcolmW]

It is amazing what Crystal Reports can do, despite how it tries to prevent you <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>