running sum and colored rows for measures

[hs1]

Hi All,

i have in a BEy Query a "running sum" for one measure.

In the analysis Excel it is after import  only a field sum.

who knows what have i to do to make a running sum in this analysis excel?

A second question

The Measures are in Rows. I have 4 Measures and i wants for each measures make a color for each measures row. is there a way to do this?

Thanks for any ides and helps

br Harry

[Former Member]

Hi Harald,

For the Measure coloring, you haven't specified whether you want the entire row colored, or just the Key Figures, or just the dimension headers.... Either way, the solution is much the same.

You'll need to register an AfterRedisplay callback method (using SAPExecuteCommand), and then in that method, loop over the cells in the crosstab to identify the cells that need to be colored (using SAPGetCellInfo to determine if it is the measure you're interested in, or some other way of identifying the cells you want to color), and apply an Interior.Color (or some other formatting) using VBA.

That way, every time the Crosstab is redisplayed (and the redisplay will clear your custom formatting), the cells are recolored. Depending upon the size of your crosstab, you may need to optimise how you discover which cells require coloring, as calling SAPGetCellInfo multiple times can be a performance hit, and applying colors/formats to individual cells is much slower than applying colors/formats to multiple cells at once.

I've used this method to apply Red text to negative numbers in a Crosstab - something that isn't possible with Scaling/Precision number formats within Analysis. I'm hoping to put together a document on my approach soon....

[Former Member]

Hi Harald,

in Analyis for Excel you can right click on the column header of your key figure and then in the menu choose Add Dynamic Calculation --> Accumalative sum. It will then insert a new column with the accumalitve sums.

For the colors I have no solution.

Hope this helps.

Regards, Machiel

[TammyPowlas]

Harald,

I unlocked it - thank you for the clarification

[TammyPowlas]

Harald,

I see you already posted this in the Analysis OLAP space.

Please note that no cross posting is allowed on SCN.

This thread will be locked.

Tammy

Moderator