We have a simple table with the following columns:

Dept Period Charges.


Data is stored vertically.

A simple Report fex shows Dept down the page, period across and charges summed.

I have been asked to show the difference in charges from one month to another.

Is there a function or something that will do this? How would I do this?

EX: current report lists Jul - June across the report, Depts down, charges summed. We want Jul, then August then a delta Jul - Aug, then Sept, then a delta column Aug-Sep and so on.

Thoughts.

This message has been edited. Last edited by: RobertF,

quote:

period across and charges summed

can you provide some sample structure of your current data and required structure.

You can try computed field using the LAST command.

ie

COMPUTE CHANGE1=SUMMED - LAST SUMMED.

This should do it:

 
-* File robertf01.fex
SET ASNAMES=ON
-*
-* Part 1: create some data for the report
TABLE FILE CAR
SUM DCOST AS DCOST
BY COUNTRY
BY MPG IN-GROUPS-OF 5 
ON TABLE HOLD
END
-RUN
-* Use the 'ROWS' option of BY in order to have all values of MPG per COUNTRY
TABLE FILE HOLD
SUM DCOST
BY COUNTRY
BY MPG AS MPG ROWS 05 OVER 10 OVER 15 OVER 20 OVER 25
ON TABLE HOLD AS H1 MISSING OFF
END
-RUN
-* Create a random value for DCOST when DCOST is 0
-* Then use the LAST function to calculate differences
-* HOLD
DEFINE FILE H1
MYDCOST/D6=IF DCOST EQ 0 THEN 4000 * RDUNIF('D6.4') ELSE DCOST
END
TABLE FILE H1
SUM MYDCOST
COMPUTE DIFF/D6=IF COUNTRY NE LAST COUNTRY THEN MYDCOST ELSE MYDCOST - LAST MYDCOST;
BY COUNTRY
BY E02 AS MPG
ON TABLE HOLD AS H2 
END
-RUN
-* The report
TABLE FILE H2
SUM MYDCOST DIFF
BY COUNTRY
ACROSS MPG
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE=ENDEFLT,
$
ENDSTYLE
END
 

Ha, Danny just beat me to it! Never mind, here's another idea just to confuse

SET ASNAMES = ON
SET HOLDATTR = ON
DEFINE FILE GGSALES
  YEAR/YY = DATE;
  MONTH/Mt = DATE;
END
TABLE FILE GGSALES
  SUM DOLLARS
   BY YEAR   AS 'Year'
   BY REGION AS 'Region'
ACROSS MONTH AS ''
ON TABLE HOLD AS MYDATA FORMAT ALPHA
END
-RUN
TABLE FILE MYDATA
  SUM DOL01   AS 'Jan'
      DOL02   AS 'Feb'
      COMPUTE DIFF1/I11 = DOL01 - DOL02; AS 'Difference,Jan - Feb'
      DOL03   AS 'Mar'
      COMPUTE DIFF2/I11 = DOL02 - DOL03; AS 'Difference,Feb - Mar'
      DOL03   AS 'Apr'
      COMPUTE DIFF3/I11 = DOL03 - DOL04; AS 'Difference,Mar - Apr'
   BY YEAR
   BY REGION
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
  grid=off, border=off, $
  type=title, justify=center, backcolor=yellow, $
  type=data, column=N5, backcolor=green, color=white, $
  type=data, column=N7, backcolor=green, color=white, $
  type=data, column=N9, backcolor=green, color=white, $
ENDSTYLE
END
-RUN

T

tony..it works...or I can make it work I guess...thanks!

That's good Robert, at least you can move forward. Holding an ACROSS output can often provide for datasets such as this.

One of the important things is the SET ASNAMES = ON as that will result in the column names being prefixed with the first three chars of the summated column and suffixed with the value from the ACROSS.

In this instance, because I was using "Mt" as the format, the resultant ACROSS values became "01" to "12".

T

funny you mention that. The report must run for the prior 12 months so the months are always changing - I was just trying to figure out how to automate the naming of the columns....can't hard code them...

