The SAS Output Delivery System and reporting techniques

How to - Make Fit Reports Widths and Heights for Tagset.ExcelXP Reports

Accepted Solution Solved
Reply
Super Contributor
Posts: 381
Accepted Solution

How to - Make Fit Reports Widths and Heights for Tagset.ExcelXP Reports

[ Edited ]

Hello everyone,

 

I have a sample code as below. I try to get my reports being a ODS Tagset.ExcelXP. However, if I get some reports  by default, their widths's seems too long. So I found a weight option to prevent this situation. This time, the reports height's seems too large. How can I prevent this situation. My desired output as below.

 

Can somebody help me, please?

 

Data AppendixRoc;
Length Range_ $ 32 Explanation $ 32;
Infile Datalines Missover Delimiter="|"; 
Input Range_ Explanation;
Datalines;
ROC|Ayirim Yok
ROC|Zayif Ayirim
ROC|Kabul Edilebilir Ayirim
ROC|Mukemmel Ayirim
ROC|Olaganustu Ayirim
;
Run;

Proc Format;
  Value $color "Ayirim Yok"="Red"
                "Zayif Ayirim"="Orange"
                "Kabul Edilebilir Ayirim"="Yellow"
				"Mukemmel Ayirim"="LightGreen"
				"Olaganustu Ayirim"="CX00B050";
Run;

Ods Tagsets.ExcelXP Path="C:\Temp" file="CAP.xml"
style=htmlblue options(zoom="80" embedded_titles='yes' sheet_interval='none' sheet_name="CAP");
PROC Report Data=AppendixRoc;
Title "ROC icin Genel Kural";
Columns Range_ Explanation;

Define Range_ /"" Display Style(Column)=Header {width=1.041666667in};
Define Explanation /"" Display style=[BackGround=$color. width=1.510416667in] ;
Run;
Title;
 
Ods Tagsets.ExcelXP Close;

Width

 

 Width.png

 

Height

 

Height.png

 

Desired

 

DesiredROC.png

 


Accepted Solutions
Solution
‎07-02-2016 05:26 AM
SAS Super FREQ
Posts: 8,721

Re: How to - Make Fit Reports Widths and Heights for Tagset.ExcelXP Reports

Hi:

  I don't think you want to specify 0. Here's the output using 10 and 20:

use_row_heights.png

 

cynthia

View solution in original post


All Replies
Grand Advisor
Posts: 17,422

Re: How to - Make Fit Reports Widths and Heights for Tagset.ExcelXP Reports

Have you tried the height options in tagsets?

 

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html

 

This is an excellent tagset reference, find the issue, and then it links to papers with examples on that topic. 

http://www.sas.com/events/cm/867226/ExcelXPPaperIndex.pdf

SAS Super FREQ
Posts: 8,721

Re: How to - Make Fit Reports Widths and Heights for Tagset.ExcelXP Reports

Hi:

  While you might have reports that cause you to need sub-options for height and/or width controls in TAGSETS.EXCELXP suboptions, I don't think this is one of those times.  I suspect your attempt to "overcontrol" the width with measurements like:

Define Range_ /"" Display Style(Column)=Header {width=1.041666667in};
Define Explanation /"" Display style=[BackGround=$color. width=1.510416667in] 

might be causing your issues. When I tried your code with slightly more reasonable widths, I got your desired output without the need for other sub-options. So I suspect something in the over-specified width caused Excel to render the rows in an undesired basis.

 

Here's my output:

desired.png

And here were the ONLY changes I made to the program:

Define Range_ /"" Display Style(Column)=Header {width=1.05in};
Define Explanation /"" Display style=[BackGround=$color. width=1.8in] ;

 

 

cynthia

Super Contributor
Posts: 381

Re: How to - Make Fit Reports Widths and Heights for Tagset.ExcelXP Reports

[ Edited ]

Hello @Reeza and @Cynthia_sas,

 

Thank you very much for trying to help me Smiley Happy

 

@Cynthia_sas, I think you are right, it can be related to "width"'s option fractions. Hovewer, the title still seems too large. what can be the reason for?

 

I checked @Reeza's web-site links and I used the Auto_Height="Yes" option but it doesn't work, the height(title) is still too large.

 


Ods Tagsets.ExcelXP Path="C:\Temp" file="CAP.xml"
style=htmlblue options(zoom="80" embedded_titles='yes' sheet_interval='none' sheet_name="CAP" AUTOFIT_HEIGHT="yes");
PROC Report Data=AppendixRoc;
Title "ROC icin Genel Kural";
Columns Range_ Explanation;

Define Range_ /"" Display Style(Column)=Header {width=1.05in};
Define Explanation /"" Display style=[BackGround=$color. width=1.8in] ;

Run;
Title;
 
Ods Tagsets.ExcelXP Close;

Thank you,

 

TitleHeight.png

Super Contributor
Posts: 394

Re: How to - Make Fit Reports Widths and Heights for Tagset.ExcelXP Reports

The Excelxp tagset (and the new ODS destination for Excel) creates titles by merging multiple cells in the row. Excel's Autoheight feature doesn't work on rows that contain merged cells. This is an Excel limitation.

 

You should be able to use the ROW_HEIGHTS keyword in the OPTIONS() option to specify a height for the title row.

Super Contributor
Posts: 381

Re: How to - Make Fit Reports Widths and Heights for Tagset.ExcelXP Reports

Hello @Tim_SAS,

 

I don't exactly understand how can I use the Row_Heights option. I tried to use the following statements but it seems it didn't work. Maybe, I used the option incorrect. Can you help me to fix the title of report.

 

Ods Tagsets.ExcelXP Path="C:\Temp" file="CAP.xml"
style=htmlblue options(zoom="80" Row_Heights="0,0,0,0" Embedded_Titles='yes' Autofit_Height="yes" sheet_interval='none' sheet_name="CAP");

 

Thank you,

Solution
‎07-02-2016 05:26 AM
SAS Super FREQ
Posts: 8,721

Re: How to - Make Fit Reports Widths and Heights for Tagset.ExcelXP Reports

Hi:

  I don't think you want to specify 0. Here's the output using 10 and 20:

use_row_heights.png

 

cynthia

Super Contributor
Posts: 381

Re: How to - Make Fit Reports Widths and Heights for Tagset.ExcelXP Reports

Thank you very much @Cynthia_sas, it worked.

 

But I don't really understand the logic behind of the values. Yes, it works when we write 0,0,0,10 but what do this values stand for? Do they stand for mm or inch or etc.?

 

I didn't understand from the following link -> https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html 

 

Do you have an another links which explain more detailed?

 

Thank you

SAS Super FREQ
Posts: 8,721

Re: How to - Make Fit Reports Widths and Heights for Tagset.ExcelXP Reports

[ Edited ]

Hi:

  if you run your code with doc='Help" in the suboption list, you will see everything about row_heights in the SAS log. All the suboptions are documented inside the template definition, so you need to use that suboption to see the full explanation.

 

  I believe that the number you specify is in points. however, I believe the number is then further manipulated, based on other option values on the font and other items, like whether you are using the merge across option for the title, or width=. So if you need a further explanation, you will need to work with Tech Support, after you use doc='Help' in the code. (in the code snippet I posted, I show the use of doc='Help' in the suboption list -- it is the first item in the list)

 

cynthia

Super Contributor
Posts: 381

Re: How to - Make Fit Reports Widths and Heights for Tagset.ExcelXP Reports

@Cynthia_sas,

 

Yes, you are totally right. You told me before, sorry. I always forget the add doc="Help" suboption.

 

Thank you very much again Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 524 views
  • 7 likes
  • 4 in conversation