bc xxl list export

™

BC XXL List Export

HE

LP

.BC

AB

AX

XL

Re lease 4 .6B

BC XXL List Export SAP AG

2 December 1999

Copyright

© Copyright 2000 SAP AG. All rights reserved.

No part of this brochure may be reproduced or transmitted in any form or for any purpose withoutthe express permission of SAP AG. The information contained herein may be changed withoutprior notice.

Some software products marketed by SAP AG and its distributors contain proprietary softwarecomponents of other software vendors.

Microsoft®, WINDOWS®, NT®, EXCEL®, Word® and SQL Server® are registered trademarks ofMicrosoft Corporation.

IBM®, DB2®, OS/2®, DB2/6000®, Parallel Sysplex®, MVS/ESA®, RS/6000®, AIX®, S/390®,AS/400®, OS/390®, and OS/400® are registered trademarks of IBM Corporation.

ORACLE® is a registered trademark of ORACLE Corporation, California, USA.

INFORMIX®-OnLine for SAP and Informix® Dynamic ServerTM

are registered trademarks ofInformix Software Incorporated.

UNIX®, X/Open®, OSF/1®, and Motif® are registered trademarks of The Open Group.

HTML, DHTML, XML, XHTML are trademarks or registered trademarks of W3C®, World WideWeb Consortium, Laboratory for Computer Science NE43-358, Massachusetts Institute ofTechnology, 545 Technology Square, Cambridge, MA 02139.

JAVA® is a registered trademark of Sun Microsystems, Inc. , 901 San Antonio Road, Palo Alto,CA 94303 USA.

JAVASCRIPT® is a registered trademark of Sun Microsystems, Inc., used under license fortechnology invented and implemented by Netscape.

SAP, SAP Logo, mySAP.com, mySAP.com Marketplace, mySAP.com Workplace, mySAP.comBusiness Scenarios, mySAP.com Application Hosting, WebFlow, R/2, R/3, RIVA, ABAP, SAPBusiness Workflow, SAP EarlyWatch, SAP ArchiveLink, BAPI, SAPPHIRE, ManagementCockpit, SEM, are trademarks or registered trademarks of SAP AG in Germany and in severalother countries all over the world. All other products mentioned are trademarks or registeredtrademarks of their respective companies.

SAP AG BC XXL List Export

December 1999 3

Icons

Icon Meaning

Caution

Example

Note

Recommendation

Syntax

Tip


4 December 1999

Contents

BC XXL List Export .............................................................................................5BC XXL List Export........................................................................................................................ 6XXL from the End User’s Viewpoint ............................................................................................ 7Overview......................................................................................................................................... 8PC Requirements........................................................................................................................... 9Availability of XXL in the R/3 System ........................................................................................ 10The XXL Selection Screen .......................................................................................................... 11The XXL List Viewer .................................................................................................................... 13List Objects .................................................................................................................................. 15Functions of the XXL List Viewer............................................................................................... 17Startup .......................................................................................................................................... 19Function Description of the Main Menu .................................................................................... 20The Report Menu ......................................................................................................................... 21The Edit Menu .............................................................................................................................. 25The View Menu............................................................................................................................. 35The Details Menu ......................................................................................................................... 41The Format Menu......................................................................................................................... 43The Options Menu ....................................................................................................................... 44Scrapbook Functions .................................................................................................................. 46The API to XXL in ABAP/4........................................................................................................... 52Parameters of the XXL_FULL_API Function Module ............................................................... 53Table HKEY .................................................................................................................................. 55Table SEMA.................................................................................................................................. 57Exceptions in the Function Modules ......................................................................................... 61Simple Cases: Module XXL_SIMPLE_API ................................................................................. 62Using XXL in R/3 Applications ................................................................................................... 63The Internal Format of XXL List Objects ................................................................................... 65


BC XXL List Export

December 1999 5

BC XXL List Export


BC XXL List Export

6 December 1999

BC XXL List ExportThe following Help Topics are available:

XXL from the End User's Viewpoint [Page 7]

List Objects [Page 15]

Functions of the XXL List Viewer [Page 17]

The API to XXL in ABAP/4 [Page 52]

The Internal Format of XXL List Objects [5]


XXL from the End User’s Viewpoint

December 1999 7

XXL from the End User’s ViewpointThis section contains the following topics:

Overview [Page 8]

PC Requirements [Page 9]

Availability of XXL in the R/3 System [Page 10]

The XXL Selection Screen [Page 11]

The XXL List Viewer [Page 13]


Overview

8 December 1999

OverviewXXL (EXtended Export of Lists) is a tool for displaying and manipulating list objects from R/3applications. XXL is based on the Microsoft Excel spreadsheet program.

The term list object is used here because the R/3 System not only transfers the list data to XXL,but also corresponding meta information, i.e. information on the list structure, the characteristics ofthe list data and explanatory texts. For more information on the structure of list objects, see ListObjects [Page 15].

The transferred meta information allows XXL to provide adequate presentation of the list objecton the spreadsheet. XXL activates special macros after the transferred data has been loaded inthe spreadsheet program or processes the transferred data using OLE Automation.

XXL has three functional areas:

• XXL defines an API ("Application Programming Interface") in the form of a functionmodule, which allows ABAP/4 programs to export list objects (data with metainformation) to the user’s PC. For further information and recommendations for includingthe API in ABAP/4 programs, please refer to The API to XXL in ABAP/4 [Page 52].

• XXL ensures that the list object is transferred to the presentation computer of the R/3user, and that Excel is opened there with that list object. The topic The Internal Formatof XXL List Objects [Page 65] describes how list objects are represented when they arepassed to Excel. An important task of the XXL function module is to generate thisrepresentation (transfer table) from the input data supplied by the ABAP/4 program.

• XXL can activate a presentation service in the spreadsheet program the user selectsafter the export.

The two presentation services offered by SAP are based on the Microsoft Excel Spreadsheetprogram:

• The so-called XXL List Viewer controls Microsoft Excel using local macros.

• Using OLE Automation to control external applications directly from ABAP/4, XXL candisplay exported data in Microsoft Excel in the form of Pivot tables.

The user also can write own macro programs to process list objects in Microsoft Excel and to callthem from XXL after the list objects have been exported.

In addition, the Lotus company offers a separate XXL presentation service for their LotusSmartsuite family of products, primarily supporting the Lotus 1-2-3 spreadsheet program. R/3data can also be transferred to other members of the Smartsuite family of products. Forexample: you can convert the representation of the exported list object into the WK1 format andstore it in Lotus Notes. If Lotus 1-2-3 is the presentation medium, macros provide special XXLfunctionality, analogous to the XXL List Viewer.


PC Requirements

December 1999 9

PC RequirementsMicrosoft Windows (3.1, 3.11, NT 3.5) is currently the only R/3 frontend plattform to use all XXLpresentation services without restrictions.

Users who run SAPGUI under OS/2 can operate Microsoft Excel (and the XXL List Viewer) underWinOS2. However, automatic startup of the List Viewer from R/3 is not possible. The user hasto store the transfer table in a PC file, start the XXL List Viewer manually and then load the PCfile.

The implementation of the OLE presentation service on Apple Macintosh will be possible in thefuture, however, the List Viewer is currently not planned to be available on Apple Macintosh.XXL can also not be used on UNIX frontends.

