Skip to Content
author's profile photo
Former Member

How to display two detail lines as one

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?

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    Posted on Apr 28, 2011 at 10:38 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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.