Column headers for key and text

[bverweijen]

Hi all

When displaying both key and text members, AfO only shows the column header in the first of the 2. Eg when looking at Key and Text for 0CUSTOMER, it just shows 'Customer' in the column header of the Key, and the column header of the Text is blank.

Does anyone know if it is possible to also show the column header of the Text column?

Reason: would like to use the AfO table as an input into pivot or powerpivot, as some functions are not yet available in AfO.

Thank you

Bart

[former_member523998 · Accepted solution]

If you use this VBA code on Worksheet activation Event, it will populate blank headers with a dynamic header when you activate the worksheet. Alternatively, you could run this macro manually, or use the AfterReDisplay callback (similar to EPM's Function After_Refresh).

'This code should be placed in the WORKSHEET Module that the AO report exists in.
'From Excel Worksheet, if you right click on the tab, you should be able to see  "view code"
'paste this in and you should be good.
Private Sub Worksheet_Activate()

Const NM_Crosstab As String = "SAPCrosstab1" 'make sure yours matches your AO named range of data
Const EndTExt As String = "_NAME" 'whatever text you want to be at end of column header



Dim aCell As Range, tRng As Range
Set aCell = ThisWorkbook.Names(NM_Crosstab).RefersToRange.Cells(1, 1)


Do Until aCell.Value2 <> ""
    Set aCell = aCell.Offset(1, 0)
Loop


For Each tRng In Intersect(aCell.EntireRow, ThisWorkbook.Names(NM_Crosstab).RefersToRange).Cells
    If tRng = "" Then
        
        'if you don't use English excel, this may need modifiying. Look up R1C1 References
        tRng.FormulaR1C1 = "=RC[-1]&""" & EndTExt & """"
    
    End If
Next tRng


End Sub

[stephen_hobbs2]

Thanks Steve, this is helpful. It looks like it's also on SAP's Roadmap for Analysis...

[former_member523998]

Thanks for sharing. I don't like working around the software, but I was pretty sure this functionality didn't currently exist. I gave you an upvote, thanks.

[Former Member]

Hi Bart,

I couldnt find any settings which offer to show the Description Column header! But when I used "Covert to Formula" option on the query...I got the below output.....Since you just want a pivot on it....it should be OK?!

[TammyPowlas]

Interesting option; I also tried a formula option to copy over but it doesn't stay when I pivot

[Former Member]

Hmm....Just serached and worked out with that option for Pivot..

[former_member269156]

Hi,

Insert a new line after the required dimension and add a formula to get TEXT/KEY.

This will stick to your report result and works with Pivot as well.

thanks,

Raju

[bverweijen]

Thank you for your thoughts on my issue!

The challenge with converting to formula is that the data set is fixed. My need is that next time I refresh the AfO report, and my data set has grown, the pivot source adjusts accordingly, which I was hoping to be able to do as the AfO report has a named Excel table name.

[Former Member]

I went to BEx Query Designer and changed the name of the dimension as follows or description...

Then In Analysis for Office I loaded the query and did Convert to Formula...

Then created a pivot on it....

JP