Microsoft Excel and/or at least one Lotus Smartsuite application is required. Microsoft ExcelVersion 4.0 is sufficient for the XXL List Viewer, however, not for XXL’s OLE version. MicrosoftExcel does not require a specific language version; however, XXL List Viewer menu names anderror messages are currently only available in German and English.

A 486 CPU and a main storage of at least 16 MB (better 32 MB) is recommended to operateSAPGUI and Microsoft Excel 5.0 in parallel.


Availability of XXL in the R/3 System

10 December 1999

Availability of XXL in the R/3 SystemCurrently, the following applications are able to transfer data using XXL:

• Executive Information System,

• Logistics information system

• ABAP/4 Query

• Line item display in Financial Accounting (customer, vendor and G/L accounts)

• Personnel Development Reporting in Human Resources

Since the above mentioned function module can be installed in ABAP/4 programs developed bythe customer, XXL can be used in user-defined applications.


The XXL Selection Screen

December 1999 11

The XXL Selection ScreenIf the user starts the XXL component in one of the applications mentioned under Availability ofXXL in the R/3 System [Page 9] (or in a customized application), XXL usually displays thefollowing selection screen:

Some of the list entries may be missing, depending on the presentation service currentlyavailable on the PC. The Excel entries, for example, require the correct installation of MicrosoftExcel. The display of Lotus entry depends on the activation of the Smartsuite XXL version on thePC (see Availability of XXL in the R/3 System [Page 10]) and so on. If the presentation servicehas been implemented during a running R/3 session, it will be available with the next logon to theR/3 System.

• SAPoffice

Instead of transferring a list object immediately to one of the presentation services, theuser can store the object at first in the SAPoffice inbox. The user can then distribute it tothe shared folders, in the personal folder or send it to other users. List objects stored inSAPoffice are of type XXL. If the user wishes to display a type XXL document inSAPoffice, the XXL mechanism is started and the user can choose a presentationservice for the list object, in this case, however, the SAPoffice entry is missing.

• Storage in PC File

This service transfers the list object to a PC file. The file name and the target directory isfreely selectable. Please note that the PC file contains the entire transfer table in theformat described in The Internal Format of XXL List Objects [Page 65]. Make sure tohave a macro perform the subsequent presentation of the data in the spreadsheet. Themacro reads and processes the PC file to be presented. The XXL List Viewer can, forexample, present the data transferred to the PC at a later time.

• Lotus Smartsuite

A so-called SAP Lotus Monitor ist started. The monitor transfers incoming data to otherSmartsuite applications as required by the user.


The XXL Selection Screen

12 December 1999

Please note that the Lotus Smartsuite presentation service is available to Lotus usersamong SAP customers. SAP delivers this tool together with the Windows version of theSAP GUI, but does not take any warranty as far as the correct functionality is concerned.SAP services (hotline, OSS, consulting,...) cover only the correct transfer of XXL listobjects to the Smartsuite presentation service within R/3 applications.

• Excel 4 / Macros

XXL activates the XXL List Viewer which is based on Microsoft Excel. The List Viewerfunctionality to present list objects is described under Functions of the XXL List Viewer[Page 17].

• Excel 5 / Pivot Table

XXL creates a pivot table in Microsoft Excel from the transferred data using OLEAutomation.

• User-defined

This option allows to call a user-defined macro in Microsoft Excel. The entry appearsthen precisely as soon as XXL finds the XXLEXIT.XLS file. XXL first searches in thecurrent working directory and then procedes to search in the XXL installation directory.The file is loaded in Excel immediately after the transfer table has been loaded. Thecustomer can put self-defined presentation services into the XXL context by using aAuto-Open routine. For an example, please refer to the XXL_EXIT.XL_ in the XXLdirectory. To activate the file, rename the file to XXL_EXIT.XLS.

Other presentation services are currently under development.


The XXL List Viewer

December 1999 13

The XXL List ViewerThe XXL List Viewer is a set of Excel 4.0 macros which turn Microsoft Excel into a specialized toolfor presenting XXL list objects. XXL adds specific functions to Excel's standard functionality andmakes these functions available in a customized menu bar and toolbar. This XXL-specificconfiguration is intended to simultaneously cut back the Excel functionality in order to prevent theuser from corrupting the consistency of the list object. This means that, for example, certaindelete operations are inactive when Excel is working in the XXL environment.

Below is a broad overview of XXL functionality for manipulating and displaying list objects. Formore details, please refer to Functions of the XXL List Viewer [Page 17].

• Sort and re-structure data

− Sort columns and rows by hierarchical key terms,

− Sort columns, insert new columns,

− Aggregate groups of rows into sums, averages,

− Selectively re-construct detailed rows from aggregated rows ("Fisheye"),

− Sort lines/line groups by selectable criteria.

• Administer objects and views

− Save the current table status or layout as a "user view" of the list object,

− Administer user views and derived objects,

− Convert objects or views to original Excel tables.

• Graphical data presentation

− create graphics using the Excel Chart Wizard,

− label graphics automatically with key values from the list object or with user-definedtexts,

− compile and administer slide shows.

XXL can save and administer the list objects on the presentation computer. If a saved list objectis subsequently imported into XXL, all the protection mechanisms and special functions will bepresent. It is also possible to export an XXL list object to the normal Excel environment. Whenyou export in this way, the list object is saved without all its meta information, so that the currentlist object status can be processed further using the unrestricted Excel functionality.

The XXL List Viewer can also be started locally by using the SAP_INT.XLM macro in MicrosoftExcel. After the SAP GUI has been installed, the macro is stored in the XXL installationdirectory.

Contrary to the representation using OLE (available with Excel Version 5.0), the XXL List Viewercan be used with both Excel 5.0 and Excel 4.0. If Excel 5.0 is used, the XXL List Viewer isrecommended only, if the user is interested in a pre-configured functionality (managing severalviews, protections against accidental deletions, graphics with autmatic legends etc.). Thepresentation options of the XXL List Viewer are also available in the OLE variant (presenting listobjects as pivot tables) and have a much better performance.


The XXL List Viewer

14 December 1999


List Objects

December 1999 15

List ObjectsList objects are files created by R/3 applications and passed to XXL. List objects contain thedata to be listed and information on the list structure and the characteristics of the data (metainformation).

Data TableThe data to be listed is presented in tabular form. The basis for this is the very general model ofa data table, the data elements of which ("business figures") are identified by vertical keys andhorizontal keys.

Both vertical and horizontal keys can be grouped in up to seven hierarchy levels. The highestkey in the hierarchy is in the top (key) row or left (key) column. Accordingly, the lowest key in thehierarchy is in the lowest (key) row or right (key) column.

By ordering the key columns and/or key rows appropriately, and by sorting its rows, it is possibleto vary the presentation of the table.

Meta InformationThe meta information for a list object is positioned physically over the data table. It is isdescribed in detail in The Internal Format of XXL List Objects [Page 65]. Below is an outline ofthe meta information in XXL list objects:

• Information on the dimension and identification of the list object:

− Protokol version,

− Table type,

− Data table size,

− Number of vertical and horizontal keys,

− List object title.

• Header and footer texts for the list object:

− Texts for online screen output, which can be displayed when required.

− Texts for printer output, which are repeated on each printed page.

• Semantic information, which relates to a column in the data table (including the keys).This information is used to control the tools for processing the data columns, forexample, illegal operations are prevented:

− Information on whether the data column contains keys or business figures.

− Information on how the data column is to be aggregated (for example, adding).

− Operations permitted for the current data column.

