Skip to Content

Long texts in SAP BW: Modeling

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: Re: Increasing the length of Infoobject from 60 to 240 characters 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.

image

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).

image

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.

image

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. image
  • 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. image
  • 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.
  • image
  • 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”).
  • image
  • 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.
  • imageimage Insert all four infoobjects. Refresh the screen. After that we see five data targets, four for texts and one – for attributes. image
  • Create URs for the attribute data target (ZGROUP_1 infoobject) based on the infosource we’ve been created. image
  • We see that rules for the attributes are grayed out. Click on either of grayed out crosses.

image

Choose “Overwrite” as Update type, and “Source Field” as Update Method. Select ZGROUP_1 infoobject as the source field. image

Click on the “Key Fields” tab to make sure that the source field for the Key Field has an assignment as shown below. image

Repeat the same procedure for all attributes. Now all grayed out crosses should look like green triangles. Activate the rules.

image

Create URs for the text data target of ZGROUP_1. Click on the grayed out cross.

image

Again, choose “Overwrite” and “Source Field”. Make sure that you choose 0ADDR_LINE1 infoobject as a source field.

image

The “Key Fields” tab will have two infoobjects. Leave them as they are.

image

Repeat creation of URs for ZGROUP_2, choosing 0ADDR_LINE2 as a source field.

image

One of the rows in the “Key Fields” tab will have the blank assignment.

image 

Choose ZGROUP_1 (not ZGROUP_2!) as a source.

image

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.

image

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.

image

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.

image

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 II.

23 Comments
You must be Logged on to comment or reply to a post.
      • Hi Eugene,

        While this is a very interesting blog, Im quite keen to know a non-workbook based solution. As you would be aware, BI delivery is heading towards a web based portal solution, away from the workbook / Bex /excel environment it would be nice to know of options in this front. We`ve had some instances where we used crystal reports as a mean of handling long texts out of BW. There seems to be a growing demand on being able to represent long texts as part of the BI solution.

        Regards

        • Hi Karthik,

          I guess that your comment should have gone to my other two blogs concerning workbooks formatting.
          I was surprised that no comments was published to them. And I thought that was mistaken when proposed that the issue would be interesting.

          Regarding your question about long texts presentation in web reporting - one day I'll try to cover this issue also.

          Best regards,
          Eugene

          • Hi,

            can you help me on this issue.

            We receiving data from source (SAP CRM) systems to SAP BW, Characteristic description is more then 300 ‘Characters’   we want to report this in BEX.
            “Description of a charactristic from source system to BW will come more then 600 and may be more then 1000 characters it is not fixed”.  

            Our problem is how to pull data into BW?
            How to take Report on this pulled data?

            Please suggest or give me a solution for this kind issues how to resolve.

            Regards
            Mohan

          • Hi Mohan,

            Never worked with CRM yet. Is it true that incoming field length is not fixed?

            In general, if the length is fixed, you can use the same technics I described. Though, the number of additional infoobject will be much more.

            Best regards,
            Eugene

          • Eugene
            Hi
            Thanks for  information it was very helpful to me.
            I was wondering if  there was a limitation to the length one could import.
            When I tried to change the length in the Datsource/ transfer structure to 360 I received an error message when activating that the Length should be between 1-255 .
            Thanks
            Ingrid
        • Web:
          This can be achieved with the help of some java scripting in Web Templates. In one of the projects we did this. I think, this is working fine.

          Excel:
          In our project we adopted the same method for displaying long texts in work books for CRM data. But, this type of solution is OK, if the length of the text is predefined may be around 200-300. This solution cannot be used for situations to display the system logs/scripts or e-mail contents since the length of text is not pre-defined and also length may go beyond 3000.

          Thanks
          Surya Muvvala

  • This blog raises some questions ,
    Why 60 characters in the first place ?

    I too had to resolve to splitting the charaters into 60 each and then using the table modifier , displaying them together , and also if you want to you can change the long text length to 240 by using an SAP access key and then storing the same in the long text of the info object but alas , reporting will not let you display more than 60...

    Arun

    • Hi Arun,

      I didn't completely understand your question: "Why 60 Characters in the first place?".
      60 chars as a BW limitation. Something wrong with it?

      When you talk about table modifier, I presume that you referred to web reporting, didn't you?
      Meanwhile I emphasized the BEx Analyzer workbooks formatting.

      So...?

      Best regards,
      Eugene

      • I guess that Arun raised the question that every second customer of mine asks. Why did SAP limit the long text to 60 characters? I guess that nobody thought that the people needed more than 60 characters. If you do the thing that BW was originally intended to do, namely reporting SAP data, 60 characters is always enough. But BI has grown beyond this.

        Arun,
        can you describe the table exit in a blog? Then we would have the complete solution, data model, excel and web in three blogs.

        Best regards
           Dirk

    • Hi,

      can you help me on this issue.

      We receiving data from source (SAP CRM) systems to SAP BW, Characteristic description is more then 300 ‘Characters’   we want to report this in BEX.
      “Description of a charactristic from source system to BW will come more then 600 and may be more then 1000 characters it is not fixed”.  

      Our problem is how to pull data into BW?
      How to take Report on this pulled data?

      Please suggest or give me a solution for this kind issues how to resolve.

      Regards
      Mohan

      • During the migration process problems have been reported for this blog. The blog content may look corrupt due to not supported HTML code on this platform. Please adjust the blog content manually before moving it to an official community.
  • Hi,

    can you help me on this issue.

    We receiving data from source (SAP CRM) systems to SAP BW, Characteristic description is more then 300 ‘Characters’   we want to report this in BEX.
    “Description of a charactristic from source system to BW will come more then 600 and may be more then 1000 characters it is not fixed”.  

    Our problem is how to pull data into BW?
    How to take Report on this pulled data?

    Please suggest or give me a solution for this kind issues how to resolve.

    Regards
    Mohan

  • I personally preffer the option to use a z-table with 3 fields: code + language + long text (255).

    And then read the long text implementing the Interface: IF_BICS_CONS_WEBITEM_CUST_EXIT.

    Regards,
    Iñigo Montoya