Ex: if I run the report today, it runs from last Jan thru this Dec. Further we are on a fiscal year. I do have a column that tells me Jul = 01, Aug = 02 etc etc. However If I want the report as of Sept 2016...they would want to see Oct 2015 - Sept 2016 trended. The month sort would be Month 10, 11, 12, 01, 02, 03...09.

I think I am again stuck....

Robert,
Since you know what is the first month you can use a DECODE function in DEFINE to translate months to values. For example when starting with month 10:

  
DEFINE FILE yourfile
...
MSORT/A2=DECODE MONTH(10 01 11 02 12 03 01 04 02 05 03 06 04 07 05 08 06 09 07 10 08 11 09 12);
...
END
TABLE FILE yourfile
...
ACROSS MSORT NOPRINT ACROSS MONTH
...
END

interesting, maybe you can help me think this through. The fex prompts the user for an ending period. Its in the form YYYYMM. Example, the user wants to run a report trended 12 months ending October 2016. They enter 201610. The fex says select:

rptperiod <= &PERIOD and
rptperiod > &period - 100

..it yields periods 20201511 - 201610.

the periods must be ordered:
201511
201512
201601
201602
.
.
.201610

I need to find a way to order the months knowing the fex can start with a different period any time its run....

A bit of Dialog Manager:

 
-SET &ECHO=ALL;
-* File robertf02.fex
-SET &PERIOD='201610';
-SET &SPERIOD=AYM(&PERIOD, -11, 'I6');
-TYPE &SPERIOD
-SET &MON=EDIT(&SPERIOD, '$$$$99');

DEFINE FILE yourfile
...
MSORT/I2L=DECODE MONTH(&MON 1
-REPEAT #DECODE FOR &I FROM 1 TO 11;
-SET &TPERIOD=AYM(&SPERIOD, &I, 'I6');
-SET &MON=EDIT(&TPERIOD, '$$$$99');
-SET &J=&I + 1;
 &MON &J
 -#DECODE
 );
...
END

TABLE FILE yourfile
...
ACROSS MSORT NOPRINT ACROSS MONTH
...
END
 

that was fast, thanks! This is a bit beyond my typical coding level. I need to figure out exactly what each statement is doing.

I figured I'd start by referencing my calendar table and try to see if I could simply get the months to go across.

What does 'MONTH' do in the MSORT. I get an error:


-SET &ECHO=ALL;
-SET &PERIOD='201610';
-SET &SPERIOD=AYM(&PERIOD, -11, 'I6');
-TYPE &SPERIOD
-SET &MON=EDIT(&SPERIOD, '$$$$99');


DEFINE FILE TBLMSTR_CALENDARPERIOD
MSORT/I2L=DECODE MONTH(&MON 1
-REPEAT #DECODE FOR &I FROM 1 TO 11;
-SET &TPERIOD=AYM(&SPERIOD, &I, 'I6');
-SET &MON=EDIT(&TPERIOD, '$$$$99');
-SET &J=&I + 1;
&MON &J
-#DECODE
);
END



TABLE FILE TBLMSTR_CALENDARPERIOD
ACROSS MSORT NOPRINT ACROSS MONTH
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END



-SET &J=&I + 1;

Sorry j = j + 1 was not the error!....copied wrong thing, here is the error
.
.
.

riserBevel":"bevel"
*END
$
ENDSTYLE
END
0 ERROR AT OR NEAR LINE 9 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: MONTH
0 ERROR AT OR NEAR LINE 22 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: MSORT
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

Robert,
There are a few things missing in your example:
1. In the DEFINE you have not calculated the field MONTH.
2. In the TABLE where are the SUM and the BY
I suggest that you post your procedure limited to the date you are using for the ACROSS, the BY field and the SUM field. We can take it from there. And if it is too convoluted, we can try using TeamViewer and Skype...

quote:

