topic ExcelXP Page Break Question in ODS and Base Reporting

ExcelXP Page Break Question

OS2Rules — Thu, 11 Aug 2011 19:00:05 GMT

Hi All:

I am using PROC REPORT and ODS with the ExcelXP tagset (v1.85) to create a spreadsheet. I have a BY value in the PROC REPORT that I would like to generate a new printer page (not a new sheet) for each BY value, but it is not doing it for some reason - the pages are continuous when I do a "print preview" in the resuting spreadsheet.

I have read discussion: http://communities.sas.com/message/49124#49124, and Cynthia mentions that using a BY should cause the resuting spreadsheet to page for each value of the BY variable. I'm thinking that it is a ExcelXP tagset option, but which?

Here is "most" of the code....

ods tagsets.ExcelXP

options(sheet_name="DECODE=&decode"

orientation='landscape'

papersize='LETTER'

embedded_titles='yes'

embedded_footnotes='no'

ROW_REPEAT='7'

FitToPage='yes'

sheet_interval='None'

zoom='90'

Pages_FitWidth='1'

Pages_FitHeight='999'

print_footer_margin='0.25'

ROW_HEIGHT_FUDGE='1'

absolute_column_width='12,12,12,12,12,14,14,12,12,14,14,14');

proc report data=reptdata2 nowd split='*' missing

style(REPORT)={font_face=Arial font_size=9pt}

style(SUMMARY)={font_face=Arial font_size=9pt font_weight=bold};

where decode=:"&decode";

BY byline2 notsorted;

column var1

var2

var3

--- more ---

var14;

define var1 / group noprint;

define var2 / group noprint;

define var3 / display 'ISIN' style(column)={just=l};

--- more ---

define var14 / computed 'some title';

rbreak after / summarize;

compute var14;

if _break_= '_RBREAK_' then var14 = var13..sum - var12..sum;

else net = .;

endcomp;

TITLE1 j=c 'title1';

TITLE2 j=c "title2";

TITLE3 j=c "title3";

TITLE4 j=c "FOR &rptdate";

title5 #byval(byline2);

footnote '&RPage &P of &N';

RUN;

Re: ExcelXP Page Break Question

data_null__ — Thu, 11 Aug 2011 19:29:15 GMT

I'm just guessing but I don't see PageBreaks option specified in your options.

PageBreaks: Default Value 'No'

Values: yes, no, on, off.

If set to 'yes' page breaks will be inserted into the stylesheet. The

pagebreak style element will be used to define what that pagebreak looks

like. A sample style definition looks like this.

style pagebreak /

cellheight=8

foreground=black

tagattr="HorzStripe";

It is not necessary to have a style element. In it's absence a blank row

will be inserted.

ExcelXP Page Break Question

Cynthia_sas — Thu, 11 Aug 2011 21:47:57 GMT

Hi:

In that previous post, I was commenting on how things worked if you took ALL the defaults, which would put every by group on a separate worksheet and, therefore, when printing, every by group would start on a new page. Since each BY group is a separate table and since each table gets a separate worksheet, that's the example I used. I did not say anything about how paging worked if you have sheet_interval='none'.

When you use sheet_interval='none', then you are turning off the normal behavior for BY group processing. I do not think that Excel will "automatically" put a page break (from the pull down menus) unless you, manually, insert the page breaks where you want them.

In my code example, I said that IF you needed an arbitrary number of rows on every page, then you could make a "fake" break variable to use for BY group processing...still with the default behavior of every BY group on a new sheet. In which case, every BY group would PRINT PREVIEW on a separate page. You have your suboptions for FIT_WIDTH set to 1 and your FIT_PAGE set to 999. It seems to me that this runs counter to the idea of having a page break between your logical BY groups when you have sheet_interval='none'....so I don't really think that there is a way to insert page breaks into your single sheet the way you want.

But I'd recommend that you double check with Tech Support first to verify whether there is any way, using TAGSETS.EXCELXP to insert an Excel command into the ODS TAGSETS.EXCELXP XML file to cause Excel to perform page-breaking.

cynthia

ExcelXP Page Break Question

OS2Rules — Fri, 12 Aug 2011 12:50:09 GMT

Cynthia:

I think the fundimental problem is that I want Excel to something that it might no be able to do. I would like to have 1 "sheet" that contains a report split with a BY group. But - when the sheet is printed, each of the BY groups is to be printed on a new page. To compound the problem, this is actually only part of the ouput because there are additional sheets within the same Excel file created by other parts of the same program. Everything is working with the exception of the paging of this one "sheet" ....

I am not familiar enough with Excel to know whether it can even do this.

I am already in contact the Tech Support on a related issue so I'll start with them.

Thanks again.

ExcelXP Page Break Question

OS2Rules — Fri, 12 Aug 2011 12:51:49 GMT

data_null:

Doesn't this iinset a 'soft' page break, ie: a printed string within the spreadsheet where the page break shoud be?

I need a 'hard' page break so each BY group is printed on a seperate physical page.

Re: ExcelXP Page Break Question

data_null__ — Fri, 12 Aug 2011 16:14:05 GMT

Below is the XLM from EXCELXP worksheet options, with default worksheet option..

<Worksheet ss:Name="Table 1 - Detailed and or summa">
<x:WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Print>
<ValidPrinterInfo/>
<PaperSizeIndex>1</PaperSizeIndex>
<Scale>100</Scale>
<FitWidth>1</FitWidth>
<FitHeight>1</FitHeight>
<HorizontalResolution>300</HorizontalResolution>
<VerticalResolution>300</VerticalResolution>
</Print>
<Zoom>100</Zoom>
<x:PageSetup>
<PageMargins x:Left="0.08" x:Right="0.08"/>
</x:PageSetup>
</x:WorksheetOptions>

Below is EXCEL XML saved from TAGSETS.EXCELXP XMP where I put in some pagebreaks.

  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <PageMargins x:Bottom="1.25" x:Left="0.08" x:Right="0.08"/>
   </PageSetup>
   <Unsynced/>
   <Print>
    <ValidPrinterInfo/>
    <HorizontalResolution>300</HorizontalResolution>
    <VerticalResolution>300</VerticalResolution>
   </Print>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <RangeSelection>R1C1:R19C4</RangeSelection>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
  <PageBreaks xmlns="urn:schemas-microsoft-com:office:excel">
   <ColBreaks>
    <ColBreak>
     <Column>6</Column>
    </ColBreak>
   </ColBreaks>
   <RowBreaks>
    <RowBreak>
     <Row>5</Row>
    </RowBreak>
    <RowBreak>
     <Row>9</Row>
    </RowBreak>
   </RowBreaks>
  </PageBreaks>
 </Worksheet>

So all you got to do, is modify the tagset to produce the RowBreaks XML tags when the value of the BY group changes. There is good SGF paper also presented at PharmaSUG that explains how to do this. Search for it a Lex's website.