Why is it that I can use variables from my data source in headings and footings but not as column titles? For example, this works:
ON item1 SUBHEAD
"Name: <item1"
But this does not:
PRINT
item2 AS '<item1'
-the column title comes out blank.
I realize that if 'item1' was different in each record that would be a problem, but my SQL sets it to the same value in each record. Bottom line, I want to have a variable column title based on the data I retrieve. I can use a dialog variable to do this, but you can't set a dialog variable to a value from your retrieved data.

item1 is not a variable, it is a data column, either created through a DEFINE or contained in the file being reported on. You can embed data columns in headings but not in column titles.

You can set a Dialog Manager variable to a value in the retrieved data by -READing a HOLD or SAVE file. You may have to modify your program slightly. If you're using SQL to create a HOLD file and then report from it, simply -READ the HOLD file, to make life easier, make the Column Title data column the first one and then read the file:

-READ HOLD01 &COL_TITLE1.A10.

Thanks.
Our standard template consists of:
SQL SQLMSS
EX stored_proc_name
TABLE FILE SQLOUT
ON TABLE HOLD AS #temptable
PRINT *
END
followed by
DEFINE FILE #temptable
to define some more fields using the retrieved data, and finally
TABLE FILE #temptable
to format the report.

Would I insert the -READ statement before or after the DEFINE FILE statement?
Would the syntax be -READ HOLD01 or
-READ #temptable01 ?
I presume the desired data can be in any column besides 01 as long as the right number is appended to the hold file name?
Will this in effect empty the hold file such that the data would have to be reinserted into the hold file before the TABLE FILE statement?

-READ will not empty the hold file. The file to -READ is #temptable (though I've not seen # in a hold file name before). Yes, the desired data could be in any column - I suggested the first one to make it less complicated to determine the correct number of characters to read. The -READ could be done before or after the DEFINE. Just make sure there's a -RUN before the -READ to ensure that the hold file gets created before the -READ is done.


The code would look something like this:

SQL SQLMSS
EX stored_proc_name
TABLE FILE SQLOUT
ON TABLE HOLD AS #temptable FORMAT ALPHA
PRINT *
END
-RUN

-READ #temptable &FILLER1.A104. &COLTITLE.A50.

DEFINE FILE #temptable
...
END
-RUN

TABLE FILE #temptable
PRINT FIELD1 AS '&COLTITLE'
...
END
-RUN

I've made it a habit to stick a -RUN after every END statement.

You may have to change the format of the HOLD file to ALPHA to correctly calculate how many characters to skip before the Column Title column. Changing the format should have no effect on the report.

If you have a lot of columns in the hold file before the Column Title column, may I suggest you change the code slightly - just to make it easier to isolate the desired column:

SQL SQLMSS
EX stored_proc_name
TABLE FILE SQLOUT
ON TABLE HOLD AS #temptable
PRINT *
END
-RUN

TABLE FILE #temptable
PRINT COLUMN_TITLE
WHERE RECORDLIMIT EQ 1
ON TABLE HOLD AS HOLDTEMP FORMAT ALPHA
END
-RUN

-READ HOLDTEMP &COLTITLE.A50.

DEFINE FILE #temptable
...
END
-RUN

TABLE FILE #temptable
PRINT FIELD1 AS '&COLTITLE'
...
END
-RUN

This message has been edited. Last edited by: <Maryellen>,

I had a problem like this and this is how I got around it:

DEFINE FILE CAR
<br />TITLE01/A50 = 'Country';
<br />TITLE02/A50 = 'Car';
<br />TITLE03/A50 = 'Retail ' | BODYTYPE || ' Cost';
<br />TITLE04/A50 = 'Dealer ' | BODYTYPE || ' Cost';
<br />END<br />-RUN<br />
<br />TABLE FILE CAR
<br />SUM<
br />     RETAIL_COST AS ''
<br />     DEALER_COST AS ''
<br />BY
<br />     BODYTYPE NOPRINT<
br />BY
<br />     COUNTRY AS ''<br />BY
<br />     CAR AS ''
<br />ON BODYTYPE SUBHEAD
<br />"<TITLE01<+0><TITLE02<+0>
<TITLE03<+0><TITLE04"<br />ON BODYTYPE SUBFOOT<br />" "
<br />ON TABLE PCHOLD FORMAT PDF<br />ON TABLE NOTOTAL
<br />ON TABLE SET HTMLCSS ON<br />
ON TABLE SET STYLE 
UNITS=IN,PAGESIZE='Letter',LEFTMARGIN=0.25,RIGHTMARGIN=0.25,
TOPMARGIN=0.25,BOTTOMMARGIN=0.00,SQUEEZE=ON,ORIENTATION
=LANDSCAPE,$ TYPE=REPORT,FONT='ARIAL',SIZE=8,COLOR='BLACK',BACKCOLOR='NONE',
STYLE=NORMAL,RIGHTGAP=0.00,LEFTGAP=0.00,
$<br />TYPE=REPORT,COLUMN=P1,SQUEEZE=1.00,$
<br />TYPE=REPORT,COLUMN=P2,SQUEEZE=1.00,$
<br />TYPE=REPORT,COLUMN=P3,SQUEEZE=1.50,$
<br />TYPE=REPORT,COLUMN=P4,SQUEEZE=1.50,$
<br />
<br />TYPE=SUBHEAD,BY=BODYTYPE,BORDER-BOTTOM=MEDIUM,STYLE=BOLD,$
<br />TYPE=SUBHEAD,BY=BODYTYPE,ITEM=1,POSITION=P1,JUSTIFY=LEFT,$
<br />TYPE=SUBHEAD,BY=BODYTYPE,ITEM=2,POSITION=P2,JUSTIFY=LEFT,$
<br />TYPE=SUBHEAD,BY=BODYTYPE,ITEM=3,POSITION=P3,JUSTIFY=RIGHT,$
<br />TYPE=SUBHEAD,BY=BODYTYPE,ITEM=4,POSITION=P4,JUSTIFY=RIGHT,$
<br />ENDSTYLE<br />END

You don't have to have TITLE01 or TITLE02 in the DEFINE FILE, you could just code it into the SUBHEAD.


If you want this to work in HTML or EXL2K you will need to change the stylesheet a little.

This message has been edited. Last edited by: <Mabel>,