Getting around the maximum frame width

[Rio38]

First time poster.

I have a custom report which will be exported to an external text file. I have 31+ columns and am running into an issue with the WIDTH phrase. I set output to the external file and use the DISPLAY command to write to the file. I do not want teh report to wrap because it will be imported into Excel. I receive an error if I try to set teh width greater than 320 characters. I need at least 500 characters to fit each record on one line.

I also use WHEN AVAIL qualifiers on some of my display items so I can't simply EXPORT the records.

Does anyone know of a way around this limitation?

Thank you very much.

 

[joey.jeremiah]

hi Rio,

afaik you can use when avail with export, and you could also use a conditional operator, like, (if yes then 1 else 0 )


if the report has different ways of presenting the result, like, display, output to excel etc. separate the presentation from the query.

1. write a fill proc that fills, prefereably one, temp-table from a query, including calculations summaries etc.

2. write export to excel, display etc. proc for every presentation type

i think, using export delimiter "," ... would be the right way to go to generate a csv file.

 

[Rio38]

Thank you very much for the reply. I don't want to use an EXPORT because I want the "cell" in teh report to actually be blank if there is no data. If I use EXPORT, it simply will not export that field and when they go to import it into Excel, the fields will not be aligned properly.

Is my logic correct or am I missing something?

In other words: If I had a row of data that looked like the following in my report:

Part # Yield1 Yield2 Yield3
ABC123 .85 .92

If I use EXPORT, the following would be exported:

"ABC123",.85,.92

When, in fact, I would want:

"ABC123",.85.,,.92

 

[joey.jeremiah]

my mistake, you're right.

try this, ( if avail <table> then <field> else "" )

 

[Rio38]

my mistake, you're right.

try this, ( if avail <table> then <field> else "" )

I don't think I can use IF..THEN..ELSE statements inside a DISPLAY command.

Also, these fields are decimal fields so I can't just set them to "" outside of the DISPLAY command.

This is one of those tricky situations for which I know there is a simple solution, but I just can't put my finger on it.

 

[eddiej]

Have you tried using a 'Put' statement instead of a display statement? It functions similiar to display, but it has no default frame. The syntax is similiar to display

PUT
field1 at 1
field2 at 10 format ">>>9.99"
field3 at 25 skip.

I've had situations like this and that's how I got around it. Just remember to put the "skip" at the end of the statement. Otherwise, the next time you output data it won't automatically go to a new line. Put will pick up where it left off.

 

[Rio38]

Thanks Eddiej, but I have also tried the PUT command. This doesn't work because I am using WHEN AVAILABLE after some fields in teh DISPLAY command.

