Running Total: Getting previous record's number

[tubbers]

Hi, I'm using XI and connecting to a SQL Server 2000 database. I'm not sure if I'm using the most efficient method to get my answers and I'm stumped as to how to make this work.

Problem:
In the sample data below, the Count field is a running total which resets on the change of a group. The 2nd Consec field is a formula which tells me when a patient has hit the therapeutic range we're looking for. What I'm looking for is the number of time we had to test before we hit the therapeutic range.

I tried this formula:

If {@2nd Consec} = "yes" Then Previous({#Count})
Else
0

but I get the following error: "This field has no previous or next value".

Is there a different way of getting this value? In the example below, the correct value is 8 (the patient was in the therapeutic range when the 9th test was taken so we know that it took 8 "attempts" to get there).

Name Collection DTTM      Result Range? Count  2nd Consec

AAAA 3/28/2005 7:25:00AM  200    N      1      no
AAAA 3/28/2005 3:46:00PM  75     Y      2      no
AAAA 3/29/2005 6:00:00AM  61     N      3      no
AAAA 4/1/2005 4:45:00PM   66     N      4      no
AAAA 4/2/2005 1:09:00AM   65     N      5      no
AAAA 4/2/2005 9:30:00AM   176    N      6      no
AAAA 4/2/2005 3:50:00PM   108    Y      7      no
AAAA 4/3/2005 5:35:00AM   112    N      8      no
AAAA 4/3/2005 2:15:00PM   84     Y      9      yes
AAAA 4/4/2005 5:40:00AM   79     Y      10     yes
AAAA 4/5/2005 5:50:00AM   101    Y      11     yes
AAAA 4/6/2005 5:10:00AM   109    Y      12     no
AAAA 4/7/2005 6:45:00AM   119    N      13     no

 

[synapsevampire]

We don't know what's in {@2nd Consec}, so don't expect us to provide a bullteproof solution for unless you include unknowns.

So do you want the previous groups row count, or the previous groups count when it hit a yes?

I'll assume the previous rows count when it hit a yes.

Probably not the most efficient means, but try:

groupheader formula:
whilprintingrecords;
booleanvar other;
if other = true then
other := false
else
other := true;
numbervar MyLast1;
numbervar MyLast2;
numbervar Rowcount:=1

Details formula:
whilprintingrecords;
booleanvar other;
numbervar MyLast1;
numbervar MyLast2;
numbervar RowCount:=RowCount+1
If {@2nd Consec} = "yes" then
(
if other=true then
MyLast1:= RowCount
else
MyLast2:= RowCount
);

Then the display formula in the details for the last groups row is
whilprintingrecords;
booleanvar other;
numbervar MyLast1;
numbervar MyLast2;
numbervar RowCount:=RowCount+1
If {@2nd Consec} = "yes" then
(
if other=true then
MyLast2
else
MyLast1
);

Should get you close, again, it's hard to know exactly what you want, your post speaks more to what we know can't work (getting a previous groups running total value) rather than showing what it is that you do want.

-k

 

[tubbers]

I apologize for not sharing all of the formulas.

The other formulas:

@Range
(This is the data under the "Range ?" heading and to be considered in range the test result must be between 70 and 110)

If {table.result} > 69 and {table.result} < 111 Then "Y"
Else
"N"

@2nd Consec

If {@Range} = "Y" THEN
    If {@Range} = Next({@Range}) Then "yes" else "no"
Else
    "no"

The sample data I provided in the first post is in the detail section of a subreport which is placed in the group footer of the main report.

I attempted to use the formulas you provided but I didn't get anywhere near the results I was looking for, which is probably due to me not providing sufficient information more than anything.

Using your formulas, the details formula displays 19, 21, 23 on the 9th, 10th and 11th lines of data (where 2nd Consec = "yes") respectively. All other rows have a value of 0. The display formula shows 0 for all 13 lines of data. I may have placed the formulas in incorrect locations?

What I was hoping to get(if possible) is this:

Main Report

Patient Account: 123456        [COLOR=blue](in group 1 header)[/color]
Orders:                        [COLOR=blue](in detail section)[/color]
  Order 1
  Order 2
  Order 3, etc.

Laboratory Results   [COLOR=blue](group footer 1a)[/color]
[COLOR=red]**begin subreport[/color]
Name Collection DTTM      Result Range? Count  2nd Consec

AAAA 3/28/2005 7:25:00AM  200    N      1      no
AAAA 3/28/2005 3:46:00PM  75     Y      2      no
AAAA 3/29/2005 6:00:00AM  61     N      3      no
AAAA 4/1/2005 4:45:00PM   66     N      4      no
AAAA 4/2/2005 1:09:00AM   65     N      5      no
AAAA 4/2/2005 9:30:00AM   176    N      6      no
AAAA 4/2/2005 3:50:00PM   108    Y      7      no
AAAA 4/3/2005 5:35:00AM   112    N      8      no
AAAA 4/3/2005 2:15:00PM   84     Y      9      yes
AAAA 4/4/2005 5:40:00AM   79     Y      10     yes
AAAA 4/5/2005 5:50:00AM   101    Y      11     yes
AAAA 4/6/2005 5:10:00AM   109    Y      12     no
AAAA 4/7/2005 6:45:00AM   119    N      13     no
[COLOR=red]**end subreport[/color]

[COLOR=blue](in group footer 1b}[/color]
[b]The number of tirations to therapy:  8[/b]

I essentially need to pass the row number back to the main report via a variable (I assume).

 

[synapsevampire]

Ahhh, much simpler indeed.

In the group header place this formula:
whileprintingrecords;
booleanvar FirstYes:=false;


In the subreport modify your formula:
whileprintingrecords;
booleanvar FirstYes;
shared numebervar Myrow;
stringvar Out:="";
If {@Range} = "Y" THEN
If {@Range} = Next({@Range}) Then
Out:="yes" else
Out:="no"
Else
Out:= "no";
If not(FirstYes) and out = "yes" then
(
Myrow := {#Count};
Firstyes:=true
);
Out

Now you can reference the formula in the main report after the subreport has fired using:

whileprintingrecords;
shared numebervar Myrow;

-k

 

[tubbers]

Thank you, I was able to get it to work the way I needed to with a slight change.

Could you help me understand how this formula works a little better?

I understand the first formula which you place in the group header...it's setting (or resetting)the FirstYes variable to a false value.

Then in the detail formula, I understand it up until Line 11.

[COLOR=blue]1[/color]  whileprintingrecords;
[COLOR=blue]2[/color]  booleanvar FirstYes;
[COLOR=blue]3[/color]  shared numbervar Myrow;
[COLOR=blue]4[/color]  stringvar Out:="";
[COLOR=blue]5[/color]  If {@Range} = "Y" Then
[COLOR=blue]6[/color]  If {@Range} = Next({@Range}) then
[COLOR=blue]7[/color]  Out:="Yes" else
[COLOR=blue]8[/color]  out:="no"
[COLOR=blue]9[/color]  else
[COLOR=blue]10[/color] out:="no";
[COLOR=blue]11[/color] if not(FirstYes) and out = "Yes" then
[COLOR=blue]12[/color] (
[COLOR=blue]13[/color] myrow := {#Count};
[COLOR=blue]14[/color] FirstYes:=true
[COLOR=blue]15[/color] );
[COLOR=blue]16[/color] Out

It's mainly line 11 where I get a little confused, specifically the "not(FirstYes)". What is that asking? Is it saying if FirstYes = False and Out = Yes then change FirstYes to true and populate the MyRow variable? Or was the value of FirstYes set to true some other place?

I appreciate your time and effort. I like to understand and learn from the suggestions that I receive in this forum.

 

[synapsevampire]

Yeah, you get it, it resets the firstyes to true so that further iterations won't change the MyRow value.

-k

 

[tubbers]

Thanks!