[CLOSED] CSV with ACROSS ... automatically generated heading lines.
I have a report that has two levels of ACROSS fields ... year and month.
When I generate a CSV output (using COMT), I get headings like this:
"2017 01 Total Mld.","2017 01 Gross Orders","2017 01 Gross %",
... and so forth.
These headings are accurate and they sufficiently describe the data; but I would like to make it so that there aren't the large gaps between column heading elements.
Does anybody know how this might be done?This message has been edited. Last edited by: FP Mod Chuck,
7706 gen 902; Windows 10
January 30, 2018, 02:29 PM
FP Mod Chuck
Michael
Can you post your code for us to see. Maybe see if you can reproduce it with the ggsales sample files as that will make it easier for us to help.
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats
March 16, 2018, 10:52 AM
MichaelSamuels
Sorry for the tardy reply. I thought that at my end the issue was closed ... so I didn't pursue it. But it's open again. (Since this thread is marked as closed, I wonder if I should start a new one?)
Here is the code example using ggsales.
TABLE FILE GGSALES
SUM
UNITS
BY ST
BY CITY
ACROSS CATEGORY
ACROSS PRODUCT
ON TABLE PCHOLD FORMAT COMT
END
As I indicated in my first post, this generates the ACROSS headings like this:
[B]"State","City","Coffee Capuccino Unit Sales","Coffee Espresso Unit Sales","Coffee Latte Unit Sales","Food Biscotti Unit Sales" ... (and so forth)[/B]
I can see where it's making the ACROSS headings be the length of the field ... plus a few more (2 more for the CATEGORY, and 3 more for the PRODUCT). Those fields are defined as A11 and A16, respectively.
I tried creating variable length definitions for these fields:
DEFINE FILE GGSALES
VARIABLE_LENGTH_CATEGORY/A11V = CATEGORY;
VARIABLE_LENGTH_PRODUCT/A16V = PRODUCT;
END
TABLE FILE GGSALES
SUM
UNITS
BY ST
BY CITY
ACROSS VARIABLE_LENGTH_CATEGORY
ACROSS VARIABLE_LENGTH_PRODUCT
ON TABLE PCHOLD FORMAT COMT
END
But that didn't work.
And lastly, I thought that I'd TRIM them:
DEFINE FILE GGSALES
VARIABLE_LENGTH_CATEGORY/A11V = TRIM('B',CATEGORY,11,' ',1,VARIABLE_LENGTH_CATEGORY);
VARIABLE_LENGTH_PRODUCT/A16V = TRIM('B',PRODUCT,16,' ',1,VARIABLE_LENGTH_PRODUCT);
END
TABLE FILE GGSALES
SUM
UNITS
BY ST
BY CITY
ACROSS VARIABLE_LENGTH_CATEGORY
ACROSS VARIABLE_LENGTH_PRODUCT
ON TABLE PCHOLD FORMAT COMT
END
I think that your issue is where you are assuming that VARIABLE_LENGTH_CATEGORY and VARIABLE_LENGTH_PRODUCT are "Heading". They are not. They are data. The only real "Heading" that you have are : State, City and Unit Sales. Other such as Coffee, Food, Mug, Thermos, ... are data
Look at below result. The headings are : State, City, Categ, Prod and Units
SET ASNAMES ON
DEFINE FILE GGSALES
VARIABLE_LENGTH_CATEGORY /A11V = TRIMV('B',CATEGORY,11,' ',1,VARIABLE_LENGTH_CATEGORY);
VARIABLE_LENGTH_PRODUCT /A16V = TRIMV('B',PRODUCT, 16,' ',1,VARIABLE_LENGTH_PRODUCT);
END
TABLE FILE GGSALES
SUM UNITS AS 'Units'
BY ST
BY CITY
ACROSS VARIABLE_LENGTH_CATEGORY AS 'Categ'
ACROSS VARIABLE_LENGTH_PRODUCT AS 'Prod'
-*ON TABLE PCHOLD FORMAT COMT
ON TABLE SET PAGE-NUM NOLEAD
END
Since you have use them as in an ACROSS, the COMT format place them as in the "Heading" section of its format. But you have three elements (Category + Product + Units) that are identifying the data related to this position in the file. As in a report display, the column is always taking the largest width as the minimal display width. And for an unknown reason the COMT format in this situation is also adding few spaces between each concatenate elements.
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007