− Information on data attributes.

• For more detailed semantic information, please refer to The API to XXL in ABAP/4 [Page 52].


List Objects

16 December 1999


Functions of the XXL List Viewer

December 1999 17

Functions of the XXL List ViewerAs a measure for preserving the data integrity, the end user is denied direct access to a series ofExcel functions, which could cause data to be corrupted. New functions have also been createdto allow powerful operations to be performed on list objects.

To implement this functionality, the XXL list viewer has a menu bar, which is different to thestandard Excel menu bar, and its own special toolbar.

Below are some of the functions available:

• Subtotals for any number of key groups. The subtotals are inserted as highlighted rows.It is also possible to remove subtotals from the table.

• Aggregation within any number of key hierarchies. This function reduces the data table tothe subtotal rows of the selected key column.

• Expand. The Expand function reverses the effect of aggregation in full or in part. Thismeans that you can expand individual list items -in an otherwise aggregated table- totrace details.

• Sort individual data rows or groups taking into account the hierarchical structure of the table.You can use the function "Sort by Example" to get any specified series of data rows/groups.

• Swap columns. You can swap or move both data columns and key columns. Whenrelocating data columns, their hierarchical assignment is checked and illegal movementsare prevented. Swapping key columns represents a change in the key hierarchy andcauses the rows in the data table to be re-sorted automatically.

• Swap key rows. Analogous to swapping key columns, an automatic re-sort of the datatable can also be caused by swapping the key rows.

• Formulas can be entered in user-defined columns: The formula is written in a cell andthen copied to all other corresponding cells in this column ("Formula by Example"). Thisfunctionality is available separately for data rows and group rows.

• Diagram presentation. Any rectangular data ranges can be converted into diagramsdirectly. All the relevant key values are automatically included in the diagram labels.

• Diagram labeling. Diagram labels can be edited unlike the standard Excel function.

• Multi-language capability. The XXL list viewer supports the languages English andGerman, no matter which of the many language-specific versions of Excel you use.Future versions of list viewer will contain all languages supported by SAP.

This section contains the following topics:


Functions of the XXL List Viewer

18 December 1999

Startup [Page 19]

Function Description of the Main Menu [Page 20]

The Report Menu [Page 21]

The Edit Menu [Page 25]

The View Menu [Page 35]

The Details Menu [Page 41]

The Format Menu [Page 43]

The Options Menu [Page 44]

Scrapbook Functions [Page 46]


Startup

December 1999 19

StartupThe XXL list viewer can be started in three different ways:

• From an R/3 application (ABAP/4 program). The API function module ensures that Excelis started on the presentation computer and the list viewer environment is set up. Thelist object selected by the R/3 application is loaded and can be processed. After exitingthe list viewer session, control is returned to R/3.

• From a normal Excel session. To do this, open the file SAP_INT.XLM (as an Excel table) inExcel. Other, already open Excel tables can remain open. It is also possible to switchbetween the list viewer window and the windows with standard Excel tables. Afterexiting the list viewer session, control is returned to standard Excel.

• From the Windows File Manager (or Program Manager). To start Excel and load the listviewer, doubleclick on the file name SAP_INT.XLM. After exiting the list viewer session,control is returned to Windows.

In order for R/3 to be able to start the XXL list viewer on an R/3 presentationcomputer, within Excel, no automatic processing must be activated at programstartup (for example, tutorials, large add-ins, etc.).


Function Description of the Main Menu

20 December 1999

Function Description of the Main MenuIn the XXL list viewer, the user cannot access all the Excel functions, which would facilitateundesired manipulation of data. For this reason, the menus and the standard toolbars aredifferent from the default Excel settings. The illustration below shows the list viewer main menuwith the standard toolbar and a section from a typical list object imported from R/3.

The menu options of the XXL list viewer are described briefly below in the order that they appear(from left/top to right/bottom).

Most options can also be activated by clicking an icon in the standard toolbar. Where this is thecase in the example below, the standard toolbar is shown with the icon concerned enlarged.


The Report Menu

December 1999 21

The Report Menu

The Report menu corresponds to the File menu in the standard Excel package. It contains someof the familiar Excel options if they are relevant to the current context.

Program information...This function shows the version number of the installed list viewer version, for example 1.0 int.(international), and the corresponding data.

Open report...The function Open report allows you to open two groups of report files:

• Original reports. These are the list objects made available by R/3, which means the "rawdata", which has not yet been processed by the XXL list viewer.

• Edited reports. These are list objects, which come from a previous list viewer session.These reports have normally already been processed. User views and diagramscrapbooks could already have been assigned to them. The user views and scrapbookscan only be accessed if the corresponding "Edited report" is opened.

The pop-up window, which appears when you select the function "Open report", shows the twogroups of files in two separate selection lists:


The Report Menu

22 December 1999

The current directory is the directory set for the original R/3 reports when the XXL list viewer wasinstalled, or another directory set in a previous list viewer session.

Export report...During export, the XXL list viewer converts a copy of the report into the original Excel tableformat. The data can then be processed (further) using the standard Excel package. Pleasenote the following:

• The data is no longer protected when in original Excel table format. Data can thereforebe manipulated as required.

• The export cannot be reversed: A table in Excel format cannot be re-imported.

Delete report...When a report is deleted, its user views and the corresponding scrapbooks (diagrams) are alsodeleted.


The Report Menu

December 1999 23

User views can also be deleted individually. Please note that an open report (in the abovescreen "Processed report EXAMPLE.000") cannot be deleted. Only its (closed) views can bedeleted.

View pageCorresponds to the original Excel function.

Set up page...Corresponds to the original Excel function.

Print...Corresponds to the original Excel function.

Back to R/3This function is only available if the list viewer was called from R/3. Control is returned to R/3,but the list viewer is not closed. If a further list object is exported from R/3, it is then notnecessary to start the list viewer again.

ExitWhen you exit the list viewer, control is returned to the calling program level. This is normallyR/3, but can also be standard Excel or Windows.


The Report Menu

24 December 1999

You can only exit the XXL list viewer via this menu option.


The Edit Menu

December 1999 25

The Edit Menu

The Edit menu contains the main options for manipulating the list viewer table. As thesefunctions are used very frequently, they can all be selected via the standard toolbar.

Sort...

Sorting changes the order of either individual data rows or groups of data rows. A group isalways determined by identical values in a key column. You can set different criteria for sortinggroups, for example the group total or the group average.

Sorting is always performed in two steps:

1. Mark the range to sort using the mouse. All the groups of a key column in the markedrange are included in the sort. To include all the groups in the table, click the title of thecorresponding key column.

2. Call the Sort function. A pop-up window appears prompting you to set options. Here, youcan specify

− which data column contains the key to sort by (key column).

− how the sort key value for groups is to be determined (maximum/minimum within thegroup, group total, group average, number of group members).


The Edit Menu

26 December 1999

− whether to sort in ascending or descending order.

The key value "Example" is a special case. It allows a "Sort by Example", which is a sort by anyspecified sequence.

Assume you want to sort a report within the business divisions (in the column"Services") by regions such that "Western Europe" and "USA/Canada" alwaysappear at the beginning, while the other regions retain their previous sequence (seethe illustration below).

To do this, create an auxiliary column (click on "Insert column", column 34 on thescreen) and enter there "Western Europe" and "USA/Canada", the names of theregions to be sorted. Then mark the column title "Regions" and call the function"Sort". In the pop-up window, enter "34" for the data column and choose the sortterm "Example". All the regions not listed in column 34 will retain their sequenceafter the sort, whereas "Western Europe" and "USA/Canada" move to the top in allbusiness divisions.


