Focal Point Banner


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
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

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  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Alignment Grid and Excel

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED]Alignment Grid and Excel
 Login/Join
 
Gold member
posted
I have a report that has the Alignment Grid in a Report Heading. When the report is run with PDF output, it shows perfectly. When I change the output to Excel, it takes all the information in a row and puts it all in the first cell. For example, I have 11 rows and 6 columns. I have data in the first row in Column 1 and Column 6. Both sets of data are then stored in A1 of the spreadsheet when exported to excel. I would like them to be stored in A1 and A6.

It appears that WebFOCUS is ignoring, or cannot render, the spotmarkers in the Alignment Grid. I have pasted some code below.

Does anyone have any ideas?

 
TABLE FILE TBL_SAMPLEBATCHES
PRINT 
     'TBL_SAMPLEBATCHES.TBL_SAMPLEBATCHES.SAMPLEBATCH_ID' NOPRINT
ON TABLE SUBHEAD
"Report To:<+0> <+0> <+0> <+0> <+0>Company Name"
"<J0.TBL_CLIENTS.RPTTO <+0> <+0> <+0> <J1.TBL_SITES.CMPADDR"
"<J0.TBL_CLIENTS.ADDRESS_L1 <+0> <+0> <+0> <J1.TBL_SITES.SITECTYPR"
"<J0.TBL_CLIENTS.CITYPROV <+0> <+0> <J1.TBL_SITES.CADTEL"
"Attention:<J2.TBL_CLIENTCONTACTS.CLNAME <+0> <+0> <J1.TBL_SITES.CADFAX"
"Date Submitted:<TBL_SAMPLEBATCHES.TBL_SAMPLEBATCHES.DATERCV <+0> <+0>Job/Project No.:<J5.TBL_PROJECTS.PRJID"
"Samples Submitted By:<J6.TBL_CLIENTCONTACTS.SUBBY <+0> <+0>COC No.:<TBL_SAMPLEBATCHES.TBL_SAMPLEBATCHES.COC"
"Samples Received By:<J7.TBL_PERSONNEL.RCVDBY <+0> <+0>P.O. Number:<J8.TBL_WORKORDERS.PO_NUMBER"
"Date Reported:<J3.TBL_REPORTS.RPTDATE <+0> <+0>Waterworks No.:<J5.TBL_PROJECTS.PROJECTNUMBER"
"Sample Matrix:<J4.TBL_SAMPLETYPES.SAMPLETYPE <+0> <+0>Quote No.:<J9.TBL_QUOTES.QUOTENUMBER"
"<DEGFINAL &TEMPERATURE<+0> <+0>Invoice To:<J2.TBL_CLIENTCONTACTS.CLNAME"
ON TABLE SUBFOOT
""
WHERE BATCHCODE EQ '&B1BATCH';
WHERE RECORDLIMIT EQ 1
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END


I have also tried changing the type of Heading as well. I have tried "Report Heading" and "Page Heading" and I get the same results.

Thanks,

Dave

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


WebFOCUS 7.7.03
Windows Web Server 2008
MS SQL Server 2000
Excel,CSV,PDF,HTML
 
Posts: 71 | Location: Kingston, ON | Registered: May 03, 2011Report This Post
Expert
posted Hide Post
Consider COLSPAN in your style sheet. Wink
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Gold member
posted Hide Post
COLSPAN doesn't seem to work. If I manually type the text in the style sheet, it does nothing. I tried by setting each Item in the Line with COLSPAN=1. Code below.

 
TYPE=TABHEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=10,
     COLSPAN=1,
     STYLE=BOLD+UNDERLINE,
     JUSTIFY=LEFT,
     WIDTH=1.500,
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=2,
     COLSPAN=1,
     JUSTIFY=LEFT,
     WIDTH=1.500,
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=3,
     COLSPAN=1,
     JUSTIFY=LEFT,
     WIDTH=1.500,
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=4,
     COLSPAN=1,
     JUSTIFY=LEFT,
     WIDTH=1.500,
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=5,
     COLSPAN=1,
     JUSTIFY=LEFT,
     WIDTH=1.500,
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=6,
     COLSPAN=1,
     SIZE=10,
     STYLE=BOLD,
     JUSTIFY=LEFT,
     WIDTH=1.500,
$


