Besides a crosstab...

[jbarbato]

Hi there! I am using crystal xi against oracle 10g db.

Each task is assigned to a location & level.
Each task has several hundred dates - the dates are the same for one task as they are for another.
For each task at each date, 2 measurements are taken. These are taken cumulatively, so the amount of work done from one period to the next can be found by subtracting the previous measurement from the current measurement. The very last date shows how much work has been done so far.

The data looks something like this...

Task = 100, Level = AAA, Location = Cleveland
Date = 2/12/05, Meas1 = 50, Meas2 = 110
Date = 2/17/05, Meas1 = 60, Meas2 = 140
Date = 2/19/05, Meas1 = 75, Meas2 = 170
Date = 2/26/05, Meas1 = 80, Meas2 = 210
Date = 2/28/05, Meas1 = 80, Meas2 = 250
Date = 3/05/05, Meas1 = 90, Meas2 = 280
Date = 3/10/05, Meas1 = 110, Meas2 = 290
Date = 3/15/05, Meas1 = 120, Meas2 = 300
Date = 3/20/05, Meas1 = 135, Meas2 = 350
Date = 3/30/05, Meas1 = 150, Meas2 = 380

Task = 102, Level = AAA, Location = Cleveland
Date = 2/12/05, Meas1 = 0, Meas2 = 100
Date = 2/17/05, Meas1 = 30, Meas2 = 110
Date = 2/19/05, Meas1 = 40, Meas2 = 120
Date = 2/26/05, Meas1 = 60, Meas2 = 130
Date = 2/28/05, Meas1 = 60, Meas2 = 140
Date = 3/05/05, Meas1 = 60, Meas2 = 150
Date = 3/10/05, Meas1 = 70, Meas2 = 160
Date = 3/15/05, Meas1 = 90, Meas2 = 160
Date = 3/20/05, Meas1 = 100, Meas2 = 160
Date = 3/30/05, Meas1 = 100, Meas2 = 175

The user will choose a date. then, that date along with the next 3 will come back for a total of 4 date periods. If the user choses 2/19/05, I would like the report to look like this...


Location: Cleveland
Level: AAA

Start Increase By Period Period End Last Value
2/19 2/26 2/28 3/05 3/05 3/30

Task 100
Meas1 75 5 0 10 90 150
Meas2 170 40 40 30 280 380

Task 102
Meas1 40 20 0 0 60 100
Meas2 120 10 10 10 150 175


I tried to do this with a crosstab, but found it a bit too limiting for my needs...

I can handle the grouping no problem - just not so sure how to get my data into a record like this... any pointers in the right direction would be greatly appreciated - thanks!

- jayme

 

[synapsevampire]

OK, I guess date period means a week... How does that last date of 3/30 fit into this? It's not within the 4 weeks specified... Are we to assume that it's the very last measurement in the database, or?

In the Report->Edit Selection Formula->Record use:

(
{table.date} >= {?MyDate}
and
{table.date} < {?MyDate}+28
)

Create a formula such as the following for the group header:

whileprintingrecords;
numbervar wk1:=0;
numbervar wk2:=0;
numbervar wk3:=0;
numbervar wk4:=0;

In the details use:
whileprintingrecords;
numbervar wk1;
numbervar wk2;
numbervar wk3;
numbervar wk4;
if {table.date} = {?MyDate} then
wk1:= {table.measur2}
else
if {table.date} = {?MyDate}+7 then
wk2:= {table.measur2}
else
...you get the idea...

The tricky one is the last measurement.

You might grab this with a subreport in the report header using sql of:

select maximum({table.measur2},{table.date}) from table.

Anyway, this should get you going.

-k

 

[jbarbato]

I think this is getting at what I need...

Except that a date can be anything the engineer sets it too - could be every three days, every week, every other week, or completely random if they feel like it.

For the very last date, I am just running another command statement that uses an analytical function and linking it to the command that grabs the user entered date with corresponding measurements and the next three. Its how to get the three dates in between as variables that I need help with