The Edit Menu

December 1999 27

The blank rows in the help column are not processed during the sort. Therefore itdoes not matter which rows the key values are. Only their sequence is important.

Sum

In the XXL list viewer, sum is always used to mean the creation of group sums. For a particularkey column, all the rows with the same attributes are grouped and their numeric contents addedup in the data columns. The results are entered into an automatically inserted group row.

If it does not make sense to add up the contents of one of the data columns, the average,minimum, maximum or the number of values in the group can appear in the group row instead ofthe sum. The R/3 application determines this when the list object is defined.

To calculate the sum, mark a position in the key column (for example, a cell), which contains thedesired group keys, and then activate the Sum function. You can calculate sums from all the keycolumns in any order. In the illustration below, the sums were calculated for the key column"Regions".


The Edit Menu

28 December 1999

The inserted group rows appear highlighted. The user can set the highlight colors individually(Options menu).

In order to calculate an overall sum for the report, select the narrow column furthest to the left asa key column (in the illustration, column 28), which is marked with a plus sign.

Delete sum

This function removes all group rows belonging to the marked column. You can mark anywherein the key column.

Change vertical hierarchy...


The Edit Menu

December 1999 29

The sequence of the key columns (vertical keys) reflects its hierarchical order, by which thewhole table is sorted. The highest key column in the hierarchy is at the far left of the table.

Swapping the key columns is identical to changing the vertical key hierarchy. A key column,which is set "to the left", is raised in the hierarchy, and the table is re-sorted accordingly.

When you call the function "Change hierarchy vertically...", a pop-up window appears with a mapof the key columns (see the next illustration). Click the symbolical key columns in the sequenceof the desired hierarchy (from left to right). Each column can only be raised in the hierarchyonce. If necessary, you can select Repeat to return the column sequence to the original order,and then start again.

Changing the vertical key hierarchy is only possible in the initial view (cf. MenuView).


The Edit Menu

30 December 1999

Move data columns

All data columns are normally assigned to one (horizontal) hierarchy, which is represented by themulti-level column titles. The XXL list viewer protects this hierarchy from deliberate or accidentaluser manipulation.

Data columns can therefore only be moved within their immediately superior hierarchy level.

Example (see above): The columns "Sales" and "Profit" for the year '93 can be exchanged witheach other, but cannot be moved to the column area for another year.

To move a data column, first mark the title of the column to be moved, and then (holding downthe CTRL key) the title of the column, before which you want to insert the moved column. To movethe column, double click the icon "Move data column".

Connected columns or groups of columns, i.e., columns with a common title, aretreated like a single column when being moved. In the example above, you canswap the column group '92 ("Sales" and "Profit") with the column group '93 in oneoperation.

Change the horizontal hierarchy...

The sequence of key rows (horizontal keys) reflects their hierarchical order. The highest key rowin the hierarchy is uppermost in the table.

Swapping the key rows is the same as changing the horizontal key hierarchy. A key row, whichis set "upwards", is raised in the hierarchy, and the table is re-sorted accordingly.

When you call the function "Change hierarchy horizontally..." a pop-up window appears with theexact mapping of the key rows (see the next illustration). Click the symbolic key rows in thesequence of the desired hierarchy (from top to bottom).


The Edit Menu

December 1999 31

To return the row sequence to its original state, select the Repeat key and repeat the procedure.

Insert data column

This function corresponds, in principle, to the original Excel function. Mark the data column, nextto which (on the right) you want to insert the new column. You can also assign the insertedcolumn to a (horizontal) hierarchy level. When you call the function, a pop-up window appears,in which you can choose one of two options:

• insert to the selected hierarchy: The inserted column becomes integrated in the selectedhierarchy level, which means that it is then part of the group formed by the next highestkey value.

• free data column: The inserted column does not fit into the hierarchy, which means that it ison the highest hierarchy level and is set on the right edge of the table.

You can also enter a title for the inserted column in the pop-up window.


The Edit Menu

32 December 1999

If you choose the same title for the inserted data column as for the neighboring datacolumn and both belong hierarchically to the same group, you are then connectingboth columns under a common title ("connected columns"). If the horizontalhierarchy is changed, these two columns will remain together.

Delete data column

This function corresponds to the original Excel function. However, only the columns inserted bythe user can be deleted from the table.

Insert data row

This function corresponds to the original Excel function. In order to preserve data integrity, anewly inserted row does not become a permanent part of the table and is not included in the rowhierarchy. Different operations, which can cause a re-sort, will therefore remove the insertedrows.

The contents of inserted rows are not included in the group sums, etc.

Delete data row

This function corresponds to the original Excel function. However, only data rows inserted by theuser can be removed from the table.


The Edit Menu

December 1999 33

Make diagram...

The XXL list viewer uses the Excel "Chart Wizard", but its functionality goes beyond that of theChart Wizard:

• Every coherent rectangular section in the table data range is suitable for chart presentation.Row or column titles are automatically added to the chart.

• The key characteristics of all hierarchy levels are available for creating titles in the chart.The row and column titles as well as the legends can be edited subsequently in thechart.

• The table can be aggregated, for example, to display only the group totals in the chart.

• The data rows can also be subsequently re-sorted in the finished chart.

If you mark the range to be displayed and select the function "Make diagram...", the Excel ChartWizard is activated. It first creates an embedded standard chart. At the same time, the listviewer switches to the scrapbook, which means that a (new) table appears, in which thegenerated diagram is displayed.


The Edit Menu

34 December 1999

Together with the chart, the relevant data from the data table is transferred to the scrapbook:This consists of the data itself and the row and column titles of all hierarchy levels, which arenecessary for the interpretation of the data elements. The row or column titles, which are used inthe legend, are highlighted in color and can be edited in the scrapbook.

For further details on processing and maintaining charts, please refer to Scrapbook Functions[Page 46].

View scrapbook

This function takes you from the data table to the scrapbook context with separate menus and aseparate toolbar. The diagrams for the current report are collected there, sorted and processed(see Scrapbook Functions [Page 46])


The View Menu

December 1999 35

The View Menu

The View menu contains functions, that influence the way the report is displayed withoutchanging the original data.

Save user view...This function saves the current data table status to a file. You specify the view name in a pop-upwindow. The name specified does not need to conform to the naming conventions of theoperating system. User view names can be up to 30 characters long and contain specialcharacters.

Physically, a user view is saved under the name of the corresponding "Edited report" with the filename extension.Vnn. The letters nn stand for the sequential view number, for exampleEXAMPLE.V01.


The View Menu

36 December 1999

Initial view

The function "Initial view" restores the original table status from immediately after data wastransferred from R/3. Use this function if you want to return quickly to the start of processingfrom a special view or sort order.

Load user view...This function restores a user view that was saved earlier.

Please note that a user view is always linked to an "Edited report". This means that, to load aparticular user view, the corresponding report must be open.

Aggregate

The function "Aggregate" reduces the data table to the group rows (intermediate totals rows) ofthe selected key column. If these group rows do not exist, they are first created automatically.The following illustration shows a table, which was aggregated on the highest hierarchy level.


The View Menu

December 1999 37

To aggregate, mark a cell in the relevant key column and choose the function "Aggregate".

