 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Focal Point 
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED]Indenting Text in a Report that uses OVER
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED]Indenting Text in a Report that uses OVER Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED]Indenting Text in a Report that uses OVER | Login/Join |
| Guru |
I have to create a report using the OVER statement and I also need to indent the text on certain lines. The report is run using a macro template that generates an Excel report. I can't use SHOWBLANKS=ON because for some reason, unknown to me, the macro will not run when the report opens up in Excel. This report is complicated because not only do I have to indent certain lines, but I have to read in the values of a certain column from the database by using the -READ function and populate the values in the report using the -READ variable. I pasted the Car File example that I created and the output that I get. As you can see, the variables are getting read in correctly, but when the report opens up in Excel the value of the variable does not show up. If anyone has the answer as to why the values of the variables do not show up in Excel, please let me know. If I cannot get this to work, I will have to use a suggestion from Waz in a previous post where he suggests using the MARKUP function. Thanks.
-*** Here is the TABLE FILE request ***
-SET &ECHO = ALL;
TABLE FILE CAR
SUM DCOST
BY CAR
BY MODEL
WHERE RECORDLIMIT EQ 1
ON TABLE SAVE AS HLDCAR
END
-RUN
-READ HLDCAR &TYPE_CAR.A16. &SPACE.A1. &MODEL_CAR.A24.
-CLOSE HLDCAR
-TYPE &TYPE_CAR &MODEL_CAR
-SET &CAR_TYPE = CTRAN(38,' Dealer Cost for a &TYPE_CAR', 32,13,'A38');
-SET &MOD_TYPE = CTRAN(57,' Retail Cost for Model Number &MODEL_CAR', 32,13,'A57');
TABLE FILE CAR
SUM DCOST AS '&CAR_TYPE' OVER
RCOST AS '&MOD_TYPE '
WHERE RECORDLIMIT EQ 1
ON TABLE PCHOLD FORMAT EXL2K
END
-*** Here is the code from the &ECHO = ALL ***
TABLE FILE CARSUM DCOSTBY CARBY MODEL
WHERE RECORDLIMIT EQ 1
ON TABLE SAVE AS HLDCAR
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1
ALPHANUMERIC RECORD NAMED HLDCAR
0 FIELDNAME ALIAS FORMAT LENGTH
CAR CARS A16 16
MODEL MODEL A24 24
DEALER_COST DCOST D7 7
TOTAL 47
-READ HLDCAR &TYPE_CAR.A16. &SPACE.A1. &MODEL_CAR.A24.
-CLOSE HLDCAR
-TYPE JAGUAR 12XKE AUTOJAGUAR 12XKE AUTO
-SET &CAR_TYPE = CTRAN(38,' Dealer Cost for a JAGUAR ', 32,13,'A38');
-SET &MOD_TYPE = CTRAN(57,' Retail Cost for Model Number 12XKE AUTO ', 32,13,'A57');
TABLE FILE CAR
"Dealer and Retail Cost"
" "
" "
SUM DCOST AS 'Dealer Cost for a &TYPE_CAR('
OVER
RCOST AS 'Retail Cost for Model Number &MODEL_CAR*8P*3 '
WHERE RECORDLIMIT EQ 1
ON TABLE PCHOLD FORMAT EXL2K
END
This message has been edited. Last edited by: Michele Brooks, WF 8205, Windows 10 Oracle DBMS EXL07/PDF Output | ||
|
| Member |
Despite what echo would have you believe those CTRANs aren't working right. TABLE FILE CAR SUM DCOST BY CAR BY MODEL WHERE RECORDLIMIT EQ 1 ON TABLE SAVE AS HLDCAR END -RUN -READ HLDCAR &TYPE_CAR.A16. &SPACE.A1. &MODEL_CAR.A24. -CLOSE HLDCAR -SET &CAR_TYPE = CTRAN(38,' Dealer Cost for a &TYPE_CAR', 32,13,'A38'); -SET &MOD_TYPE = CTRAN(57,' Retail Cost for Model Number &MODEL_CAR', 32,13,'A57'); -TYPE &CAR_TYPE -TYPE &MOD_TYPE Either -SET &CAR_TYPE = CTRAN(38,' Dealer Cost for a &TYPE_CAR.EVAL', 32,13,'A38'); -SET &MOD_TYPE = CTRAN(57,' Retail Cost for Model Number &MODEL_CAR.EVAL', 32,13,'A57'); or -SET &CAR_TYPE = CTRAN(38,' Dealer Cost for a ' | &TYPE_CAR.QUOTEDSTRING, 32,13,'A38'); -SET &MOD_TYPE = CTRAN(57,' Retail Cost for Model Number ' | &MODEL_CAR.QUOTEDSTRING, 32,13,'A57'); should get the desired substitution. WebFOCUS 7.6.7 Linux HTML, Excel, PDF | |||
|
| Guru |
Excellent suggestion. I tested the EVAL against the CAR File and it works. In the actual report request, I have some text after the .EVAL. How do I close up the space between the text before the EVAL and the text after the EVAL. When I run my report and open it up in Excel, there are spaces between the &NW_NAME2 and the word Company. I do not know how long the values will be for the .EVAL variable each month the report is run, so I'm putting a fixed length. Once again, thank you for the awesome suggestion. -SET &DDC_NW2_NAME_TITLE = CTRAN(52,' &NW_NAME2.EVAL Company', 32,13,'A52'); WF 8205, Windows 10 Oracle DBMS EXL07/PDF Output | |||
|
| Member |
TRUNCATE will remove any trailing spaces from the first variables before you start combining them into the full strings. -READ HLDCAR &TYPE_CAR.A16. &SPACE.A1. &MODEL_CAR.A24. -SET &TYPE_CAR=TRUNCATE(&TYPE_CAR); -SET &MODEL_CAR=TRUNCATE(&MODEL_CAR); WebFOCUS 7.6.7 Linux HTML, Excel, PDF | |||
|
| Guru |
Your suggestion worked. Thanks so much. However, now I'm facing a problem with trailing characters after the word Company because the length of the field in CTRAN is less than 52. I need the length to be 52 because the length of other companies in the table will be longer than others. WF 8205, Windows 10 Oracle DBMS EXL07/PDF Output | |||
|
| Expert |
If you have text after the inserted variable, and you want a fixed lengh output rather than the truncated length then just leave the output as is - -SET &CAR_TYPE = CTRAN(38,' Dealer Cost for a &TYPE_CAR.EVAL' || (' more text'), 32,13,'A38');
-SET &MOD_TYPE = CTRAN(57,' Retail Cost for Model Number &MODEL_CAR.EVAL' || (' yet more text'), 32,13,'A57');T
| |||||||||
|
| Guru |
I am running into two problems when using the following CTRAN statement in my Car File example. First, the wording is wrapping around to the next line. I need the wording to be on one line. When I use a fixed length, if the value of the variable is shorter than the fixed length, garbage wording appears after the variable. I need to use a fixed length because the variable lengths are all different lengths. Your suggestion too, was excellent.
-SET &CAR_TYPE = CTRAN(70,' Dealer Cost for a &TYPE_CAR.EVAL' || (' Purchased at Toyota'), 32,13,'A70');
-SET &MOD_TYPE = CTRAN(76,' Retail Cost for Model Number &MODEL_CAR.EVAL' || (' Purchased at Toyota'), 32,13,'A76');
WF 8205, Windows 10 Oracle DBMS EXL07/PDF Output | |||
|
| Expert |
But you're changing spaces to carriage returns which will cause the "wrap to next line". Isn't that what you wanted? If not then why the CTRAN? Perhaps a graphical representation of what you want might help? T
| |||||||||
|
| Expert |
You're trying to create column titles with an embedded carriage-return character? I'm not sure this will work... Meanwhile, to ensure the garbage isn't tacked on to the end of the CTRAN result, you need to provide the real length of the string, something like this (where 30 and 20 are the lengths of the non-variable string):
-SET &STR_LEN = 30 + &TYPE_CAR.LENGTH + 20;
-SET &CAR_TYPE = CTRAN(&STR_LEN,' Dealer Cost for a &TYPE_CAR.EVAL' || (' Purchased at Toyota'), 32,13,'A&STR_LEN');
"A column title can contain up to five lines of text". It looks like your column titles have more than that, perhaps this is why things are not working. Also, normally, "To specify a multiple-line column title, separate each line with a comma", so try a comma instead of a carriage return, but limit them to a maximum of four (for five lines). Francis  Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Guru |
I was using the carriage return to insert blank spaces in front of certain lines, like so. I can't use SHOWBLANKS=ON because it interfers with the macro template when the report opens up in Excel. All I'm trying to do is a report that uses the OVER statement and that has certain lines indented. Thanks.
ABC Company OVER
Company 1 OVER
Sub-Company 1 OVER
Sub-Company 1a
WF 8205, Windows 10 Oracle DBMS EXL07/PDF Output | |||
|
| Expert |
But the &TYPE_CAR and &MODEL_CAR variables are static, they won't change for each data row... Francis Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Guru |
In my actual report, I'm pulling in names that are defined as character 50. There may be 5 names that I read in for the month of December. The length of the names may be as follows: Toyota Car Company (A18) Ford Car Company (A16) Chrysler Car Company (A20) Hyundai Car Company (A19) Mazda Car Company (A17) If the variable value in the CTRAN function is shorter than the CTRAN format, garbage is inserted after the text and it displays on the report. Even though the length that I defined is fixed, the actual values that I read in vary in length. I need a way to prevent the garbage from appearing after the text if the variable is shorter than the fixed length specified in CTRAN. I also need all of the text to appear on one line. Perhaps I'm using the wrong CTRAN code for what I want to do. Thanks. WF 8205, Windows 10 Oracle DBMS EXL07/PDF Output | |||
|
| Expert |
Did this help? Meanwhile, to ensure the garbage isn't tacked on to the end of the CTRAN result, you need to provide the real length of the string, something like this (where 30 and 20 are the lengths of the non-variable strings): -SET &STR_LEN = 30 + &TYPE_CAR.LENGTH + 20;
-SET &CAR_TYPE = CTRAN(&STR_LEN,' Dealer Cost for a &TYPE_CAR.EVAL' || (' Purchased at Toyota'), 32,13,'A&STR_LEN');Francis Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Guru |
Super suggestion. I used the pasted code below based on your excellent suggestion. When I look at the source code output, the correct value and length shows up, but when the report opened up in Excel, the row was blank. This is the code that I used. Unfortunately I cannot paste the confidential source code.
The x's represent the text that goes there.
-SET &STR_LEN1 = 12 + &NW_NAME1.LENGTH + 11;
-SET &DDC_NW1_RED_TITLE = CTRAN(&STR_LEN1,' &NW_NAME1.EVAL' || (' xxxxxxxxxx'), 32,13,'A&STR_LEN1');
WF 8205, Windows 10 Oracle DBMS EXL07/PDF Output | |||
|
| Expert |
I just don't think you can use embedded carriage-returns for Excel output. Francis Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Guru |
I just figured out how CTRAN will work. When doing a report that uses the OVER statement and that will be exported to Excel, the maximum CTRAN length is 47 if you want all of your text to appear on the same line. Otherwise, the text will wrap to the next line and the cell row will naturally be larger than the rest of the rows because the text for the CTRAN row takes up two rows. I used the pasted CTRAN code in my report and it works like a charm. Thanks to everyones input I now know how to use this function properly. In order for me to be able to use the CTRAN function, I would have to shorten the variable name so that it and any other text that I want to display on that row fits in that cell on the Excel report. Thanks again to all who gave me excellent suggestions for getting a complicated report done. I love Focal Point. And, to you Francis I say "An old coder never leaves a mess."
-SET &TITLE_NAME = CTRAN(47,' &NW_NAME1.EVAL' || (' Dec Losses'), 32,13,'A47');This message has been edited. Last edited by: Michele Brooks, WF 8205, Windows 10 Oracle DBMS EXL07/PDF Output | |||
|
| Member |
To explain a bit, there aren't actually carriage returns in the Excel file. While WF would normally remove leading spaces from title text, the carriage returns somehow show up as spaces in Excel. Thus, they can be used to indent without SHOWBLANKS=ON. (How Michelle discovered this I have no idea.) So you don't really need to CTRAN the whole title text, just create an indentation string to put on the beginning as needed. -SET &INDENT=CTRAN(4, ' ', 32, 13, 'A4'); -SET &CAR_TYPE=&INDENT | 'Dealer Cost for a ' | &TYPE_CAR; -SET &MOD_TYPE=&INDENT | 'Retail Cost for Model Number ' | &MODEL_CAR; However, the problem with the wrapping isn't because of the CTRAN. It's a general issue with OVER and Excel. TYPE=TITLE, WRAP=OFF,$ Will stop the wrapping, but I can't find any way to force the column to be wide enough to show all the text. I don't know the hierarchy is expressed in your data, but may I suggest something like the following based on CAR's to get the results you're looking for: -SET &INDENT=CTRAN(4, ' ', 32, 13, 'A4'); DEFINE FILE CAR TOTAL_DCOST/D22.2CM=DCOST; TOTAL_RCOST/D22.2CM=RCOST; COUNTRY_LBL/A99V=&INDENT.QUOTEDSTRING | 'Country: ' | COUNTRY | ' costs'; COUNTRY_DCOST/D22.2CM=DCOST; COUNTRY_RCOST/D22.2CM=RCOST; CAR_LBL/A99V=&INDENT.QUOTEDSTRING | &INDENT.QUOTEDSTRING | 'Car: ' | CAR | ' costs'; CAR_DCOST/D22.2CM=DCOST; CAR_RCOST/D22.2CM=RCOST; MODEL_LBL/A99V=&INDENT.QUOTEDSTRING | &INDENT.QUOTEDSTRING | &INDENT.QUOTEDSTRING | 'Model: ' | MODEL | ' costs'; MODEL_DCOST/D22.2CM=DCOST; MODEL_RCOST/D22.2CM=RCOST; END TABLE FILE CAR SUM TOTAL_DCOST NOPRINT TOTAL_RCOST NOPRINT SUM COUNTRY_DCOST NOPRINT COUNTRY_RCOST NOPRINT BY COUNTRY NOPRINT SUM CAR_DCOST NOPRINT CAR_RCOST NOPRINT BY COUNTRY NOPRINT BY CAR NOPRINT SUM MODEL_LBL AS '' MODEL_DCOST AS '' MODEL_RCOST AS '' BY COUNTRY NOPRINT BY CAR NOPRINT BY MODEL NOPRINT ON TABLE SUBHEAD " <+0>Dealer Cost<+0>Retail Cost" "Total Costs<TOTAL_DCOST<TOTAL_RCOST" ON COUNTRY SUBHEAD "<COUNTRY_LBL<COUNTRY_DCOST<COUNTRY_RCOST" ON CAR SUBHEAD "<CAR_LBL<CAR_DCOST<CAR_RCOST" ON TABLE PCHOLD FORMAT EXL2K ON TABLE SET STYLE * TYPE=REPORT, COLUMN=MODEL_LBL, WRAP=4.0,$ TYPE=TABHEADING, HEADALIGN=BODY, JUSTIFY=RIGHT,$ TYPE=TABHEADING, LINE=1, JUSTIFY=CENTER, STYLE=BOLD,$ TYPE=TABHEADING, ITEM=1, JUSTIFY=LEFT,$ TYPE=SUBHEAD, HEADALIGN=BODY, JUSTIFY=RIGHT,$ TYPE=SUBHEAD, ITEM=1, JUSTIFY=LEFT,$ ENDSTYLE END WebFOCUS 7.6.7 Linux HTML, Excel, PDF | |||
|
| Guru |
Nick thanks for the explanation. I will try your suggestion and provide feedback on the results. All I know is that this is a lot of coding for a simple report using OVER and indentation. As far as the CTRAN carriage control syntax, I think I saw an example on Focal Point of someone using the carriage return code to generate spaces. If I find it, I'll let you know. Thanks for getting me started on this daunting task. WF 8205, Windows 10 Oracle DBMS EXL07/PDF Output | |||
|
| Guru |
Nick, in looking at your suggestion more closely, I will not be able to set up my report that way. The report that I am working on uses SUM, COMPUTE, BY, and ACROSS. It has a static heading of nine lines and contains a macro template that exports the report in Excel. If the heading is not nine lines, the macro template will not work. If I use subheads or subfoots in this report, the macro will not run. If the report did not use the macro template, I may possibly be able to use your suggestion. I will a copy your example for future references. Thanks again.This message has been edited. Last edited by: Michele Brooks, WF 8205, Windows 10 Oracle DBMS EXL07/PDF Output | |||
|
| Virtuoso |
Why not change the macro logic then?  It seems like it's the one stopping your from styling the report exactly as you want it.  Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2 Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K. | |||
|
| Guru |
I am consulting on a temporary assignment and the director does not want the macro to be modified at all. The macro template was set up by a third party vendor some time prior to my tempoarary assignment. There were several macro templates created and there are tons of reports that use them. Therefore, I must work within the constraints of the macro template. WF 8205, Windows 10 Oracle DBMS EXL07/PDF Output | |||
|
| Guru |
Have you tried usint the Squeez function? WF 7.6.11 Oracle WebSphere Windows NT-5.2 x86 32bit | |||
|
| Guru |
This is what I have coded in the program
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=8,
SQUEEZE=ON,
$
WF 8205, Windows 10 Oracle DBMS EXL07/PDF Output | |||
|
| Expert |
Not StyleSheet SQUEEZE, SQUEEZ function... In Dev Studio, click on Help --> Search, type in SQUEEZ and you'll see this at the top of the list: SQUEEZ: Reducing Multiple Spaces to a Single Space Tom Flynn WebFOCUS 8.1.05 - PROD/QA DB2 - AS400 - Mainframe | |||
|
| Guru |
Michele, Go to the top of the page and do a Search on Squeez. This will give a list of posts and some examples. WF 7.6.11 Oracle WebSphere Windows NT-5.2 x86 32bit | |||
|
| Guru |
I read up on SQUEEZE. Thanks for the suggestion. I was able to go with the CTRAN function. In this particular report, I would not need to use the SQUEEZE function. Thanks to everyone for their feedback. This post is resolved. WF 8205, Windows 10 Oracle DBMS EXL07/PDF Output | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED]Indenting Text in a Report that uses OVER
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED]Indenting Text in a Report that uses OVERCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |