How to display two detail lines as one

[Former Member]

In a report I am working on I have a detail line that displays values from two tables.

Table OR contains the date time stamp I need

Table OB contains the rest of the information I need.

(These two tables are linked by a Set_Id field.)

Table OB contains different types of data depending on the OB_Id field.

If OB_Id = 1 then OB_fldA = weight.

If OB_Id = 2 then OB_fldB = appetite_flg.

Below I have tried to type up a sample of the report. I have it grouped by the OR_DtTm.

I would like to get the report to look like there is one line that contains both the weight and the appetite_flg and eliminate any lines that have weight info and no appetite_flg to go with it (like the 3rd one down with the time of 3:45:30).

How it currently looks:

Date        Weight         Flg         OR_DtTm
6/22/2010   64.8                    6/22/2010 3:44:31PM
6/22/2010    0.0            N       6/22/2010 3:44:31PM
6/22/2010   64.0                    6/22/2010 3:45:30PM
8/3/2010    64.0                    8/3/2010 9:09:01AM
8/3/2010                    Y       8/3/2010 9:09:01AM

How I would like for it to look:

Date         Weight         Flg         OR_DtTm
6/22/2010    64.8            N        6/22/2010 3:44:31PM
8/3/2010     64.0            Y        8/3/2010 9:09:01AM

I hope this makes sense. Any suggestions on how to do this?

[former_member292966 · Accepted solution]

Hi,

In your Group Footer create two formulas. One for your weight and one for your FLG.

Your weight formula would look something like:

Maximum ({OB.OB_fldA});

Your FLG formula would like like:

Maximum ({OB.OB_fldB});

Drop the information you want to see in the Group Footer with these formula.

Hide the Detail section so you don't see it.

You can now format your Group Footer section to suppress if the Weight formula is 0 or the FLG formula is "".

Good luck,

Brian

[Former Member · Accepted solution]

Hi Brian,

Sorry to take so long to get back to you. I spent most of Friday wrestling with this trying several variations of your suggestion but haven't gotten any of them to work. Either I didn't quite understand what you were saying or it's not quite there yet. It certainly seems like it could work once I get the specifics correct.

Just putting the formulas as you suggested in the report returned incorrect values. The MaxAppetiteFlg was always 'Y'. The MaxWeight was always '59.30' which didn't correspond with any of the records I saw.

I then tried making the formulas conditional based on the OB_Id field. Something like this:

If OB.OB_Id = 1 then
   Maximum ({OB.OB_fldA});

and

If OB.OB_Id = 2 then
   Maximum ({OB.OB_fldB});

Then MaxWeight is always 0.0 and MaxAppetiteFlg is always 'Y'.

My first attempt at this was to create two subreports: one for the weight the other for the appetite_flag. That works just fine except for not displaying weight records that don't have a corresponding appetite_flag record. I went back to trying this and still don't have it working either.

So I am still working on it and open to any suggestions you might have.

Thank you for your time!

[Former Member · Accepted solution]

I thought I would try using variables to hold the values I need. According to Mr. Peck in his The Complete Reference variables are suppose to retain their value until explicitly changed. So I thought I would try this in my MaxWeight formula

WhilePrintingRecords;
NumberVar HoldWeight;

If {Ob.OB_ID} = 1 then
   HoldWeight := ({Ob.Obs_FldA});

I did the same thing with MaxAppetiteFlg

WhilePrintingRecords;
StringVar HoldAppetite_flg;

If {Ob.OBD_ID} = 2 then
   HoldAppetite_flg := {@DecreasedAppetiteInd};

BTW - HoldWeight and HoldAppetite_flg are first declared in a formula that is placed in the Report Header.

So in theory, the only time HoldWeight should change is when the OB.OB_ID = 1.

I tried just putting them in the GroupFooter and this is what I got:

Date        Weight         Flg         OR_DtTm 
6/22/2010
6/22/2010   64.8                    6/22/2010 3:44:31PM 
6/22/2010    0.0            N       6/22/2010 3:44:31PM 
             0.0            N

