Hello,

Sorry, this may be long winded, but it's hard to explain.

I have a hold file that has one field in it, which is a summed value from a previous hold file that essentially calculates the count of closed records.

Beyond that I then need to create a couple hold files to calculate some fees and expenses.

Lastly I will then join together the fee and expense hold files and then report off of those. All of that goes well...Where I'm getting hung up is the initial count of closed records, I need to somehow record that value and bring it into the final report to calculate fees and expenses per the closed record count. Being there is really nothing for me to join that hold file to the fee and expense hold files I was wondering if there was a way to set a global variable or something so it can be carried to the end. I'll post my code below, but I tried to do a -SET &&COUNTVAR = HOLDEVERYTHINGDISTOFFICE.SEG01.CLOSED_ALL_REQUESTS, which carried the variable to the end but the value was set to the text of what I tried to set "HOLDEVERYTHINGDISTOFFICE.SEG01.CLOSED_ALL_REQUESTS". I also tried to create a dummy office and attach it to the summed hold file so that I could join it in with the others, but that doesn't work because then the count value only exists for the office number I attached it to and can't seemingly be used as an individual number to do my calculates. Lastly I tried to do a define for the HOLDEXPENSES and tried to do -INCLUDE HOLDCLOSEDTOTAL, but received an error. I'm all out of ideas, anyone else with any suggestions?

TABLE FILE HOLDEVERYTHINGDISTOFFICE
SUM 
     HOLDEVERYTHINGDISTOFFICE.SEG01.CLOSED_ALL_REQUESTS
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLDCLOSEDTOTAL FORMAT FOCUS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
TABLE FILE VWSBLCEXPENSES
PRINT
     VWSBLCEXPENSES.VWSBLCEXPENSES.EXPENSE_AMOUNT
BY  LOWEST VWSBLCEXPENSES.VWSBLCEXPENSES.OFFICE
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLDEXPENSES FORMAT FOCUS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
TABLE FILE VWSBLCFEES
PRINT
     VWSBLCFEES.VWSBLCFEES.FEE_AMOUNT
BY  LOWEST VWSBLCFEES.VWSBLCFEES.OFFICE
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLDFEES FORMAT FOCUS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
JOIN
 FILE HOLDEXPENSES AT HOLDEXPENSES.SEG01.OFFICE TO MULTIPLE FILE HOLDFEES
 AT HOLDFEES.SEG01.OFFICE TAG J0 AS J0
 WHERE HOLDEXPENSES.SEG01.OFFICE EQ J0.SEG01.OFFICE;
 END
TABLE FILE HOLDEXPENSES
PRINT
     J0.SEG01.FEE_AMOUNT AS 'Fee Income'
     HOLDEXPENSES.SEG01.EXPENSE_AMOUNT AS 'Expenses'
     COMPUTE NFI/D20.2 = J0.SEG01.FEE_AMOUNT - HOLDEXPENSES.SEG01.EXPENSE_AMOUNT; AS 'Net Fee Income'
     -*need to pull in the count for closed items for division
BY  LOWEST HOLDEXPENSES.SEG01.OFFICE
WHERE ( HOLDEXPENSES.SEG01.EXPENSE_AMOUNT GT 0 ) OR ( J0.SEG01.FEE_AMOUNT GT 0 );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
 

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

If you HOLD your total count of closed records you can subsequently use -READFILE to retrieve the value as an amper variable with the same name as the original variable (plus the ampersand of course).

Another method would be to use MATCH FILE with a dummy variable - DUMMY/A1=' '; in each of the two files. That will allow you to carry the value from your inital calculation into the second part.

I'm guessing you must be using the GUI because your initial 3 HOLDs have all kinds of useless styling appended at the end. Personally I would strip it out because it's just code bloat.

If it is only one record, then I would suggest the -READFILE suggested by George.

If you really want to make your life complicated though, you can issue a WHERE based join with no WHERE -

TABLE FILE GGSALES
  SUM DOLLARS
ON TABLE HOLD AS TEMPHLD1
END
-RUN

TABLE FILE GGSALES
  SUM UNITS
      BUDUNITS
      BUDDOLLARS
   BY ST
ON TABLE HOLD AS TEMPHLD2
END
-RUN

JOIN CLEAR *
JOIN          FILE TEMPHLD2 AT ST TAG T1
  TO MULTIPLE FILE TEMPHLD1 AT DOLLARS TAG T2
END

?FF TEMPHLD2

TABLE FILE TEMPHLD2
  SUM
      DOLLARS
      BUDDOLLARS
      UNITS
      BUDUNITS
   BY ST
END
-RUN

T

Thanks for the suggestions! I'm trying to work through these. First I'm trying the readfile option. I've read up on it and have implemented what seems to be the right syntax, but I'm having some trouble. If I use single amper as it currently looks below and I go to open or run the output report it asks me to manually enter a value for CLREQS. When I use double amper and run the report I get the following error: "(FOC295) A VALUE IS MISSING FOR : &&CLREQS". I've only included the code in which I've modified from that of the original post. Going to keep working through this and then the other options, but if you see where I may be going astray please let me know.

TABLE FILE HOLDEVERYTHINGDISTOFFICE
SUM 
     HOLDEVERYTHINGDISTOFFICE.SEG01.CLOSED_ALL_REQUESTS AS 'CLREQS'
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLDCLOSEDTOTALS FORMAT FOCUS
END

-RUN
-READFILE HOLDCLOSEDTOTALS
-TYPE CLREQS IS &CLREQS
  

One other tidbit, I found one forum post where a user specified to set the FORMAT of the hold to ALPHA and I tried that to no avail as well.

And yes, in part I use GUI and in part I use the text. Hadn't gotten around to cleaning up the code yet .

Thanks,
Joe

Oh ya.... Use format ALPHA ...

And before your first TABLE put this:

SET ASNAMES=ON

Hi George,

I set the hold file format back to ALPHA and also set ASNAMES to ON at the beginning of the procedure, and still when opening the final report it asks me to manually enter a value for CLREQS. Something must be amiss, and I can't seem to find what it is.

Thanks,
Joe

quote:

Use format ALPHA

When you use -READ then you need to use FORMAT ALPHA, but using -READFILE uses the formats within the synonym.

Use SET ASNAMES = ON as George suggests and also use -DEFAULTH &CLREQS = 0 to get rid of the FOC295 error message.

T

Thank you kindly Gents, that did the trick.

So my hold with the single sum value looks like this:

TABLE FILE HOLDEVERYTHINGDISTOFFICE
SUM 
     HOLDEVERYTHINGDISTOFFICE.SEG01.CLOSED_ALL_REQUESTS AS 'CLREQS'
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLDCLOSEDTOTALS FORMAT FOCUS
END

Then just before my final table file I have this:

-DEFAULTH &CLREQS = 0
-RUN
-READFILE HOLDCLOSEDTOTALS
-TYPE CLREQS IS &CLREQS

From there in my final table file I'm able to call &CLREQS and display that value (for example in a subfoot):

ON TABLE SUBFOOT
"&CLREQS<+0> <+0> <+0> "

Now I just need to work on my math .