Daily Counts

[sandiclaus]

Hi,
I have scoured the forum and tried several things I found but have not been able to acheive what I need to do.

We are using CR Profession Ver 10.0. The database is an AS400 database and I am linking via ODBC.

I have a table with JuvNo, DetStartDt, DetEndDt, PODNo (not actual fieldnames; just easier to understand).

I need to create a report with a count by POD for each day within a parameter period (?startdate, ?enddate).
I need to have the dates by row and the pods by column.

I would appreciate any help!

 

[lbass]

Insert a crosstab and use the datefield as the row and POD as the column, with a summary of count of POD.

Your record selection formula should be:

{table.date} in {?start} to {?End}

-LB

 

[sandiclaus]

Appreciate the suggestion, but don't have a table with a record/date for each date the juvenile is in detention; just a start and end date, so I don't beleive this will work.
Sandi

 

[lbass]

You are right, it won't. Do you need to show all days within the parameter period, or only those where records exist? Please also explain what a POD is--I'm having trouble conceptualizing this.

-LB

 

[sandiclaus]

A POD is a section of rooms/cells. Yes, I do need to show a count for every day within the parameter period. That is what makes this so difficult.
Sandi

 

[lbass]

Do you have the ability to create a table containing all dates?

-LB

 

[sandiclaus]

Since the user can specify any period, that would be very difficult.
Sandi

 

[lbass]

Meaning what? What is "any" period?

-LB

 

[sandiclaus]

For example 7/1/2005 through today (3/2/2005)
S

 

[lbass]

Please try to answer more specifically. Can you or can't you create your own table? See SynapseVampire's FAQ on this topic: FAQ767-4532.

-LB

 

[IdoMillet]

Step 1: create a table "Dates" with one record for each possible date.

Step 2: join the Dates table to your table using a condition of:

Dates.MyDate >= YourTable.DetStartDt AND
Dates.MyDate <= YourTable.DetEndDt

Step 3: CrossTab, Chart or whatever on Change of Dates.MyDate and show a count of JuvNo (or any other non-Null field.

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[sandiclaus]

The following illustrates what I attempted, the results and what I was trying to get instead:

Parameters:
?startdate = 1/1/2006
?enddate = 3/1/2006

Formula Field: Days (I placed this in details section)

numbervar x:=1;
datevar thisdate := {?startdate};
datevar enddate := {?enddate};
while enddate - thisdate > 0
do
(
datevar array detdates;
redim preserve detdates[x];
thisdate := thisdate + 1;
if ({@DetStart}<= thisdate and{@DetEnd} = #12/30/1899#)
or ({@DetStart}<= thisdate and{@DetEnd}>= thisdate)
then
detdates[x]:= thisdate;
x+1;
);
detdates[x];


Results (used 1 juvenile for example):

(File No) (@Days) (DetStart) (DetEnd) (POD)
16586902 1/28/2006 1/28/2006 1/28/2006 0300
16586902 3/1/2006 2/27/2006 3/2/2006 1300

Desired results:

(File No) (@Days) (DetStart) (DetEnd) (POD)
16586902 1/28/2006 1/28/2006 1/28/2006 0300
16586902 2/27/2006 2/27/2006 3/2/2006 1300
16586902 2/28/2006 2/27/2006 3/2/2006 1300
16586902 3/1/2006 2/27/2006 3/2/2006 1300

Then I would do a crosstab on the Details. Is there any way to get this to work?

Thanks,
Sandi

 

[IdoMillet]

It seems to me that you are ignoring our attempts to help you.

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[sandiclaus]

LB and Ido,
I appreciate the suggestions about a table but that is not a practical solution for our system. The table would have to be in a separate database and maintained. Our goal is automation and user independence.
S