Skip to Content

Hi,

anyone who has ever tried to create a pivot table on top of the Bex Analyzer output will have experienced this issue.

When displaying key and text for an info object, the column header for the key is filled, but the text column remains empty without a header.

This makes it impossible to create a pivot table on top of it.

Using the Callback macro in Bex 7.x it is possible to scan the column headers in the result area and put in a custom text.

In this blog I describe how to do this.

First of all, run the query in Bex analyzer.

After running the query, go to view –> macro’s –> view macro’s

select CallBack and press edit.

macro screen.jpg

Scroll below to the following piece of code

callback macro before.JPG

After the End With and before End If, insert the following lines:

    ‘set column headers for key + text

    Dim nrCol As Long

    Dim resultArea As Range

    Set resultArea = varname(1)

    nrCol = resultArea.Columns.Count

    For i = 1 To nrCol – 1

        If resultArea.Cells(1, i + 1) = “” Then

            resultArea.Cells(1, i + 1) = resultArea.Cells(1, i) & “_text”

        End If

    Next i

This code will put suffix _text in the column header, based on the preceding column header.

The end result in the macro then looks like this:

callback macro after.JPG

After refreshing the query, you will now see the column headers being added based on the previous column header, with _text behind it.

Hope this will help a lot of people.

Best regards,

Arno

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply