organizing records in the same field

[jj1576]

Hi,

I'm trying to organize sub- reports by each 10 records. I have a total of 30 records sorted by a testid field and I want them divided within 3 subreports. Each 10 records has a the same date- example: 1:59:07 for the first 10, 2:20:15 for the second 10 and 3:54:14 for the 3rd 10. I was able to group them by the second to separate them, only I want the to line up side by side in 3 sub reports- instead of one below the other. They should look like this

23 23 25
24.4 27 21.1
21.2 32.2 17.7

etc.

with 10 records in each column. Does anybody know a way to separate them for each sub-report by something like recordnumber? They all appear in the same testresults.resulttext field.

Thanks.

 

[lbass]

Why do you want them in separate subreports? Is this only because you think this is the only way to get the desired display?

-LB

 

[jj1576]

well I thought about a crosstab, but I also need to have columns with averages and totals, plaus an averge of the averages for each row at the bottom. It seems like cross-tabs have issues with averages or summaries- some of them can't be done because they are calculated at a later pass then the chart.

I'll admit my experience with this type of reported is limited, so to answer your question, yes, that may very well be the case. Is there an easier way?

Thanks

Jeff

 

[lbass]

Please show a sample of what you mean, adding in the "average of averages for each row at the bottom."

-LB

 

[jj1576]

report would be in columns like this:

A B C D E F G

Column A is the first 10 records of the resulttext field.
B is the next 10 results of the resulttext field.
C is B-A
D is the last 10 results of the resulttext field.
E is D-A
F is C-E
G is F/C X 100 (gives a percent total.

At the bottom would be an average of column G.

Thanks.

 

[lbass]

Sample data would have been helpful. So when you say C = B-A, is this calculation a subtraction of record #11 from record #1? Or is it a subtraction of the sum of the first column of records from sum of the second column? In other words, are the calculations at the detail level or at the group level? If at the detail level, how are these records related to each other other than by order (which can easily be changed).

-LB

 

[jj1576]

As an example:

A B C D E F G

10 98 88 90 80 8 9.09
11 98 87 92 81 6 6.90

total average of G 7.99

This is an example of two rows. The actual report would have 10 rows- the calculations for row g would be at record level, and the total average would be group level. The records of the same date- (10 to each date- or a date for column A, B, and D)) are sorted by a testid field.

 

[lbass]

But you have not explained what record 1 and record 11 and record 21 have in common--do they share any value? Please show sample data before grouping that includes the ID field, the date, and any other field that explains the correspondence between the columns--there has to be some reason you are making these calculations between records 1, 11, 21 and records, 2,12, and 22, etc.

-LB

 

[jj1576]

They are all weight tests- the first 10 weight entries are taken after a barcode scan. Then the barcode is scanned again and the next ten weight entries are taken. There is a time stamp for each set of ten weight entries. Each weight entry is for a plate. the first 10 are the plate by itself. the second 10 are the plate weights with agar and the last are after incubation- all of this is laboratory talk, but that is why I need to separated them and then calculate average weight loss after incubation.

Record 1,11 & 21 are all the same plate with different conditions- that is the relationship. Sorry for the confusion & I Hope this makes more sense.

 

[lbass]

I think you should create a manual crosstab where you insert a group on {table.plateno}, and then create three formulas that will distinguish the sets of ten based on date:

//{@colA}:
if {table.datetime} = datetime(currentdate,time(1,59,7)) then {table.wgt}

//{@colB}:
if {table.datetime} = datetime(currentdate, time(2,20,15)) then {table.wgt}

Repeat for column D. Then insert maximums on each of these formulas at the plateno group level, and suppress the detail section.

For your calculations, you would use formulas like:

maximum({@colB},{table.plateno})- maximum({@colA},{table.plateno})

//etc.

It seems like there should be some fields that distinguish the three columns other than datetime, but anyway this should get you started.

-LB

 

[jj1576]

LB,

Thanks- that looks good, only thing is the datetime field varies- those were just the examples I was using. Is there a way to incorporate something into that formula-

//{@colA}:
if {table.datetime} = datetime(currentdate,time(1,59,7)) then {table.wgt}

allows for the time to vary- example- first time stamp, second time stamp, third etc.?

 

[lbass]

Trying to do this based on sequence would involve using variables, and would much more complicated. Don't you have some field that is based on the criteria that are being used at each interval, e.g., a type field (no addition, agar added, incubation) or an ID field? I wonder what other fields you have to work with. It might help to see the data at the detail level including all potentially relevant fields (as requested earlier).

-LB

 

[jj1576]

That's the problem- there really isn't anything that distinguishes them other then time. Initially I had this working because I was able to create different test names, but that would be cumbersome for the client to enter the data- this proposed way would be much easier, but I don't think it's possible.

Thanks for all your help.