Formatting CrossTab in SAP Analysis Office 1.4

[Former Member]

Hi Experts

SAP Analysis for Office 1.4 SP7

Realized fomatting with the standard styles incl. SAPMemberCellX, SAPMemberCellX, SAPMemberTotalCellX SAPHierarchyCellX, SAPHierarchyOddCellX, SAPHierarchyCell0-9

Problems:

• How to format SAPDataTotalCell in Columns (no SAPDataTotalCell X available)
• How to format SAPMemberCellX like in the example: border only at the last row, not between the MemberCells

Today:

Target:

Delta:

thx for reply!

Michael

[Former Member]

Hi Michael

I did something similar by help of a mixture of Analysis API and VBA

1.

In your workbook define in "this workbook the following declarations:

Public iFirstRow As Integer, iLastRow As Integer, iFirstColumn As Integer, iLastColumn As Integer

' defined in ThisWorkbook

Public Sub Workbook_SAP_Initialize()

' register callbacks

Call Application.Run("SAPExecuteCommand", "RegisterCallback", "AfterRedisplay", "Callback_AfterRedisplay")

End Sub

2.

then define a module with the macro ( User Exiit)

Public iFirstRow As Integer, iLastRow As Integer, iFirstColumn As Integer, iLastColumn As Integer

' defined in a module

Public Sub Callback_AfterRedisplay()

'THis is a User Exit for Analysis Office

myDimension  ' get the dimensions of your crosstab

Undeline_Header ' Underline the first row

End Sub

3. define a function (you call in upper macro to detect the dimensions(rows, columns) of your data

Function myDimension()

'this allows to modify your crosstab by rows. columns and offsets

iFirstRow = Range("SAPCrosstab1").Row

iLastRow = Range("SAPCrosstab1").Rows.Count + iFirstRow

iFirstColumn = Range("SAPCrosstab1").Column

iLastColumn = Range("SAPCrosstab1").Columns.Count + iFirstRow

End Function

4 .

last not least the function or sub (if you like to use a button like "underline"

Function Undeline_Header()

' you my skip the selection ans just purely address the range

Range(Cells(iFirstRow + 1, iFirstColumn), Cells(iFirstRow + 1, iLastColumn)).Select

    With Selection.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .Color = -16777024

        .TintAndShade = 0

        .Weight = xlMedium

    End With

End Function

THe result will look like my last picture.

By changing the offsets of first or any other variable, you may insert formatting or whatever Excel has to offer

Hope that may help you.

I did a lot of enhancements in BEx for the last 15 years by help of the (with 7x ) almost lost API

Many thanks to the Analysis Office developer crew taht you decided to have such a fantanstic API

Joerg Boeke

[Former Member]

Dear Joerg

Sorry for the late answer. I was in holidays

Many thanx for your replay. This is excellent! It is a pity, that formatting of crosstabs is not easier in EXCEL when using Analysis (!).

Kind Regrads,

Mike

[TammyPowlas]

Is this a hierarchy?  Because the top of the hierarchy style can be changed with SAPHierarchy0 format - see below:

Not sure about the totals row though; I've done this before with Crystal Reports and formulas (works great) - but maybe you could play with the desired hierarchy levels to get the desired effect.

[Former Member]

Thanks for your replay! There are two
tables and the first table contains a hierarchy.

I tried with SAPHierarchyCell0, and as
you can see the row title in the left are with the correct format (inkl.
border). My problem is the data part of hiearchy0 level. Unfortunately this is
assigned to the sytle “SAPDataCell” and has no relation to the hierarchy.

However in the second table I have a
problem with the format of the crosstab totals in rows or in columns.
Unfortunately there is no SAPDataTotalCellX, only a SAPMemberTotalCellX.
Hence the member part can be formatted as wished, the part of data total not.

I guess VBA is my only possibility. Any
idea how to do this?

[TammyPowlas]

VBA - maybe, but I am not an expert

I hope or others have some ideas...

The formatting you are asking works great in Crystal