If I use the Alignment grid and click on "Cell Options" and then set the COLSPAN it deletes the rest of the cells to the right. It doesn't matter the number of columns I tell it to span.

Any other ideas?

Thanks.


WebFOCUS 7.7.03
Windows Web Server 2008
MS SQL Server 2000
Excel,CSV,PDF,HTML
 
Posts: 71 | Location: Kingston, ON | Registered: May 03, 2011Report This Post
Expert
posted Hide Post
HEADALIGN and COLSPAN


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Gold member
posted Hide Post
This still doesn't work. Maybe I'm not being specific enough.

 
HEADING
"Report To:<+0> <+0> <+0> <+0> <+0>Company Name"
"<J0.TBL_CLIENTS.RPTTO <+0> <+0> <+0> <J1.TBL_SITES.CMPADDR"
"<J0.TBL_CLIENTS.ADDRESS_L1 <+0> <+0> <+0> <J1.TBL_SITES.SITECTYPR"
"<J0.TBL_CLIENTS.CITYPROV <+0> <+0> <J1.TBL_SITES.CADTEL "
"Attention:<J2.TBL_CLIENTCONTACTS.CLNAME <+0> <+0> <J1.TBL_SITES.CADFAX"
"Date Submitted:<TBL_SAMPLEBATCHES.TBL_SAMPLEBATCHES.DATERCV <+0> <+0>Job/Project No.:<J5.TBL_PROJECTS.PRJID"
"Samples Submitted By:<J6.TBL_CLIENTCONTACTS.SUBBY <+0> <+0>COC No.:<TBL_SAMPLEBATCHES.TBL_SAMPLEBATCHES.COC"
"Samples Received By:<J7.TBL_PERSONNEL.RCVDBY <+0> <+0>P.O. Number:<J8.TBL_WORKORDERS.PO_NUMBER"
"Date Reported:<J3.TBL_REPORTS.RPTDATE <+0> <+0>Waterworks No.:<J5.TBL_PROJECTS.PROJECTNUMBER"
"Sample Matrix:<J4.TBL_SAMPLETYPES.SAMPLETYPE <+0> <+0>Quote No.:<J9.TBL_QUOTES.QUOTENUMBER"
"<DEGFINAL &TEMPERATURE<+0> <+0>Invoice To:<J2.TBL_CLIENTCONTACTS.CLNAME "


The above code is a HEADING that was setup using the Alignment Grid. The "<+0>" are spotmarkers indicating that there is an empty cell. Take line 1 in that code "Report To:<+0> <+0> <+0> <+0> <+0>Company Name". There are 6 cells here. Line 1 Item 1 has a value of "Report To:". Line 1 Item 6 has a value of "Company Name". When the output is Excel I get "Report To: Company Name" in the cell A1 If I set COLSPAN=1. If I set COLSPAN=5 then I get "Report To: Company Name" in A1 to A5. What I want to see is in cell A1 "Report To:" and in cell A6 I would to see "Company Name" with 4 blank cells between them. Excel seems to ignore the spotmarkers where there should be blank cells.

Tom, I have tried HEADALIGN already and it doesn't make a difference. Thanks.

Any other ideas?

Thanks.


WebFOCUS 7.7.03
Windows Web Server 2008
MS SQL Server 2000
Excel,CSV,PDF,HTML
 
Posts: 71 | Location: Kingston, ON | Registered: May 03, 2011Report This Post
Expert
posted Hide Post
There are no other ideas, except, DUMMY up the report. None of us have your 9 JOINs, nor the final output; use CAR or GGSALES when replicating a problem:

  
APP APPENDPATH IBISAMP
-RUN
DEFINE FILE CAR
  DUMMY/A20 = '';