It is neither desirable nor possible to sort or hierarchically re-group an aggregatedtable. Before performing operations such as this, expand all aggregated areas (seefunctions "Expand" below and "Initial view" above), and aggregate again after re-grouping.

Expand

The function "Expand" reverses an aggregation operation on the level of the selected keycolumn. There are two alternative possibilities:


The View Menu

38 December 1999

• Global expand: If you mark a whole key column by clicking the column title (in theillustration below, column titles are in row 26), the aggregation for the whole table isundone.

• Local expand: If you click an individual key characteristic, only this row is expanded. Thismeans that the details for this group row become visible, whereas the rest of the tableremains aggregated. You can expand any number of rows individually.

For the following screen - starting with the situation shown above - "Projects" in the key"Services" (column 29) was expanded to make its regions visible. Then in the key "Regions"(column 30), the entry "USA/Canada" was expanded to show its division into individual productgroups.

Header


The View Menu

December 1999 39

This function provides information on the "Table header". This is global information on the listobject, which was passed on by R/3.

Row header on/off

This function causes the row title to be displayed or suppressed. In some aggregation or sortoperations, a title of a row may not be visible; for example, if large groups comprise more rowsthan will fit onto the screen. By marking a cell in the area of the vertical keys and calling thefunction "Row header on/off", you can make key values visible or invisible.

If you mark a whole key column (click the column title), the list viewer creates the optimumdisplay for all key values for this column (only one visible entry per group).

Fix window

After the transfer of a list object from R/3, a division of the screen display into key and datacolumns is fixed. In the data range (right half), you can navigate using a scroll bar at the bottomof the screen. Which key columns are visible in the left half, however, is controlled by the "Fixwindow".

If you position the cursor on a cell in the far left key column and choose the function "Fixwindow", the column will move off the left of the screen, and all other screen columns move alongto the left. If you click on the leftmost key column repeatedly, you will suppress all the keycolumns in order except the last one.

Position the cursor on a cell in the far right key column and then choose the function "Fixwindow". This will make visible the column furthest to the right of the currently invisible columns.You can repeat this operation until all key columns are visible.


The View Menu

40 December 1999

Zoom...

The zoom functions correspond to those of the standard Excel package. You can use the iconsin the toolbar to change the scale for the screen display in fixed steps, whereas thecorresponding menu option gives you a much more variable scale.


The Details Menu

December 1999 41

The Details Menu

Define name...This corresponds to the original Excel function. You can assign names to individual cells or cellranges in order to access these cells directly via their names instead of scrolling in the table.

Goto...This corresponds to the original Excel function. You can use this function to access particulartable areas directly (instead of scrolling in the table), which is particularly helpful when usingnames.

Find...This corresponds to the original Excel function. You can use this function to find particular cellcontents in the table.

Insert formulas...

The XXL list viewer allows you to enter formulas in cells of user-defined columns. Using thefunction "Insert formulas" you can copy a formula to all the other rows in this column. There arethree different alternatives:

• Formula in data row. A formula, that was entered in a data row, is copied to all other datarows in the column, calculated and replaced by the result.

• Formula in group row. A formula entered in a group row is copied to all other group rows(on all group levels) in its column, calculated and replaced with its result.


The Details Menu

42 December 1999

• Blank cell in group rows. If you mark a blank cell in a group cell (in the user-definedcolumn) and call the function "Insert formulas", a pop-up window appears, from whichyou can call up pre-set formulas (see illustration). The formula is calculated for all grouprows and the result is entered:

− Addition. Enters the group totals for the user-defined column in all group rows.

− Number. Enters the number of group members in all group rows.

− Average. Calculates the average value for each group.

− Minimum. Enters the minimum value of the group.

− Maximum. Enters the maximum value for the group.

− Multiplication. Calculates the product of all group members.

− Standard deviation. Calculates the standard deviation for the group (in accordancewith the definition in standard Excel).

− Variance. Calculates the variance for the group (in accordance with the definition instandard Excel).

The XXL list viewer only copies formulas to the remaining rows in the user-definedcolumn. Results already calculated are no longer formulas and are not copied.Instead they are treated a blank rows.

To copy text, enter it as a formula. Example: You want to copy the currency unit "$" to aninserted column. In any cell in this column, enter the formula:

="$"

and then call the function "Insert formula".


The Format Menu

December 1999 43

The Format Menu

All options in the Format menu correspond to standard Excel. You can also change the width ofa column directly by moving the column border while holding down the mouse button.

In addition to these formatting functions, the XXL list viewer also offers ways of copying the fontand color of a formatted cell to all other cells of the same type. These functions can be found inthe Options menu.


The Options Menu

44 December 1999

The Options Menu

Language...In a pop-up window, you can set the language for the XXL list viewer (see illustration). In version1.0, only English and German are supported. Future versions will support more languages.

Sample colorThe color of sum rows (group rows) or user-defined rows can be set individually. To do this, setthe color for one cell in a row (Format\Pattern menu) and then activate the function "Samplecolor". Rows of the same type (all totals rows or all user-defined rows) will be assigned the colorpattern.

Sample types...The typeface characteristics set for a particular row can be copied to other cells via a pop-upwindow:

• to all data rows in all data columns.

• to all group rows on all hierarchy levels.

• to all user-defined rows.


The Options Menu

December 1999 45


Scrapbook Functions

46 December 1999

Scrapbook FunctionsYou can access the scrapbook either automatically, if you are creating a diagram, or directly viathe menu options "Edit\View scrapbook". All diagrams created for a particular user view arecollected and administered in the scrapbook.

In addition to the actual diagrams, the scrapbook also contains copies of relevant data rangesfrom the list object including its row and column headings (see also the Edit menu).

A scrapbook is stored physically under the name of its corresponding "Edited report" with the fileextension.Ann. The letters nn stand for a sequential scrapbook number, for exampleEXAMPLE.A01.

The Scrapbook Menu


Scrapbook Functions

December 1999 47

View pageCorresponds to the original Excel function.

Define print areaCorresponds to the original Excel function.

Print...Corresponds to the original Excel function.

Exit scrapbook

This function closes the scrapbook and returns you to the data table (list object).

The Edit Menu: Scrapbook

The Edit menu contains some functions for administering diagrams in the scrapbook.

You can activate the diagram processing functions of standard Excel by clicking ordouble-clicking the diagram. This will activate either (for single click) the standarddiagram toolbar or (for double-click) both the standard diagram toolbar and the Excelmenu for editing diagrams.


Scrapbook Functions

48 December 1999

Sort diagrams

You can use this function to change the order of diagrams in the scrapbook. The sequence ofdiagrams is important for the sequence of the slide show.

The diagrams in the scrapbook are numbered. The numbers are assigned automatically by theprogram when the diagram is created. The numbers are in a bordered color-highlighted cellabove the row titles, which were copied from the list object. You can enter any other numbers inthese cells and re-sort the scrapbook accordingly.

Delete diagram

This function deletes the marked diagram from the scrapbook. To mark a diagram, activate(click) the cell with the diagram number. The diagram number is in a bordered color-highlightedcell above the row titles, which were copied from the list object.


Scrapbook Functions

December 1999 49

Sort data rows...

You can change the order of elements in a data range. Your sort criteria can be the values of thekey columns or key rows. The sort keys appear in the corresponding pop-up window dependingon whether you want to sort the data ranges by row or by column.

The View Menu: ScrapbookNormalView

Slideshow Zoom...


