The SAS Output Delivery System and reporting techniques

ExcelXP tagset - Formatting and Data Layout

Reply
N/A
Posts: 1

ExcelXP tagset - Formatting and Data Layout

I'm trying to format a spreadsheet using the tagset.ExcelXP tags and am having problems trying to figure out if the following is possible. The ExcelXP tagset is being used to add filters, frozen headers and multiple worksheets.

---------------1------------------------
Adding a two line header in the report that spans 9 cells for each line. I unable to find an example of this. Is it possible to add a two line embedded title to the spreasheet and specify how many columns it will span?

---------------2------------------------
Changing how the data is being placed. The output currently follows the standard format with the labels across the top and the data filling in the cells below. Is it possible to change the data layout?

We are trying to acheive something similar to the following:

Header1 (span 9 columns)
Header2 (span 9 columns)

Obs1
Label1 Value1 Label4 Value4
Label2 Value2 Label5 Value5
Label3 Value3

Obs2
Label1 Value1 Label4 Value4
Label2 Value2 Label5 Value5
Label3 Value3
.....

Instead of:

Header1
Header2
Label1 Label2 Label3 Label4 Label5
Value1 Value2 Value3 Value4 Value5
Value1 Value2 Value3 Value4 Value5
.....
SAS Employee
Posts: 95

Re: ExcelXP tagset - Formatting and Data Layout

First, anything is possible. However, the tagset does not currently do these things.

We can probably get very close just by using system titles and a table template.

If the headers are left justified, using a system title should work just like
you want. In the left justified case, no spanning is specified and Excel just
displays the text across all adjoining cells.

The table layout looks very much like a phone book type layout. I think this can be done with a table template, although I'm not the expert on this.
I'll ask our expert to take a look.

If the table template cannot do it, I can certainly make the tagset do it. But it will take a bit of work. The tagset has arrays, so it could save the column headers and the data values for each row and rearrange them accordingly. The result would be a custom Excel tagset that has this
specific behavior, it should also be possible to turn the behavior on and off.

Let me know if you want to pursue this direction. It is not likely that I could get to it until sometime in January. If you would like to attempt it yourself I would be happy to give you guidance.

Another way to do it would be to rearrange your data into a data set that reflects this shape. That might be easier than anything else.
SAS Employee
Posts: 16

Re: ExcelXP tagset - Formatting and Data Layout

Table templates won't be able to do what you want. Eric's suggestion about using titles, and rearranging the dataset is the only way that I think you would be able to do what you want.
Ask a Question
Discussion stats
  • 2 replies
  • 161 views
  • 0 likes
  • 3 in conversation