If you run this IBI sample code,

 
SET BLANKINDENT=ON
SET FORMULTIPLE = ONJOIN SYS_ACCOUNT IN CENTGL TO ALL SYS_ACCOUNT IN CENTSYSF
TABLE FILE CENTGL
SUM 
NAT_AMOUNT/D10.0 
NAT_YTDAMT/D10.0
FOR GL_ACCOUNT
3100 WITH CHILDREN ALL AS CAPTION OVER 
BAR                               OVER
3100 ADD AS CAPTION
IF PERIOD EQ '2002/03'
END 

The summary groups Selling Expenses and Advertising are displayed with the '.' in the data columns indicating that no data exists for this summary levels. If you add WHEN EXISTS to "3100 WITH CHILDREN ALL..." line and run the report again, you no longer see the Selling Expenses and Advertising lines.

If there were other subcategories of Advertising in addition to TV/Radio, Print Media, Internet Marketing, would there be a way to only include those subcategories when they exist but still be able to see the summary levels of Advertising and Selling Expenses?

Bethany

I'm not sure I understand the questionright, but what you are asking about is the default behavior of WHEN EXISTS. If any sub-level exists, then the parent exists. So if there are other sub-categories of Advertising that exist, then you would still see the summary level of Advertising.

This message has been edited. Last edited by: Darin Lee,

If a child exists then the parent exists is what I thought too, but If you run the code below the Selling Expenses and Advertising lines are no longer in the output.

 
SET BLANKINDENT=ON
SET FORMULTIPLE = ONJOIN SYS_ACCOUNT IN CENTGL TO ALL SYS_ACCOUNT IN CENTSYSF
TABLE FILE CENTGL
SUM 
NAT_AMOUNT/D10.0 
NAT_YTDAMT/D10.0
FOR GL_ACCOUNT
3100 WITH CHILDREN ALL WHEN EXISTS AS CAPTION OVER 
BAR                                           OVER
3100 ADD AS CAPTION
IF PERIOD EQ '2002/03'
END  

Bethany

Bethany,

I think that you'll find that the parent levels do exist but the value is held at the child level, so WHEN EXISTS is used only those data entities that have data are displayed? If you table out all the data (not using FML just TABLE FILE PRINT *) from the joined structure using the join that you have (typo detected?) you will find that you only have the data at child level. If you add LEFT_OUTER to the join then you will see parent levels without financial data.

I would imagine that if you had daat associated directly with the parent (why?) then the WHEN EXISTS would behave as you are expecting?

It might be worth raising an info response case to get the official view (unless the PM has their ears on?).

T

This message has been edited. Last edited by: Tony A,

I did an info response case on this and was told that WHEN EXISTS works as it should in this code.

 
SET BLANKINDENT=ON
SET FORMULTIPLE = ON
JOIN SYS_ACCOUNT IN CENTGL TO ALL SYS_ACCOUNT IN CENTSYSF
TABLE FILE CENTGL
SUM 
NAT_AMOUNT/D10.0 
NAT_YTDAMT/D10.0
FOR GL_ACCOUNT
3100 WITH CHILDREN ALL WHEN EXISTS AS CAPTION OVER 
BAR                                           OVER
3100 ADD AS CAPTION
IF PERIOD EQ '2002/03'
END  
 

Which unfortunately doesn't show the parent levels of Advertising or Selling Expenses since there is no data directly associated with them.

They suggested using the following code which has the subtotals at the parent levels.

 SET BLANKINDENT=ON
SET FORMULTIPLE = ON
JOIN SYS_ACCOUNT IN CENTGL TO ALL SYS_ACCOUNT IN CENTSYSF
TABLE FILE CENTGL
SUM
NAT_AMOUNT/D10.0
NAT_YTDAMT/D10.0
FOR GL_ACCOUNT
3100 WITH CHILDREN 2  ADD WHEN EXISTS AS CAPTION OVER
BAR                               OVER
3100 ADD AS CAPTION
IF PERIOD EQ '2002/03'
END 

Output:

             
                                 Month      YTD 
                                 Actual    Actual 
Selling Expenses              1,554,319. 4,451,098. 
Advertising                   1,303,277. 3,705,368. 
TV/Radio                      1,049,146. 2,954,342. 
Print Media                     244,589.   721,448. 
Internet Advertising              9,542.    29,578. 
Promotional Expenses             53,719.   151,732. 
Joint Marketing                  97,135.   289,799. 
Bonuses/Commisions              100,188.   304,199. 

Selling Expenses              1,554,319. 4,451,098. 
 

I still don't think this is ideal, but it would be easier to read if the parent levels were bold. Does anyone know how you could format it to make only the parent levels bold?

Thanks as always!
Bethany

quote:

bold

Bethany,

The way to get bold is use FMLINFO to first get account number into a define or compute field.

Then use this field in a test. Example:

ISTEST/A1=IF ACCOUNT_FLD EQ 1000 THEN 'Y' ELSE ... O

Then create a bold style along with a macro against the test field.

Fernando

Would you be able to write out the code using the IBI sample data? I don't understand how this would work with the FML Hierarchy.

Thanks,
Bethany

Bethany,

Here is a sample using your code: I assuming accounts 3100, 3200, 3300 are bold and nothing else

SET BLANKINDENT=ON
SET FORMULTIPLE = ON
JOIN SYS_ACCOUNT IN CENTGL TO ALL SYS_ACCOUNT IN CENTSYSF
TABLE FILE CENTGL
SUM
NAT_AMOUNT/D10.0
NAT_YTDAMT/D10.0
COMPUTE F_VALUE/I7 = EDIT (FMLINFO, 'FORVALUE', 'A7')); NOPRINT
COMPUTE STYLE_BOLD/I2 = DECODE F_VALUE( 3100 1 3200 1 3300 1 ELSE 0 ); NOPRINT
FOR GL_ACCOUNT
3100 WITH CHILDREN 2  ADD WHEN EXISTS AS CAPTION OVER
BAR                               OVER
3100 ADD AS CAPTION
IF PERIOD EQ '2002/03'
ON TABLE SET STYLE *
UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT,$
 DEFMACRO=COND0001,
 MACTYPE=RULE,
 WHEN=STYLE_BOLD EQ 1,$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
	 COLOR=BLACK,
     BACKCOLOR='NONE',
     STYLE=NORMAL,$
TYPE=REPORT,
     STYLE=BOLD,
     COLOR=BLACK,
     MACRO=COND0001,$
ENDSTYLE
END

Fernando