Scrapbook Functions

50 December 1999

Slide show

You can use the slide show function to view diagrams in a scrapbook sequentially. The graphicsare displayed formatted to the window size. You can use the cursor keys or the menu options toscroll back and forth.

The diagram editing functions of the standard Excel package can also be activatedfrom the slide show by double-clicking the diagram.


Scrapbook Functions

December 1999 51

Zoom...

The zoom functions correspond to those of the standard Excel package. You can use the iconsin the toolbar to change the screen display scale in fixed steps. The corresponding menu optiongives you a more variable scale.

The Format Menu: Scrapbook

All options in the Format menu correspond to the standard Excel package.


The API to XXL in ABAP/4

52 December 1999

The API to XXL in ABAP/4The function modules XXL_FULL_API and XXL_SIMPLE_API are used as APIs to XXL inABAP/4. Although XXL_SIMPLE_API calls XXL_FULL_API, it is easier to access and should besufficient for many situations. This section describes in detail the interface of the function moduleXXL_FULL_API and briefly describes the restrictions for XXL_SIMPLE_API.

This section contains the following topics:

Parameters of the XXL FULL API Function Module [Page 53]

Table HKEY [Page 55]

Table SEMA [Page 57]

Exceptions in the Function Modules [Page 61]

Simple Cases: Module XXL_SIMPLE_API [Page 62]

Using XXL in R/3-Applications [Page 63]


Parameters of the XXL_FULL_API Function Module

December 1999 53

Parameters of the XXL_FULL_API Function ModuleTable Parameters

• List data DATA, any structure

− Please note that the data table can contain both rows and columns, which are notintended to be used in the list object. You can restrict the rows to be transferred withthe parameters DATA_STARTING/ENDING_AT (see below). Which columns aretransferred and in which order they are to appear in the list object, is controlled bytable SEMA (see also Table SEMA [Page 57])

− Please note that the data table must not be transferred empty; it must always containat least one row.

• Titles of the vertical keys VKEY

Number of the key column (INT2)

Text (domain SCRTEXT_L)

• Characteristics of the horizontal keys HKEY(see also Table HKEY [Page 55])

Hierarchy level (INT2)

Number of the column (INT2)

Text (domain SCRTEXT_L)

• Semantic information SEMA

Column number (INT2)

Type (C 3, value supply)

Standard aggregattion (C 3, value supply)

Origin of the column, i.e. number of the column in DATA (INT2)

Assigned currency column (from Release 2.1B)

• Printed texts PRINT_TEXT

HeaderFooter (C, value supply: H/F)

LeftCenterRight (C, value supply: L/C/R)

Line number (INT2)

Text (C 60)

• Online texts ONLINE_TEXT

Line number (INT2)

Key text (domain SCRTEXT_L)

Value text (C 60)


Parameters of the XXL_FULL_API Function Module

54 December 1999

Scalar Input Parameters

• File name for the list object FILENAME (C 8)

• Title row HEADER_1 (C 60)

• Subtitle HEADER_2 (C 60)

• First row to be transferred in the data table DATA_STARTING_AT (N)

• Last row to be transferred in the data table DATA_ENDING_AT (N)

− By supplying the parameters DATA_STARTING/ENDING_AT it is possible to identifya range of consecutive lines in the data table. If one of the two parameters does nothave its default value, only the range defined by the two values is transferred.Please note that the range to be transferred from the table DATA must not be empty.For the same reason, the data table as a whole must never be empty.

• No. of V keys N_VRT_KEYS (N)

• No. of H keys N_HRZ_KEYS (N)

• No. of attribute columns (i.e. columns in the actual data range) N_ATT_COLS (N)

• Switch for string conversions SEMA_TYPE (C, value supply: 'blank'/X)

• Default title for storing the list object in SAPoffice (domain SO_OBJ_DES)

• Flag to determine the default processing option NO_START (C, value supply: 'blank'/X)

• Flag to suppress the selection screen NO_DIALOG (C, value supply: 'blank'/X)

− If both NO_DIALOG = ‘X’ and NO_START = ‘X’, the selection screen of XXL issuppressed. The list object will automatically be stored in SAPoffice under the titledetermined by SO_TITLE (in case SO_TITLE is empty, an artificial title will becreated). In every other combination of the two parameters (including the defaultone), the selection screen will be displayed. In this case, the value in NO_STARTwill only determine which option is given as the default when the selection screen isdisplayed for the first time, whereas NO_DIALOG will have no effect at all.

− In case XXL_FULL_API is called during background processing, it behaves as if bothNO_DIALOG = ‘X’ and NO_START = ‘X’.

ABAP/4 Dictionary SupportFor most of the above fields, special data elements and domains were entered in the ABAP/4Dictionary. The structures of the table parameters - except the table DATA - are stored in theABAP/4 Dictionary. This means that the automatic checks and other services of the ABAP/4Dictionary are available when programming the XXL connection.

The table parameter DATA is an exception. As the structure of this parameter is variable, nostructure can be assigned.

Some fields of the XXL interface reference the ABAP/4 Dictionary domain SCRTEXT_L. This isthe domain for the longest of the three standard key words, which the ABAP/4 Dictionary savesfor each data element. Referencing this domain ensures that all the key words stored in theABAP/4 Dictionary can be used as titles etc. in XXL list objects.


Table HKEY

December 1999 55

Table HKEYEach row in table HKEY sets an entry in the range of the horizontal keys. This is illustrated bythe example below.

A1

A2

B1

C1

B2

C2

B3

C3

C4

1

3

1

1

2

2

3

3

5

1

1

2

3

2

3

2

3

3

B2

C4C2

A2 A2 A2A1 A1

B3 B3 B3B1

C3 C3C1

B2

C4C2

A2A1

B3B1

C3C1

The number of data columns in the list object is 5. The contents of HKEY specified on the leftthen sets the characteristics of the horizontal flags as specified at the top right.

Because the function module automatically fills all non-set fields from the left, the definition at thelower right is passed to XXL.

Please note that columns with the same characteristics in all horizontal keys (in the example, the3rd and 4th column) of XXL are considered as connected. You should use this, for example, tolink an amount column and a related currency column.

For the contents of HKEY, the following conditions apply:

• For all rows [n,m,...] in the table, the following must apply:

n <= N_HRZ_KEYS andm <= N_ATT_COLS

• A row in the form [1,1,...] must be available

• If a row [n,m,...] is available and n < N_HRZ_KEYS applies, a row [n+1,m,...] must alsobe available


Table HKEY

56 December 1999

B2

C4

A2A1

B3B1

C2 C3C1

Non-adherence to any one of these rules triggers the exception ERROR_IN_HKEY in themodule.

In the above example, the third rule was broken: The entry below B2 should be set so that thehierarchical blocks can be built correctly.


Table SEMA

December 1999 57

Table SEMAColumn OriginOne row in table SEMA references a column in the list object to be transferred, i.e. the first row ofSEMA defines characteristics of the first vertical key, the second row defines characteristics ofthe second vertical key - or, if there is only a vertical key, the characteristics of the first datacolumn - etc. The number of rows in SEMA - not the number of columns in DATA - determinesthe number of columns in the list object.

For each individual column in the list object, you can set the field "column origin" in thecorresponding SEMA row to determine which column in the table DATA is to be transferred atthis position. If you leave the field - for example, in the n-th SEMA row - empty, the n-th columnin the data table is included as the n-th column in the list object. If the field contains the value m,the m-th column of DATA is used as the m-th column of the list object.