Here is my original DISPLAY block:
DISP
op_site COLUMN-LABEL "Location"
op_dept COLUMN-LABEL "Dept"
op_part COLUMN-LABEL "Part #"
pt_desc1 COLUMN-LABEL "Part Description" WHEN AVAIL pt_mstr
edate COLUMN-LABEL "Start Date"
edate1 COLUMN-LABEL "End Date"
op_wo_op COLUMN-LABEL "SEQ/!Op #"
opm_desc COLUMN-LABEL "Operation!Description" WHEN AVAIL opm_mstr
monthly_yield_pct[1] COLUMN-LABEL "Jan!Actual!Yield" WHEN monthly_yield_pct[1] <> 9999
monthly_yield_pct[2] COLUMN-LABEL "Feb!Actual!Yield" WHEN monthly_yield_pct[2] <> 9999
monthly_yield_pct[3] COLUMN-LABEL "Mar!Actual!Yield" WHEN monthly_yield_pct[3] <> 9999
monthly_yield_pct[4] COLUMN-LABEL "Apr!Actual!Yield" WHEN monthly_yield_pct[4] <> 9999
monthly_yield_pct[5] COLUMN-LABEL "May!Actual!Yield" WHEN monthly_yield_pct[5] <> 9999
monthly_yield_pct[6] COLUMN-LABEL "Jun!Actual!Yield" WHEN monthly_yield_pct[6] <> 9999
monthly_yield_pct[7] COLUMN-LABEL "Jul!Actual!Yield" WHEN monthly_yield_pct[7] <> 9999
monthly_yield_pct[8] COLUMN-LABEL "Aug!Actual!Yield" WHEN monthly_yield_pct[8] <> 9999
monthly_yield_pct[9] COLUMN-LABEL "Sep!Actual!Yield" WHEN monthly_yield_pct[9] <> 9999
monthly_yield_pct[10] COLUMN-LABEL "Oct!Actual!Yield" WHEN monthly_yield_pct[10] <> 9999
monthly_yield_pct[11] COLUMN-LABEL "Nov!Actual!Yield" WHEN monthly_yield_pct[11] <> 9999
monthly_yield_pct[12] COLUMN-LABEL "Dec!Actual!Yield" WHEN monthly_yield_pct[12] <> 9999
yearly_total_yield_pct COLUMN-LABEL "Total!Actual!Yield"
monthly_comp[1] COLUMN-LABEL "Jan!Qty!Comp" WHEN monthly_comp[1] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[2] COLUMN-LABEL "Feb!Qty!Comp" WHEN monthly_comp[2] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[3] COLUMN-LABEL "Mar!Qty!Comp" WHEN monthly_comp[3] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[4] COLUMN-LABEL "Apr!Qty!Comp" WHEN monthly_comp[4] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[5] COLUMN-LABEL "May!Qty!Comp" WHEN monthly_comp[5] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[6] COLUMN-LABEL "Jun!Qty!Comp" WHEN monthly_comp[6] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[7] COLUMN-LABEL "Jul!Qty!Comp" WHEN monthly_comp[7] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[8] COLUMN-LABEL "Aug!Qty!Comp" WHEN monthly_comp[8] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[9] COLUMN-LABEL "Sep!Qty!Comp" WHEN monthly_comp[9] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[10] COLUMN-LABEL "Oct!Qty!Comp" WHEN monthly_comp[10] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[11] COLUMN-LABEL "Nov!Qty!Comp" WHEN monthly_comp[11] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[12] COLUMN-LABEL "Dec!Qty!Comp" WHEN monthly_comp[12] <> 0 FORMAT "->,>>>,>>9"
yearly_comp COLUMN-LABEL "Total!Qty!Comp"
WITH WIDTH 320.


I tried it with the PUT command as follows adn received the following error: +--------------------------- Error ---------------------------+
| ** Unable to understand after -- "op_part pt_desc1". (247) |
| ** Unable to understand WITH phrase. (259) |
| ** ./xx002059prompt.p Could not understand line 190. (198) |
| |
| ----------------------------------------------------------- |
| <OK> |
+-------------------------------------------------------------+

PUT
op_site
op_dept
op_part
pt_desc1 WHEN AVAIL pt_mstr
edate
edate1
op_wo_op
opm_desc WHEN AVAIL opm_mstr
monthly_yield_pct[1] WHEN monthly_yield_pct[1] <> 9999
monthly_yield_pct[2] WHEN monthly_yield_pct[2] <> 9999
monthly_yield_pct[3] WHEN monthly_yield_pct[3] <> 9999
monthly_yield_pct[4] WHEN monthly_yield_pct[4] <> 9999
monthly_yield_pct[5] WHEN monthly_yield_pct[5] <> 9999
monthly_yield_pct[6] WHEN monthly_yield_pct[6] <> 9999
monthly_yield_pct[7] WHEN monthly_yield_pct[7] <> 9999
monthly_yield_pct[8] WHEN monthly_yield_pct[8] <> 9999
monthly_yield_pct[9] WHEN monthly_yield_pct[9] <> 9999
monthly_yield_pct[10] WHEN monthly_yield_pct[10] <> 9999
monthly_yield_pct[11] WHEN monthly_yield_pct[11] <> 9999
monthly_yield_pct[12] WHEN monthly_yield_pct[12] <> 9999
yearly_total_yield_pct
monthly_comp[1] WHEN monthly_comp[1] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[2] WHEN monthly_comp[2] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[3] WHEN monthly_comp[3] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[4] WHEN monthly_comp[4] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[5] WHEN monthly_comp[5] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[6] WHEN monthly_comp[6] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[7] WHEN monthly_comp[7] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[8] WHEN monthly_comp[8] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[9] WHEN monthly_comp[9] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[10] WHEN monthly_comp[10] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[11] WHEN monthly_comp[11] <> 0 FORMAT "->,>>>,>>9"
monthly_comp[12] WHEN monthly_comp[12] <> 0 FORMAT "->,>>>,>>9"
yearly_comp
SKIP.

 

