I hope someone out there will have a solution to this issue because I can't seem to find anything that will work the way I would like it to.

I'm creating a report that uses OVER's and ACROSS's to show several metrics over a period of time stacked on top of each other. This is working fine. My issue comes when I start breaking down the information in the same way but also include a WHERE clause to show the metrics by division. My first section has the totals.

My second section only includes one Divisions information but it's laid out exactly the same as the total section. I currently get the divsion information by duplicating the total report and including a WHERE statement in my code.This is working fine except for the fact that they want to see what percentage the Division metirc is to the TOTAL metric above.

I was able to replicate it using the CASHFLOW table in the ibisamp app folder. Hopefully you will be able to run this sample code and see what I'm dealing with.

Basically, in the first section you will find the total 'LONG_TERM_DEBT' of $7,658,360,760.

The second section is the Debt Section and as you can see,'LONG_TERM_DEBT' IS $4,196,129,400

I would like to insert a metric below called 'LONG_TERM_DEBT AS % OF TOTAL' and have the value be 55% (4,196,129,400/7,658,360,760)
but I can't figure how to get the total LONG_TERM_DEBT value to appear in my Debt Section area.

Hopefully, someone can help me with this!

Keith


Here is the code:

DEFINE FILE CASHFLOW
TOTAL_DEBT/P15.2M=LONG_TERM_DEBT + INTER_TERM_DEBT + SHRT_TERM_DEBT;
TOTAL_EQUITY/P15.2M=LONG_TERM_EQUIT+INTER_TERM_EQUIT+SHRT_TERM_EQUIT;
MONTH/M=CASH_DATE;
DEBT_OR_EQUIT/A1=IF TOTAL_DEBT GT TOTAL_EQUITY THEN 'D' ELSE 'E';
END

TABLE FILE CASHFLOW
PRINT *
TOTAL_DEBT
TOTAL_EQUITY
MONTH
DEBT_OR_EQUIT
ON TABLE HOLD AS HOLD1
END

TABLE FILE HOLD1
SUM
LONG_TERM_DEBT OVER
INTER_TERM_DEBT OVER
SHRT_TERM_DEBT OVER
TOTAL_DEBT OVER
LONG_TERM_EQUIT OVER
INTER_TERM_EQUIT OVER
SHRT_TERM_EQUIT OVER
TOTAL_EQUITY
ACROSS MONTH
HEADING
"TOTAL Report"
END


TABLE FILE HOLD1
SUM
LONG_TERM_DEBT OVER
INTER_TERM_DEBT OVER
SHRT_TERM_DEBT OVER
TOTAL_DEBT OVER
LONG_TERM_EQUIT OVER
INTER_TERM_EQUIT OVER
SHRT_TERM_EQUIT OVER
TOTAL_EQUITY
ACROSS MONTH
WHERE DEBT_OR_EQUIT EQ 'D'
HEADING
"DEBT Only Report"
END

TABLE FILE HOLD1
SUM
LONG_TERM_DEBT OVER
INTER_TERM_DEBT OVER
SHRT_TERM_DEBT OVER
TOTAL_DEBT OVER
LONG_TERM_EQUIT OVER
INTER_TERM_EQUIT OVER
SHRT_TERM_EQUIT OVER
TOTAL_EQUITY
ACROSS MONTH
WHERE DEBT_OR_EQUIT EQ 'E'
HEADING
"EQUITY Only Report"
END

This message has been edited. Last edited by: Kathleen Butler,

Did you try to search on multiverb
that would solve your problem

Using your example:

  
-* File kfreers01.fex
DEFINE FILE CASHFLOW
TOTAL_DEBT/P15.2M=LONG_TERM_DEBT + INTER_TERM_DEBT + SHRT_TERM_DEBT;
TOTAL_EQUITY/P15.2M=LONG_TERM_EQUIT+INTER_TERM_EQUIT+SHRT_TERM_EQUIT;
MONTH/M=CASH_DATE;
DEBT_OR_EQUIT/A1=IF TOTAL_DEBT GT TOTAL_EQUITY THEN 'D' ELSE 'E';
END

TABLE FILE CASHFLOW
PRINT *
TOTAL_DEBT
TOTAL_EQUITY
MONTH
DEBT_OR_EQUIT
ON TABLE HOLD AS HOLD1
END

TABLE FILE HOLD1
SUM
LONG_TERM_DEBT NOPRINT
INTER_TERM_DEBT NOPRINT
SHRT_TERM_DEBT NOPRINT
TOTAL_DEBT NOPRINT
LONG_TERM_EQUIT NOPRINT
INTER_TERM_EQUIT NOPRINT
SHRT_TERM_EQUIT NOPRINT
TOTAL_EQUITY NOPRINT
BY MONTH
SUM 
LONG_TERM_DEBT  
INTER_TERM_DEBT  
SHRT_TERM_DEBT  
TOTAL_DEBT  
LONG_TERM_EQUIT  
INTER_TERM_EQUIT  
SHRT_TERM_EQUIT  
TOTAL_EQUITY
COMPUTE 
PCT_LTD/D7.2%=C9/C1 * 100;
PCT_ITD/D7.2%=C10/C2 * 100;
PCT_STD/D7.2%=C11/C3 * 100;
PCT_TTD/D7.2%=C12/C4 * 100;
PCT_LTE/D7.2%=C13/C5 * 100;
PCT_ITE/D7.2%=C14/C6 * 100;
PCT_STE/D7.2%=C15/C7 * 100;
PCT_TTE/D7.2%=C16/C8 * 100;
BY MONTH
BY DEBT_OR_EQUIT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HOLD2
END
-RUN
DEFINE FILE HOLD2
DOE/A8=IF DEBT_OR_EQUIT EQ 'D' THEN 'Debt' ELSE 'Equity';
END
TABLE FILE HOLD2
SUM
LONG_TERM_DEBT OVER
PCT_LTD OVER
INTER_TERM_DEBT OVER
PCT_ITD OVER
SHRT_TERM_DEBT OVER
PCT_STD OVER
TOTAL_DEBT OVER
PCT_TTD OVER
LONG_TERM_EQUIT OVER
PCT_LTE OVER
INTER_TERM_EQUIT OVER
PCT_ITE OVER
SHRT_TERM_EQUIT OVER
PCT_STE OVER
TOTAL_EQUITY OVER
PCT_TTE
ACROSS MONTH
BY DOE NOPRINT PAGE-BREAK
HEADING
"<DOE Only Report"
END

Danny,

Your solution worked great for what I needed to show on my report.

Thanks Again,

Keith