SET &ECHO=ALL;-SET &PERIOD='201610';-SET &SPERIOD=AYM(&PERIOD, -11, 'I6');-TYPE &SPERIOD-SET &MON=EDIT(&SPERIOD, '$$$$99');DEFINE FILE TBLMSTR_CALENDARPERIODMSORT/I2L=DECODE MONTH(&MON 1-REPEAT #DECODE FOR &I FROM 1 TO 11;-SET &TPERIOD=AYM(&SPERIOD, &I, 'I6');-SET &MON=EDIT(&TPERIOD, '$$$$99');-SET &J=&I + 1; &MON &J -#DECODE );END

My bad, I guess I need to understand the concept first. It looks like you are determining and ordering the months. I thought MONTH was a function. Perhaps if I get a better understanding of what some of this does...though the echo all does help...


SET &ECHO=ALL;
-SET &PERIOD='201610';
* this will be the period the user selects...

-SET &SPERIOD=AYM(&PERIOD, -11, 'I6');
It looks like this is uses a function to set the start period...?

-TYPE &SPERIOD
Displays the start period...


-SET &MON=EDIT(&SPERIOD, '$$$$99');
MON is the start period, looks like you are flipping the type...why?



DEFINE FILE TBLMSTR_CALENDARPERIOD
MSORT/I2L=DECODE MONTH(&MON 1
-REPEAT #DECODE FOR &I FROM 1 TO 11;
-SET &TPERIOD=AYM(&SPERIOD, &I, 'I6');
-SET &MON=EDIT(&TPERIOD, '$$$$99');
-SET &J=&I + 1;
&MON &J
-#DECODE
);
END

It might be best if you just said a few words about what this is doing, obviously looping through and ordering but a few words might get me going again..or tell me what is expected for: MONTH and I'll add in the appropriate field from my table then I can probably see what happening.

Thanks!

ok, MONTH is what we call period..I am piecing this together trying to build the hold initial HOLD file, I am getting only 1 month...I'll keep playing with it.



-SET &ECHO=ALL;
-DEFAULT &PERIOD='201611';
-SET &SPERIOD=AYM(&PERIOD, -11, 'I6');
-TYPE &SPERIOD
-SET &MON=EDIT(&SPERIOD, '$$$$99');
SET ASNAMES = ON
SET HOLDATTR = ON


JOIN
INNER TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.PERIOD IN
TBLMSTR_PAYCODEDETAIL TO UNIQUE
TBLMSTR_CALENDARPERIOD.TBLMSTR_CALENDARPERIOD.PERIOD IN TBLMSTR_CALENDARPERIOD
TAG J0 AS J0
END


DEFINE FILE TBLMSTR_PAYCODEDETAIL
MSORT/I2L=DECODE PERIOD(&MON 1
-REPEAT #DECODE FOR &I FROM 1 TO 11;
-SET &TPERIOD=AYM(&SPERIOD, &I, 'I6');
-SET &MON=EDIT(&TPERIOD, '$$$$99');
-SET &J=&I + 1;
&MON &J
-#DECODE
);
END

TABLE FILE TBLMSTR_PAYCODEDETAIL
SUM
TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.MSORT
TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.PERIOD
TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.ADJAMOUNT
BY LOWEST TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.SPECIALTY
WHERE TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.PERIOD LE &PERIOD.(FIND TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.PERIOD,TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.PERIOD IN TBLMSTR_PAYCODEDETAIL).PERIOD.;
WHERE TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.PERIOD GT &PERIOD - 100;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END

Robert,

I shall assume (oh the danger of assuming...) that the field PERIOD is an integer, YYYYMM. If not some adjustments will have to be made:

  
-SET &ECHO=ALL;
-*
-* This is the end PERIOD
-DEFAULT &PERIOD='201611';
-*
-* This calculates the start PERIOD
-SET &SPERIOD=AYM(&PERIOD, -11, 'I6');
-TYPE &SPERIOD
-*
-* I had isolated the month thinking that you are sorting by month without the year.
-* Are you?
-* If not, meaning that you are showing across the year-month values, you don't need the special sorting as I wrote it.
-* Forget the following
-SET &MON=EDIT(&SPERIOD, '$$$$99');
SET ASNAMES = ON
SET HOLDATTR = ON


JOIN
INNER TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.PERIOD IN 
TBLMSTR_PAYCODEDETAIL TO UNIQUE
TBLMSTR_CALENDARPERIOD.TBLMSTR_CALENDARPERIOD.PERIOD IN TBLMSTR_CALENDARPERIOD
TAG J0 AS J0
END

-* Forget the DEFINE
DEFINE FILE TBLMSTR_PAYCODEDETAIL
MSORT/I2L=DECODE PERIOD(&MON 1
-REPEAT #DECODE FOR &I FROM 1 TO 11;
-SET &TPERIOD=AYM(&SPERIOD, &I, 'I6');
-SET &MON=EDIT(&TPERIOD, '$$$$99');
-SET &J=&I + 1;
&MON &J
-#DECODE
);
END

TABLE FILE TBLMSTR_PAYCODEDETAIL
SUM 
-*TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.MSORT
TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.PERIOD
TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.ADJAMOUNT
BY LOWEST TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.SPECIALTY
WHERE TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.PERIOD LE &PERIOD.(FIND TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.PERIOD,TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.PERIOD IN TBLMSTR_PAYCODEDETAIL).PERIOD.;
-* This condition is wrong
-*WHERE TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.PERIOD GT &PERIOD - 100;
-* Use this
WHERE TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.PERIOD GE &SPERIOD;
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END

-* However if you want to show across just the months 10 11 12 01 ... come back.

I am sorry ...been away. I have not tried your latest code however I got it working with period earlier this week where the result would give me, say for a report run of: OCT/NOV/DEC/JAN/FEB/MAR/...SEPT it would correctly ordered as OCT = 01, NOV = 02 etc etc.

If I save as a HOLD file I assume it will created some sort of column for each of the 12 months. How will label them though on the report...sure I could just dump the columns as PMTS01, PMTS02 etc but it won't be obvious to the user that PMTS01 is: OCT, PMTS02 is Nov etc...getting closer though...

I think we have come full circle on this. I feel bad, you put a bit of time into this. Looking at your last post, well let me say this. If I run this code and choose period 201609 when prompted:

TABLE FILE TBLMSTR_PAYCODEDETAIL
SUM
TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.ADJAMOUNT
BY LOWEST TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.SPECIALTY
ACROSS TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.PERIOD
WHERE TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.PERIOD LE &PERIOD.(FIND TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.PERIOD,TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.PERIOD IN TBLMSTR_PAYCODEDETAIL).PERIOD.;
WHERE TBLMSTR_PAYCODEDETAIL.TBLMSTR_PAYCODEDETAIL.PERIOD GT &PERIOD - 100;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS RFTMP FORMAT ALPHA
END

I end up with a HOLD file with columns:
SPECIALTTY
ADJ201510
ADJ201611
ADD20512
ADJ201601
ADJ201602
.
.
ADJ201609

These are the columns I want and they are indeed ordered correctly and even with a somewhat usuable column header/title. I can do delta defines on them:
Delta1 = ADJ201511 – ADJ201510
Delta2 = ADJ201512 – ADJ201511
Etc.

The trouble is those defines fall apart as soon as the user runs the report for a different period, say 201612…because the column names change in the HOLD File rendering the defines useless.

So..I thought we were trying to generically name the ACROSS columns so the Delta defines hold true regardless of what Period the user chooses. I think using a DECODE we could get the columns to be something like this, regardless of what value the user selects as: period:

SPECIALTY
ADJ01
ADJ02
.
.
ADJ12

Then our Delta Defines should ALWAYS work.

The trouble here is now one of labeling the **column titles** on the report. The user is not going to want to see columns labeled:

ADJ01….ADJ02…Delta01….ADJ03…Delta02………………………….ADJ12…Delta12

They want to see some indication of what the **month** is

EXAMPLE:

201510……………201511……………..Delta01…………..201512…………..Delta02………..201601…………Delta3……………….201609………..Delta12…………



So, I think I am stuck unless I missed something here somewhere. I hope this makes sense, if not I am indeed sorry for taking your time.

Let's go back to the original request:

quote:

We have a simple table with the following columnsept Period Charges.Data is stored vertically. A simple Report fex shows Dept down the page, period across and charges summed.I have been asked to show the difference in charges from one month to another.Is there a function or something that will do this? How would I do this?EX: current report lists Jul - June across the report, Depts down, charges summed. We want Jul, then August then a delta Jul - Aug, then Sept, then a delta column Aug-Sep and so on.

First of all, I think we need to dispense with the idea of formatting the data across to start with. That just make it more confusing than necessary.

It's easy enough to calculate the delta with a simple define:

DEFINE FILE XYZ
MONTH/M=PERIOD;
END

Then try:

TABLE FILE XYZ
PRINT CHARGES DELTA
BY DEPT BY MONTH
END

I think you will get something that looks like:

DEPT     MONTH    CHARGE    DELTA
 A       Jul        100.00       0
         Aug        200.00  100.00
         Sep        300.00  100.00
.
.
B       Jul         400.00       0
        Aug         500.00  100.00

Etcetera.

Not the format you want, obviously.

Now try:

DEFINE FILE XYZ
MONTH/M=PERIOD;
END

TABLE FILE XYZ
SUM CHARGES
BY DEPT BY PERIOD
ON TABLE HOLD
END

DEFINE FILE HOLD
DELTA/D12.2 = IF MONTH EQ 07 THEN 0 ELSE CHARGES - LAST CHARGES;
END

TABLE FILE HOLD
SUM CHARGES DELTA BY DEPT ACROSS MONTH
END

That gives the desired output with my data ....

Now if your months span a year end, you obviously have to handle that. Simple. Just change the DEFINE

DEFINE FILE XYZ
MONTH/MYY=PERIOD;
END

Let me just add that in posting your code it is always MUCH easier to follow if you strip out the table and segment names from your fieldnames .... My rant for today.

This message has been edited. Last edited by: George Patton,

Hi Robert,

Maybe this will put you in the right direction:

 
-SET &ECHO=ALL;
-* Here you should prompt for your end period. I put a DEFAULT for good measure
-* Assumption: PERIOD is a YYM field 
-DEFAULT &PERIOD='201611';

-* This is the calculation for the start period
-SET &SPERIOD=AYM(&PERIOD, -11, 'I6');

-* This is the value of the month of the start period
-SET &MON=EDIT(&SPERIOD, '$$$$99');

-* Some settings...
SET ASNAMES = ON
SET HOLDATTR = ON

-* Your JOIN
JOIN
INNER PERIOD IN 
TBLMSTR_PAYCODEDETAIL TO UNIQUE
PERIOD IN TBLMSTR_CALENDARPERIOD
TAG J0 AS J0
END

-* Calculating the differences between periods and HOLDing the result
TABLE FILE TBLMSTR_PAYCODEDETAIL
SUM 
ADJAMOUNT
COMPUTE DIFF/D9=IF SPECIALTY NE LAST SPECIALTY THEN ADJAMOUNT ELSE ADJAMOUNT - LAST ADJAMOUNT;
BY LOWEST SPECIALTY
BY PERIOD
WHERE PERIOD LE .
WHERE PERIOD GE &SPERIOD;
ON TABLE HOLD AS RFTMP FORMAT ALPHA
END
-RUN

-* In this DEFINE:
-*   isolating the month
-*   creating a sortfield to put the months in the right order 
DEFINE FILE RFTMP
MONTH/MTr=PERIOD;
MSORT/I2L=DECODE MONTH(&MON 1
-REPEAT #DECODE FOR &I FROM 1 TO 11;
-SET &TPERIOD=AYM(&SPERIOD, &I, 'I6');
-SET &MON=EDIT(&TPERIOD, '$$$$99');
-SET &J=&I + 1;
 &MON &J
 -#DECODE
 );
END

-* Output
TABLE FILE RFTMP
SUM ADJAMOUNT DIFF
BY SPECIALTY
ACROSS MSORT NOPRINT ACROSS MONTH
END
 

Following up on the last item in my previous post about handling periods (assumed as months) that span a year end.

As others have pointed out, if you just use months, then January 2017 will sort before July 2016, which isn't what you want.

Changing the define to:

DEFINE FILE XYZ
MONTH/MYY=PERIOD;
END

will immediately solve the problem - assuming that PERIOD is a smartdate.

If you have a bunch of charges throughout a month associated with a fieldname something like CHARGEDATE then just substitute that for PERIOD in the define, because you are going to SUM those charges in the first step anyway.

You will, however, have to figure out how to prompt the user for the start and end months. It could be a calendar control where they simply pick the first day of the beginning month and the last day of the ending month, or they could choose from a list of months and a list of years.

In the latter case you would have to concatenate the begin month+year and then convert the result to a format compatible with what is in the data. You'll need to consider if there are any months that might have zero charges.

George / Danny
Thanks..let me catch up on normal weekly stuff then try to absorb this. BTW, PERIOD is an: I11. There is no smart date. The user is prompted for an 'as of' Period. Ex: 201609. The WHERE selects period <= to this and greater than 201609 - 100...that part works.

Getting the data sorted correctly is not a problem....its getting a consistent set of column names to do defines against, then representing those column names in a meaningful way.

I'll look at your code in a bit. Thanks again.

Robert,

quote:

The WHERE selects period <= to this and greater than 201609 - 100...that part works

That's ok but beware when your user will want to have a specified number of months.

quote:

getting a consistent set of column names ... representing those column names in a meaningful way

Can you clarify this statement or give an example?

Danny,the code WORKS! I think I see what is going on. I added some formatting.

Of course now they want some changes that impact the code I think.

Where as I was prompting for a start period, now they only want to report the last 12 months _- THEY DO NOT WANT TO BE PROMPTED FOR A START PERIOD.

Selection on my end is simple as we have a variable in the calendar table called: Rolling months; all I need do is select where Rolling months >=-12...BUT..now I do not have an explicit way to set the SPERIOD and TPERIOD unless perhaps I run some sort of fex query to get take the MIN / MAX periods in the range of periods first and somehow set SPERIOD and TPERIOD..what do you think.

Also, they would like a Delta % and some formatting on the deltas, ie make font red when % Delta > then 10% or something....I'll worry about that later.

Thanks, at the very least I have a pretty neat report!

Robert,
For your &PERIOD, you can either use the &YYMD variable and extract:
-SET &PERIOD = EDIT(&YYMD, '999999');
Or you can extract the PERIOD from your calendar table
TABLE FILE calendar_table
SUM MAX.PERIOD
ON TABLE HOLD AS PMAX
END
-RUN
-READFILE PMAX
You may have to do a bit of formatting if PERIOD is an I11.
The rest should be the same.
As for the formatting of the Delta, it shouldn't be a problem.

BINGO! I have what they originally asked for.

I seem to recall WF could do conditional formatting...but can our version, WF 8?

They want me to add a delta % and if the Delta% is more than 5% and Delta$ are greater than 5K, make the font red. Is this possible?

Yes WF8 can.
Yes, possible...

btw...I finally see it...I think I only needed the code below to wrap it up...as I think you were trying to tell me! I did not need the &MON stuff...now on to cond fmtng!

.
.
.
-* Calculate the difference between periods and HOLD the result
TABLE FILE PAYCODEDETAIL
SUM
ADJAMOUNT
COMPUTE DIFF/D9=IF SPECIALTY NE LAST SPECIALTY THEN ADJAMOUNT ELSE ADJAMOUNT - LAST ADJAMOUNT;
BY LOWEST SPECIALTY
BY LOWEST RPTGROUPER
BY PERIOD
ON TABLE HOLD AS RFTMP FORMAT ALPHA
END
-RUN

TABLE FILE RFTMP
SUM
RFTMP.RFTMP.ADJAMOUNT
RFTMP.RFTMP.DIFF
BY LOWEST RFTMP.RFTMP.SPECIALTY
BY LOWEST RFTMP.RFTMP.RPTGROUPER
ACROSS LOWEST RFTMP.RFTMP.PERIOD
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
TYPE=ACROSSVALUE,
ACROSS=1,
BACKCOLOR='WHITE',
$
TYPE=REPORT,
COLUMN=N1,
SQUEEZE=6.333333,
$
TYPE=REPORT,
COLUMN=N3,
WRAP=6.000000,
$
ENDSTYLE
END

....

This message has been edited. Last edited by: RobertF,