on 07-10-2014 2:15 PM
Hi Experts
SAP Analysis for Office 1.4 SP7
Realized fomatting with the standard styles incl. SAPMemberCellX, SAPMemberCellX, SAPMemberTotalCellX SAPHierarchyCellX, SAPHierarchyOddCellX, SAPHierarchyCell0-9
Problems:
Today:
Target:
Delta:
thx for reply!
Michael
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
User | Count |
---|---|
85 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.