...

...

... 41 ...

...

... 13

2

SEMA

...

12,5 Pabc 3

...... ...

DATA

...

abcP 3

......

List object

In this example, a SEMA table with 3 rows chooses three of four columns of the DATA table fortransfer.

Data TypeThe type specifications in the table parameter SEMA is used as formatting information by theXXL macros. Up to 10 different type specifications are possible. These specifications are usedto format as follows:

• STR

no special formatting; for numbers, all significant decimal places are displayed

• Nxx

(xx = 00, 01, 02, 03 or 04) display with xx decimal places

• NUM


Table SEMA

58 December 1999

display with as many decimal places as defined in the DECIMALS specification in theR/3 ABAP/4 Dictionary.

– Please note that the R/3 ABAP/4 Dictionary only has DECIMALS specifications fortype P, DECIMALS = 0 applies for all other types.

• PCT

display as a percentage of 1. Here, only whole percentage values are used, forexample, 1.543 will be displayed as 154%

• DAT

display as a date in the form "Day.Month.Year"

• MON

display as a date in the form "Month/Year"

The conversion of values within a column in table DATA for transfer to XXL is not normally basedon the type specification in SEMA, but on the ABAP/4 data type of the column. Excel recognizestwo data types, numbers and character strings. The formats described above are normally onlyfor numbers. Types C, T and X are passed to XXL as character strings, all other types arepassed as numbers. For type D, a special conversion to a number is performed so that thesubsequent formatting (DAT or MON) correctly represents the date passed to XXL.

If you set parameter SEMA_TYPE, which is 'blank' by default, to 'X', the module will convertDATA columns of the ABAP/4 data type C into dates or numbers if the type specification inSEMA requires this. For these columns in the list object, numbers would be passed to Excel sothat a possible special format in SEMA (for example, N02, MON,...) would apply.

Forcing a conversion using the parameter SEMA_TYPE involves the risk that it may result in run-time errors, which cannot be recovered by exception handling, if the DATA columns containincorrect entries. The advantage of this procedure lies in the fact that the parameter DATA canbe held completely in data type C. This can make it easier for the application to dynamicallyreserve the internal table, which is to be passed in parameter DATA, for list objects with differentstructures.

Module XXL_FULL_API does not perform any checks for illegal combinations of ABAP/4 datatype and type specification in SEMA. This is because the effort required for such checks in themodule is not justified by the advantages. Please note that illegal combinations such as thesecan cause undesirable situations in XXL.

The main reason for this is that the standard aggregation for a column (also specified in SEMA)is only checked against the SEMA column type, not against the ABAP/4 column type. Forexample, it is possible to provoke errors in XXL by incorrectly declaring an ABAP/4 date columnas NUM and specifying addition as the standard aggregation. If, however, you select one of thecorrect date column types, DAT or MON, then ADD cannot be the standard agregation; themodule would react to this illegal combination with the exception ERROR_IN_SEMA.

The table below shows all the recommended (✔) and illegal (✖) combinations of ABAP/4 andSEMA type. Empty fields indicate that the combination does not cause errors, but does not haveany advantages over the combination recommended for the respective ABAP/4 type. For theABAP/4 data types F, P and D, there are several recommended SEMA type specifications,depending on which number or date display you prefer.


Table SEMA

December 1999 59

C, X, T DP, F I, N

NUM

Nxx

PCT

STR

DAT

MON

Please note that you can only use ABAP/4 types converted to character strings for vertical keys(C, X, or T). Type D is not permitted; under certain circumstances, the calling ABAP/4 programmust first ensure that it is converted to type C.

Amount fields from R/3 applications often require the correct format for their currency - usually acurrency-specific correction by a factor of 100 or 1000. The XXL module does this automaticallyif another column in the table is assigned as a currency column to a column in table DATA, whichcontains amounts of money in ABAP/4 format P. You must simply enter the number of the othercolumn in the field "Assigned currency column" as semantic information on the amount column intable SEMA.

Standard aggregation operationThe following standard aggregation operations are available in XXL:

• ADD

Sum of field contents (only permitted for type NUM)

• CNT

Number of aggregated rows

• MIN

field contents

• MAX

Maximum field contents

• AVG

Average field contents (only permitted for NUM and PCT)

• NOP

No aggregation, the field in the compressed row remains empty


Table SEMA

60 December 1999

• DFT

Field is used for differentiation ("DiFferenTiator"), it contains, for example, a currency, aquantity unit etc.

When working with group rows, XXL refuses to sum or aggregate rows, which have differentvalues in a DFT column. This is why DFT is also the only reasonable standard specification forvertical key columns. If a different specification is made for vertical keys or if one of the abovelist of illegal combinations of type and aggregation operation occurs, the function module triggersthe exception ERROR_IN_SEMA. This exception is also triggered if the value ranges forspecifications for type and aggregation operation are not kept to.


Exceptions in the Function Modules

December 1999 61

Exceptions in the Function ModulesApart from the exceptions ERROR_IN_HKEY and ERROR_IN_SEMA, which were explained inthe last two sections, there are several more situations, which the function moduleXXL_FULL_API can identify and report as exceptions:

• The user has cancelled processing: CANCELLED_BY_USER. This exception can onlyoccur if the XXL-specific dialogs were activated by setting the parameter NO_DIALOG ='blank'.

• The data table is too large: DATA_TOO_BIG

− The upper limits for the dimensions of table DATA are calculated using the formulas:

Number of data rows + 5 * N_HRZ_KEYS <= 16362

and

N_ATT_COLS + 5 * N_VRT_KEYS <= 234

• Number of rows SEMA is not equal to N_ATT_COLS plus N_VRT_KEYS:DIM_MISMATCH_SEMA

• DATA contains too few columns, i.e.: a column, which was defined as the original for acolumn to be transferred, is not in DATA: DIM_MISMATCH_DATA (*)

• Number of rows VKEY is not equal to N_VRT_KEYS: DIM_MISMATCH_VKEY

• File FILENAME cannot be opened or written to: FILE_OPEN_ERROR orFILE_WRITE_ERROR (*)

• The range of data rows to be transferred limited by DATA_STARTING_AT andDATA_ENDING_AT is invalid or the data table is empty: INV_DATA_RANGE (see alsoParameters of the XXL_FULL_API Function Module [Page 53])

• XXL was called without the option 'NO_START', but XXL, although available on the front-end computer, cannot be started: INV_WINSYS (*)

• XXL is not installed correctly on the front-end computer: INV_XXL (*)

Exceptions marked with an asterisk can also be recognized by the module XXL_SIMPLE_API.


Simple Cases: Module XXL_SIMPLE_API

62 December 1999

Simple Cases: Module XXL_SIMPLE_APIXXL_SIMPLE_API is sufficient for all cases, in which:

• there is only one horizontal key, i.e. no hierarchy of horizontal keys is to be built abovethe business figures.

• no special requirements exist concerning the transfer of "semantic" information on typeand standard aggregation.

• the option of restricting the rows or columns in the table parameter DATA, which are to tobe transferred, is not required.

• the option of deactivating the XXL automatic start is not required.

In the interface of XXL_SIMPLE_API the table parameter SEMA is missing as are several scalarparameters; also the table parameter COL_TEXT replaces he table parameters VKEY and HKEYof the more complex module. In table COL_TEXT, the structure of which is the same as that oftable VKEY, both can be passed: The titles of columns with vertical keys and the values for the(single) horizontal key as titles of the data columns.