[joey.jeremiah]

i think, the term is conditional operator.

here's a simple example -

find first item no-lock no-error.

display 
    ( if avail item then itemnum else 0 )
        @ itemnum.

if you don't want to use a conditional operator, you can just use a variable.

if avail then assign it the buffer field value else assign it to null etc.

 

[Rio38]

It just must not be my day today. That didn't work either. I am getting teh same error when I try to run it. It doesn't seem to like any kind of conditional in the PUT block.

 

[Casper]

Hi Rio,

If you want to make a csv file then first of all don't use display. Just make a variable which you put every time it is filled.
Put after each field a ';' to make the csv file.

The column labels you replace with a put statement of the labels to fill the first row with column labels.
(PUT stream sOut unformatted 'Location;Dept;Part#..........' etc).

e.g:

define variable cPut as character no-undo.
define variable iTmp as integer no-undo.
define stream sOut.
Output stream sOut to ........
 
/* bla bla */
/* now the pt_desc1 part */
 
assign cPut = cPut + (if available pt_mstr then pt_desc1 else '') + ';'.
/* .... */
/* now for the extents */
do iTmp = 1 to 12:
  assign cPut = cPut + (if monthly_yield_pct[iTmp] <> 9999 then string(monthly_yield_pct[iTmp]) else string(0)) + ';'.
end.
 
/* same for monthly_comp */
 
/* at the end you do */
assign cPut = trim(cPut,';').
 
Put stream sOut unformatted cPut skip.

This should do the trick.

Regards,

Casper.

 

[joey.jeremiah]

I don't think I can use IF..THEN..ELSE statements inside a DISPLAY command.

i wrote a display example since that question was about display.

@ references a field in a frame, it cannot be use in a put statement.



here's both a put and export simple example ( don't use both ! ) -

output to test.csv.

for each salesrep no-lock: /* sports2000.db */



    /* put statement */

    put unformatted
        ( if avail salesrep then monthquota[1] else 0 )
        ","
        ( if avail salesrep then monthquota[2] else 0 )
        ","
        ( if avail salesrep then monthquota[3] else 0 )
        skip.



    /* export statement */

    export delimiter ","
        monthquota[1]   when avail salesrep 
        ""              when not avail salesrep

        monthquota[2]   when avail salesrep 
        ""              when not avail salesrep

        monthquota[3]   when avail salesrep 
        ""              when not avail salesrep.

end. /* each salesrep */

output close.

like the excellent example Casper posted there's many good options and variations to choose from.

my preference, in most cases, is using export even if it requires abit of hacking.

export quotes strings etc. and it's, well, built for exporting data.

 

[it@flude.co.uk]

I've run into the same problem when producing very wide reports. Since I can't meaningfully print 300 character wide reports, I normally assume that such reports are for importing to another application, such as Excel, and treat them as such.

Generally, I use PUT rather than EXPORT when producing files for Excel, and I normally use tab-delimited TXT files rather than CSV files, as they are easier to handle.

Also, when generating reports, I normally build a Temp-Table first, then output the Temp-Table to the report. That way, I can generalise some of the routines to a certain extent and have more control on how I output the data.

If you did that, then you would build the Temp-Table using "if available" statements, then you'd go through the temp-table and use PUT or EXPORT to create the report for Excel with no need for any "when available" statements.

 

[Rio38]

Okay, I think I finally got it with everyone's help.

I used logic to determine if the user wanted to export it to an Excel output file or display it on teh screen. After determining that, I converted the numeric fields that I was previously testing in the DISPLAY block into string variables so I can output a "blank" if necessary. I then use a PUT block for the Excel output and a DISPLAY block for the terminal output.

Thank you all for your help.