topic excelXP frozen_headers='yes' in ODS and Base Reporting

excelXP frozen_headers='yes'

Peter_C — Thu, 15 Oct 2009 10:23:10 GMT

in tagsets.excelXP v1.94

seems like frozen_headers='yes' ignores the setting of embedded_titles='yes', or am I missing something?

From "doc='help' : [pre]Frozen_Headers: Default Value 'No'
Values: yes, no, true, false, number.
If 'Yes' The rows down to the bottom of the headers will be frozen when
the table data scrolls. This includes any titles created with the
embedded titles option. If a number is given, that is the row count
that will be frozen[/pre]

So I expected I could lose my "title counter" macro that works out where the column headers will come.
Unfortunately!
Only the top row is frozen.
Here is a brief demo:
ods _all_ close;
title 'demo title1' ;
title2 'frozen_headers=''yes'' ';
ods tagsets.excelxp file='demo.xml.xls' options(sheet_name='frozen_demo' autofilter='all' embedded_titles='yes' Frozen_Headers='yes') ;
proc print data= sashelp.class ;
run ;
proc sql noprint ;* obtaining the number of title lines ;[pre] select max( number)
into :n_titles separated by ' '
from sashelp.vtitle
where type='T'
;[/pre] %let head_at =%eval( 2+&n_titles) ; *proc print column headers are 2 lines after last title ;
quit ;
title2 "frozen_headers='&head_at' ";
ods tagsets.excelxp options(sheet_name='frozen_demo2' autofilter='all' embedded_titles='yes' Frozen_Headers="&head_at") ;
proc print data= sashelp.class ;
run ;
ods _all_ close;

what am I missing?
PeterC

Re: excelXP frozen_headers='yes'

Eric_SAS — Thu, 15 Oct 2009 16:36:18 GMT

This does appear to be broken.

Here are the changes to make it work. I'll have a new version out as soon as I can.

The T should be capitalized.

define event add_title_rowcounts;
/*-------------------------------------------eric-*/
/*-- Sure would be nice to be able to do this. --*/
/*-- eval $nskip ^^$skip_factor['title']; --*/
/*----------------------------------------19Aug05-*/
do /if $skip_factor['Title'];

Titles should not be unset at the end of this event...

define event embedded_title;
break /if ^$embedded_titles;
break /if ^$titles;

....

/* unset $titles */

Re: excelXP frozen_headers='yes'

Peter_C — Fri, 16 Oct 2009 22:30:49 GMT

many thanks
Running the excelbase template code with these changes resulted in the correct frozen headers, but ...
... perhaps I shouldn't expect to edit the code in the template editor.
suddenly the style became different/minimalist
and it looked like some continued (overflowed) lines joined up a little differently than in the original.
So, I'll look forward to the update, when you can fix it!

Many thanks Eric

peterC