END
TABLE FILE CAR
HEADING
"Report To:<+0> <+0> <+0> <+0> <+0>Company Name"
"TBL_CLIENTS.RPTTO <+0> <+0> <+0> TBL_SITES.CMPADDR"
"TBL_CLIENTS.ADDRESS_L1 <+0> <+0> <+0> TBL_SITES.SITECTYPR"
"TBL_CLIENTS.CITYPROV <+0> <+0> TBL_SITES.CADTEL "
"Attention: TBL_CLIENTCONTACTS.CLNAME <+0> <+0> TBL_SITES.CADFAX"
"Date Submitted: DATERCV <+0> <+0>Job/Project No.: TBL_PROJECTS.PRJID"
"Samples Submitted By: TBL_CLIENTCONTACTS.SUBBY <+0> <+0>COC No.: COC"
"Samples Received By: TBL_PERSONNEL.RCVDBY <+0> <+0>P.O. Number: TBL_WORKORDERS.PO_NUMBER"
"Date Reported: TBL_REPORTS.RPTDATE <+0> <+0>Waterworks No.: TBL_PROJECTS.PROJECTNUMBER"
"Sample Matrix: TBL_SAMPLETYPES.SAMPLETYPE <+0> <+0>Quote No. TBL_QUOTES.QUOTENUMBER"
"DEGFINAL TEMPERATURE<+0> <+0>Invoice To: TBL_CLIENTCONTACTS.CLNAME "
PRINT 
    DUMMY NOPRINT
    DUMMY NOPRINT
    DUMMY NOPRINT
    DUMMY NOPRINT
    DUMMY NOPRINT
    DUMMY NOPRINT
 BY COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1;
 ON TABLE PCHOLD FORMAT EXL2K
 ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=OFF, $
TYPE=HEADING, HEADALIGN=BODY, $
TYPE=DATA, WIDTH=1.5,$
ENDSTYLE
END
-EXIT


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Member
posted Hide Post
have you tried

ON TABLE SET STYLE *
TYPE = REPORT, HEADALIGN=BODY,$
 
Posts: 12 | Registered: June 08, 2010Report This Post
Gold member
posted Hide Post
quote:
Originally posted by Greg G:
have you tried

ON TABLE SET STYLE *
TYPE = REPORT, HEADALIGN=BODY,$


Yes I have tried that. It doesn't seem to work. I'm going to talk with IB about this.


WebFOCUS 7.7.03
Windows Web Server 2008
MS SQL Server 2000
Excel,CSV,PDF,HTML
 
Posts: 71 | Location: Kingston, ON | Registered: May 03, 2011Report This Post
Expert
posted Hide Post
quote:
DUMMY up the report. None of us have your 9 JOINs, nor the final output; use CAR or GGSALES when replicating a problem
That way we can better help you with a solution... Music
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Gold member
posted Hide Post
Hey Doug,

I will try this with the CAR file and use your suggestion. I will let you know when I have tried it out.

Thanks.


WebFOCUS 7.7.03
Windows Web Server 2008
MS SQL Server 2000
Excel,CSV,PDF,HTML
 
Posts: 71 | Location: Kingston, ON | Registered: May 03, 2011Report This Post
Gold member
posted Hide Post
quote:
Originally posted by Tom Flynn:
There are no other ideas, except, DUMMY up the report. None of us have your 9 JOINs, nor the final output; use CAR or GGSALES when replicating a problem:


I tried to "DUMMY" up the report using the CAR file. I found that the Alignment Grid will not remain the same size or format unless the columns that you are reporting on are "Visible". As soon as I set them to NOPRINT, the Alignment Grid changes format.

Your suggestion Tom, has led me to believe that I have to do this another way. I will have to see if I can incorporate this in with a different report without it affecting the other repot.

Thank you all for your help.

Dave


WebFOCUS 7.7.03
Windows Web Server 2008
MS SQL Server 2000
Excel,CSV,PDF,HTML
 
Posts: 71 | Location: Kingston, ON | Registered: May 03, 2011Report This Post
Virtuoso
posted Hide Post
Dave,

I wrote a couple of articles awhile ago about HTML and PDF report formatting which may be useful in this situation for Excel formatting. Take a look a the following links. Hopefully you will find them useful.

Report Design - Part I
Report Design - Part II


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Gold member
posted Hide Post
quote:
Originally posted by mgrackin:
Dave,

I wrote a couple of articles awhile ago about HTML and PDF report formatting which may be useful in this situation for Excel formatting. Take a look a the following links. Hopefully you will find them useful.

Report Design - Part I
Report Design - Part II


Thanks Mickey. I will definitely take a look. For right now I have figure out a way around it and had to add the data and Alignment Grid to a different report.

Dave


WebFOCUS 7.7.03
Windows Web Server 2008
MS SQL Server 2000
Excel,CSV,PDF,HTML
 
Posts: 71 | Location: Kingston, ON | Registered: May 03, 2011Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Alignment Grid and Excel

Copyright © 1996-2020 Information Builders