8/3/2010    64.0                    8/3/2010 9:09:01AM 
8/3/2010     0.0            N       8/3/2010 9:09:01AM
             0.0            N

The lines without a date is the group footer. So it looks like the HoldWeight variable is being reset when ob.OB_ID is not equal 1.

I also tried without the "WhilePrintingRecords" evaluation. I tried putting these formulas also in the detail just in case it needed it there.

Grrrr! Any ideas?

Edited by: SweetPea on May 3, 2011 1:05 AM

[Former Member · Accepted solution]

As you are grouping on OR_DtTm your maximums must be determined at that group

Using Maximum ({OB.OB_fldA}); will return max of the entire report that is why its always the same.

Use this instead

Maximum ({OB.OB_fldA}, );

Do same for your FLG formula too.

Ian

[Former Member · Accepted solution]

Ah, yes. That makes sense.

I went into the MaxWeight formula and typed in this

Maximum ({Ob.Ob_FldA}, {OR_DtTm})

I double checked that OR_DtTm field is the one used in the group. But when I clicked Save and close I get the following error:

There must be a group that matches this field

Am I not understanding correctly how to set this up?

[Former Member · Accepted solution]

Its probably a typo, instead of typing

Maximum ({Ob.Ob_FldA}, ) delete

leave cursor there and the double click correct field used for group, could be that you have missed the table name prefix.

Ian

[Former Member · Accepted solution]

Yeah, I acutally did build it that way. I never type anything ;-}. Too easy to make a mistake.

I thought maybe it wanted the Group #1 Name... from the Report variables but that didn't work either.

[Former Member · Accepted solution]

Ok, I don't get it.

In order to get the grouping to work correctly I had changed the "The section will be printed:" value to for each second. Just to see if this was causing a problem, I went into Report | Group Expert | Options button and changed it back to the default (for each day). I went back to edit the MaxWeight formula to use the group name and it saved it just fine. Preview the report and it works beautifully. Went back into the Group Expert and changed it back to for each second. Came back out and tried to preview the report and got the error "There must be a group that matches this field". I tried a few other options (for each year; for each minute) and they all give the same error message. The only one that works is the for each day. Fortunately, the grouping seems to be working without changing this option.

So, it seems like I have the report doing what I want it to do. Thanks Ian and Brian for all your help with this.

But I would still like to understand the variables and why they seem to be resetting (as I explained in an earlier post in this thread). If anybody can shed any light on that I would appreciate it.

Edited by: SweetPea on May 3, 2011 8:03 PM

[Former Member · Accepted solution]

Hey Brian,

Now that I have the Maximum formulas working on my Group Footer, I need to figure out how to supress when the MaxAppetiteFlg is not there. In the Section Expert on the Group Footer I clicked the x+2 button and put this:

{@MaxAppetiteFlg} = "" or {@MaxAppetiteFlg} = " "

but it still does not suppress when nothing is in the MaxAppetiteFlg. I have tried single quotes and double quotes; I have tried checking for just null and for just space and as shown above for both. Nothing suppresses the group footer when the MaxAppetiteFlg is blank/empty. What actually goes into the formula?

Ok, let me back up a little. I don't think this makes a difference but you never know. I am going to explain the steps preceeding the creating of @MaxAppetiteFlg. It's a little more complicated than I originally indicated (I removed the complexicity to hopefully make it easier to understand my original question).

Originally I said:

If OB_Id = 2 then OB_fldB = appetite_flg

Actually it is

OB_Id = 2 is part of the filter in the Select Expert.  Then I created a formula:
DecreaseAppetiteInd =
     If {OB.Ob_fldB = 22474 then
          'N'
     Else If {OB.Ob_fldB = 22475 then
          'Y' ;

Then MaxAppetiteFlg = 
     Maximum ({@DecreasedAppetiteInd},{Ob_DtTm})

So I don't know if that makes a difference in later checking MaxAppetiteFlg (but I didn't think it would make a difference whether I had changed the options on the group but it did).

I hope all this makes sense and I haven't muddied the water too badly.

I look forward to your response.