Max Value / Distinct Count to Determine Group

[IceRuby]

Crystal XI to Access 2007 database

I have the following data

ID VAR DateTimeStamp
90009 G8 24/5/09 00:00:00
90009 G8 24/5/09 11:00:00
90009 G8 24/5/09 12:00:00
90009 G99 24/5/09 13:00:00
90009 G99 24/5/09 14:00:00
90009 G99 24/5/09 14:30:00
90009 G99 24/5/09 14:50:00

(the datetimestamp is cast from a string datetimestamp) There isnt any other date field to extract.

I need to create a report with a distinct VAR count. Easily done via group on VAR this resulting in

VAR DistinctCount
G8 3
G99 4

however...this is seen as a duplicate. The requirement is for 1 VAR category and distinct count. To calculate this I plan to calculate maximum datetimestamp per ID, & return VAR value & create a distinct count for each VAR.

I have tried creating GRP#1 - ID, GRP#VAR and applying @maxdatetime however the distinct count only provides count per VAR per ID. I need per VAR distinct totals.

Also tried creating a group on @maxdate.... CR doesnt want to know about

Lastly, just with GRP#1 - ID, I tried creating a running total on STRVAR10.
i.e. Field to Summarize STRVAR10 = DistinctCount,
Evaluate formula - Maximum ({LOG.TIME_STAMP}, {LOG.ID})
Reset - Never
This doesnt provide any result.

Thoughts? Frustrated, as this should be simple.

Thanks

 

[HowardHammerman]

Why not a running total in which you accumulate once per group?

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[IceRuby]

The result of that will be

ID VAR DateTimeStamp
90009 G8 24/5/09 00:00:00
90009 G8 24/5/09 11:00:00
90009 G8 24/5/09 12:00:00
90009 G99 24/5/09 13:00:00
90009 G99 24/5/09 14:00:00
90009 G99 24/5/09 14:30:00
90009 G99 24/5/09 14:50:00

ID VAR DistinctCount DateTimeStamp (maxtimeperid)
9009 G8 3 24/5/09 12:00:00
9009 G99 4 24/5/09 14:50:00

I need Maxdatetimestamp per ID then return VAR

e.g
ID VAR DistinctCount DateTimeStamp (maxtimeperid)
9009 G99 4 24/5/09 14:50:00



I need

 

[HowardHammerman]

Group on VAR.
Then create a running total using the Maximum type summary and then set the accumulation to "once per group" where the group is the one based on VAR.

Reset on a higher group or none at all (never).


Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[IceRuby]

Thanks but the issue with that that is I need the maximum(datetimestamp,ID) before I can evaluate to get distinct count.

New day and clearer head. Whilst the evaluation criteria is maximum(datetimestamp,ID)forcing me to create
#GRP1 ID
#GRP2 VAR

Regardless of what I do the results will always be these groups
ID VAR DistinctCount
e.g. 9009 G99 4
9008 G97 5
9010 G99 10
9012 G97 8

Where need Total per VAR (e.g. G14)

The only way to evaluate and via ID and then group by VAR (not including ID) I know of is to create a separate table with maximum(datetimestamp,ID) link on ID and then create simple report grouping via VAR.

Thanks

 

[HowardHammerman]

Creating a table is certainly one way. The problem is that it is a two step procedure and if it has to be done under program control it can get very messy.

I am not certain that I still understand the problem but a sub-report might do the the trick.

One way to use the sub-report is to place the sub-report in the report header and return an array via a shared variable. There are limitations to the size. However, if the number of groups is not too large, the array could hold the maximum date-times that you want for each group.

I am sorry I could not be more helpful.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[lbass]

Why not convert the datetime to a date? Then you can insert a crosstab where you use var as the row, and distinctcount of {@date} as the summary field. (If you are trying to count the var only once per date.)

-LB