The questions about representing in BW the texts longer than 60 symbols almost disappeared in BI forums. I hope that my detailed posting with explanation of how to do it was not the least reason of it: However, the questions about formatting workbooks and representing long texts are still there. It was the main motive for writing this blog. I'm going to demonstrate how to show long texts in Business Explorer Analyzer. Also it is to be an introduction to formatting workbooks in Excel using Visual Basic for Applications (VBA) code. The code is based on the modeling scheme for long texts that I proposed in my posting. That's why I'll explain this scheme again as a prerequisite for formatting with my VBA code. As a side effect, It'll be shown how to use a flexible infosource for simultaneous upload of texts and attributes (master data). Suppose, for simplification, that we need to keep in SAP BW the long text (description) with length of 240 characters and show this text in Business Analyzer workbook. I often encountered such an approach when this long description has been cut in four parts, 60-symbol peaces each (due to the well-known limitation of SAP BW), and been kept as characteristic values of four infoobjects, type of CHAR 60. I don't like this approach, because:
- it's difficult to display this long text in queries and workbooks as a whole
- it's impossible to have such long descriptions time or language dependent
- it's somewhat slower to retrieve from the database long alphanumeric keys.
- there are some problems with lowercase and uppercase in reports.
All this is enough for me to come up with another approach. I also use four infoobjects (hereinafter referred to as IOs). But, the peaces of the long text are kept in the long texts of the IOs, not in their characteristic values (keys). We will create three IOs and assign them as attributes of the one, basic (master) infoobject. The advantage of this - we don't have to pass to the infoprovider all four IOs. We load in there just one, master infoobject. I will demonstrate it on the example of the long description of foreign trade goods groups. The picture below shows an Excel file prepared for data load. Notice, that some description lengths definitely exceed 60 characters.
So, here we go. First, we create an infoobject for the 2nd part of the description. There is no need to create it as CHAR 60, because we will keep the peaces of the long description (60 symbols each) in the long texts, totaling 240 characters maximum length. It is even more convenient to determine each IO as NUMC 2 (the natural code of the goods group).
Since we are not going to use the peaces of the long description for displaying separately, sorting or analyzing; we declare them as "Attribute Only". Uncheck "With master data" flag since our attributes will not have their own attributes. And you see also that our texts are going to be language-dependent.
Create the 3rd and 4th parts, ZGROUP_3 and ZGROUP_4 infoobjects similarly. Finally, create the master/basis infoobject ZGROUP_1 which will have all the three created earlier infoobjects as attributes. The master IO has the same type and features, except the following:
- "Attribute Only" flag unchecked
- "With master data" flag is checked In the "Attributes" tab of the infoobject include all the three created IOs.
- Activate it. Create an infosource with the shown below IOs. Assign the flat file source system. In case of flat file source system, the transfer structure (hereinafter referred to as "TS") and transfer rules are generated automatically by the system which uses the communication structure (hereinafter referred to as "CS") as a template. Hence, the sequence of fields in the CS is important.
- In order to load four peaces of the long description we need to have four infoobjects of CHAR 60 type. We'll not need them after load. That's why we are not creating such IOs. We use the system standard infoobjects like 0ADDR_LINEx (or similar) instead. Notice, that in the TS we increased the length of the 0ADDR_LINE1 infoobject to 240 symbols. It is allowed in the TS. Doing so, we can upload just three fields, as has been shown in the first picture. The 3rd field will carry the whole 240-symbol long description. The rest of fields are to be empty. Create a start routine in transfer rules. Place there the following code.
- This code cuts the three peaces from the long description and put them into 0ADDR_LINE2, 0ADDR_LINE3 and 0ADDR_LINE4. Pay attention to that there is no need to do it for the first peace. The 0ADDR_LINE1 infoobject will accept the whole 240-symbol long text, which will be truncated to 60 characters in update rules (hereinafter referred to as "URs").
- Recall the data flow during the load: TS (PSA table) -> Start routine in Transfer Rules (if any) -> Transfer rules -> CS -> Start routine in URs (if any) -> URs of the Data Target. Behold that in the Transfer Rules incoming from the source system fields values are transferred into CS' fields as 1:1. But, our flat file has one column with 240-symbol description only. The following three columns are empty. It doesn't matter because our start routine works out before the Transfer Rules. The routine will provide the dissected texts for the last three fields (as if they came from the source system itself). There are two options. 1. If you have a CSV-file with all four peaces (60-symbol each) of the description placed in the separate columns, you may not increase the length of the ZGROUP_1 length to 240 symbols and not use the start routine. 2. Instead of start routine in transfer rules we may use routines for the three attributes. The code in them is the following. RESULT = TRAN_STRUCTURE-ADDR_LINE1+60(60) - for 0ADDR_LINE2. RESULT = TRAN_STRUCTURE-ADDR_LINE1+120(60). - for 0 ADDR_LINE3. RESULT = TRAN_STRUCTURE-ADDR_LINE1+180(60). - for 0 ADDR_LINE4. Incoming into 0ADDR_LINE1 240-symbol text will be truncated automatically to 60 characters. Since we use a flexible infosource for simultaneous load of texts and attributes, we need to insert our IOs as data targets. We do it in Infoproviders area of RSA1.
Insert all four infoobjects. Refresh the screen. After that we see five data targets, four for texts and one - for attributes. 
- Create URs for the attribute data target (ZGROUP_1 infoobject) based on the infosource we've been created.
- We see that rules for the attributes are grayed out. Click on either of grayed out crosses.
Choose "Overwrite" as Update type, and "Source Field" as Update Method. Select ZGROUP_1 infoobject as the source field. 
Click on the "Key Fields" tab to make sure that the source field for the Key Field has an assignment as shown below. 
Repeat the same procedure for all attributes. Now all grayed out crosses should look like green triangles. Activate the rules.
Create URs for the text data target of ZGROUP_1. Click on the grayed out cross.
Again, choose "Overwrite" and "Source Field". Make sure that you choose 0ADDR_LINE1 infoobject as a source field.
The "Key Fields" tab will have two infoobjects. Leave them as they are.
Repeat creation of URs for ZGROUP_2, choosing 0ADDR_LINE2 as a source field.
One of the rows in the "Key Fields" tab will have the blank assignment.
Choose ZGROUP_1 (not ZGROUP_2!) as a source.
Repeat the same for the rest of the text data targets, selecting appropriate infoobject for the source field of Update Method and ZGROUP_1 as the source for the Key Field. Create an infopackage. Make sure that you see in the "Data Targets" tab all five targets, four for texts, and one - for attributes.
Load data. In the Infoobjects area of RSA1, from the context menu of ZGROUP_1 execute "Manage Master Data" option. You'll see that the long text has been truncated, and all attributes have the same values as this infoobject's characteristic value.
Master data for the ZGROUP_2 will show that its long text contains the second part of the long description. The blank texts for some rows mean that the first part of the long description was less than 61 character long.
The similar is true for the 3rd and 4th peaces. Create an infoprovider. Load into it some transaction data containing our ZGROUP_1 infoobject. Now we are ready for a query creation and formatting a workbook. See the next blogs: Long texts in SAP BW: Modeling – Follow UpLong Texts in SAP BW: Displaying in BEx Analyzer. Introduction to Excel Workbooks Formatting. Part I...Long Texts in SAP BW: Displaying in BEx Analyzer. Introduction to Excel Workbooks Formatting. Part I...
- SAP Managed Tags:
23 Comments
