Column headers in Bex for Key + text
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.
Scroll below to the following piece of code
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”
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:
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.
Is it possible to use similar macro to achieve the same result in Analysis for Office ?