XXL_SIMPLE_API uses XXL_FULL_API and therefore has to define input tables automaticallyfor parameters SEMA, VKEY and HKEY. The module derives VKEY and HKEY fromCOL_TEXT. In the SEMA parameter, it sets the type of a column on the basis of the type of thecorresponding DATA column in ABAP/4 and declares - wherever possible - the addition to be thestandard aggregation.


Using XXL in R/3 Applications

December 1999 63

Using XXL in R/3 ApplicationsThis topic aims to provide recommendations for the integration of XXL into ABAP/4 programs.The example programs XXLSTEST and XXLFTEST provide further information on the integrationof the XXL-API.

Currencies and Quantity UnitsSome comments were made on processing currencies, quantity units etc. in the introduction tothe standard aggregation "differentiate". It should be pointed out that, in this area, a relativelyuncomplicated tool such as XXL will not be able to provide an ideal solution in all cases. Forexample, conversions based on the exchange rate of the day, which are possible in R/3, are notsupported in XXL. It will frequently be advisable to look into currency and quantity problemsbefore exporting a list object within R/3.

For example, for a list which contains several different currencies, an application could advise theuser to choose one of the currencies and convert all the amounts into this currency first beforeexporting the list. A similar, but more flexible solution for the user would be to choose a currencycolumn in the list and then in each row convert all the amounts into the currency dictated by theselected currency column.

If this - or another procedure - succeeds in creating a data table, in which quantity and currencyunits are the same, there is no reason to use space for special columns. In this case, it is betterto list quantity and currency information above the columns in a separate horizontal key.

As mentioned before, it is also possible to vary the number of decimal places in an amount field -as in the R/3 System - depending on the contents of an assigned currency field. This is doneusing the currency field specification in the parameter SEMA (see also Table SEMA [Page 57]).The column containing the currency specifications must be included in parameter DATA fortransfer. If you do not want it to appear in the list object display in XXL, you can exclude it byusing the SEMA field "Column origin".

How Much Data should be Transferred?A spreadsheet in Excel can have a maximum of 16384 rows and 256 columns. The XXL macrosneed for spreadsheet representation of an XXL list object - in addition to the display area - 17 + 4* N_HRZ_KEYS rows and 17 + 4 * N_VRT_KEYS columns for administration information.Together with a reserve of 5 columns or rows for macro extensions, these make up the upperlimits for the size of XXL list objects mentioned on page 4-13.

The formula "Number of rows in the data table * Number of columns in the data table* 30 bytes" can be used to get a rough estimate of the size ot the list object file to betransferred. If the file size is greater than 0.5 MB, working with XXL becomes lesspleasant, even on the faster 486 PCs; when the file size reaches 1 MB it is definitelyno longer reasonable to work with it. A list object passed to XXL should, if possible,therefore not contain significantly more than 15,000 individual data items.Performance considerations such as these will usually prevent the above maximumvalues from being exceeded.


Using XXL in R/3 Applications

64 December 1999

For the same reasons, it is generally better to allow the user the greatest possible freedom in theR/3 application to restrict the list object to be passed to XXL. This is particularly important if verylarge data volumes are being processed in the R/3 application. For example, the ABAP/4program could present the user with a complete list, but then offer to pass only individual pagesto XXL.

A decision that has to be taken frequently when defining list objects is whether to select for thevertical keys with key values (for example, material number "4711") or with key texts (forexample, material description "Nut M12"). It would be advisable to transfer both here - if thisdoes not contradict the above size limitations for list objects: The key values offer the XXL user amore reliable sort criteria, while the key texts are better suited for printing the list object. Theobjection that a list object could become too wide for displaying online can be answered withreference to the fact that the user in XXL can interactively set the display width of columns withkey values to 0, so that they can be used for sorting without appearing in the list display.


The Internal Format of XXL List Objects

December 1999 65

The Internal Format of XXL List ObjectsAll information on a list object - data and meta data - is passed to XXL as a single file, which isknown as the list object file. This file contains the description of an Excel table (here referred toas a transfer table). It is irrelevant for the functioning of the XXL macros, which of the manyExcel-readable formats the list object has. The current version of the XXL interface uses SYLKformat; a conversion to WK1 format is being considered.

General DataThe transfer table contains some global information on the transferred data:

• Protocol version: The version described here is number 2.

• Table type: Currently only the number 1 is used.

• The total number of rows and columns, which contain information.

• The delivered texts, divided into online, header and footer texts.

• The coordinates of the area containing the semantic information described below, andthe coordinates of the display area. The upper left and the lower right cells are specifiedin the form : "<Row>,<Column>:<Row>,<Column>", for example, "4,1:6,3".

• The number of vertical and horizontal keys.

- Please note: When counting the horizontal keys, the lowest key in the hierarchy isnot included. For the list object in the example in Functions of the XXL List Viewer[Page 17], the values 3 and 1 therefore apply.

• A title for the list object, which can be two lines long.

This information must be entered as follows in cells [1,1] to [2,7] of the transfer table:

Protocol-

Version

Table type

No. of

rows

No. of

columns

No. of pairs

Online-text

--- free ---

Maximum row

Header texts

Maximum row

Footer-texts

Coordinates

Semantics

Coordinates

Display

No. of

V-keys

No. of

H-keys

Titel

Subtitel

Directly below - beginning with cell [3,1] to [3,8] - you must position print and online texts asfollows:

1. PairOnline text(left part)

2. PairOnline text(left part)

...

1. PairOnline text(right part)

2. PairOnline text(right part)

...

1. RowPrint textTop left

2. RowPrint textTop left

...

1. RowPrint textTop center

2. RowPrint textTop center

...

1. RowPrint textTop right

2. RowPrint textTop right

...

1. RowPrint textBottom left

2. RowPrint textBottom left

...

1. RowPrint textBottom cent.

2. RowPrint textBottom cent.

...

1. RowPrint textBottom right

2. RowPrint textBottom right

...



66 December 1999

Display Area and Semantic InformationThe values of the horizontal and vertical keys, the titles of the vertical keys and the businessfigures are arranged in the transfer table in the way they will be displayed by XXL - except thatthe configuration macros handle the color highlighting and grouping of the keys, as well as thekey-specific sorting of the data range. The smallest rectangular area encompassing all this isknown here as the display area; i.e. the "top left" specification in cell [2,5] (see above) specifiesthe row of the uppermost horizontal key and the column of the uppermost vertical key in thehierarchy.

Certain restrictions apply to the location of the display area, because the XXL macros needspace both above and to the left of the display area for their own administration information. Thefirst value of the top vertical key in the hierarchy must be at least 17 + 4 * V columns from theleft border and at least 17 + 4 * H rows from the top border, where V and H are the number ofvertical and horizontal keys respectively.

For each column in the display area, the transfer table records the following semantic information(see also table SEMA for possible values):

• Key attribute: Vertical keys are marked here by an "M"; Business figures are markedwith a "K".

• Standard aggregation.

• Data type.



December 1999 67

You will find the semantic information in the column it applies to. This information is locatedabove the display area and is separated from the display area and from other items ofinformation by one empty row, as shown in the above diagram.

The smallest rectangle, which contains all the semantic information, is known as the semanticarea. The coordinates of this area are entered in cell [1,5]. The semantic area always takes upexactly 5 rows.

bc xxl list export

Documents