Running Sum on Sorted data & freeze panes?

[Former Member]

Hi all,

Brand new to Web Intelligence XI, so learning whilst putting together first report, however need some guidance and help on the following.

I have a basic report which is sorted by highest ytd sales within the report and has breaks at each change in brand category.

I need to be able to provide a running % cumulative total for each article within each brand category based on the highest ytd sales.

I have tried using the running sum formula but this does not give correct results due to the sort, as when this is taken off

the formula is correct. Have different varients of this after reviewing the forum but unfortunately still cannot get this basic calculation to work.

My second question is that when viewing a lot of data, is it yet possible to freeze the headers in each column?

Thanks in advance for any help given.

Nick

[Former Member · Accepted solution]

Regarding your second question about having freeze pane functionality in a Webi report I have written in my blog about a workaround I came up with. You can read at http://breakthatdown.blogspot.gr/2013/04/freeze-panes-in-bo-webi-report-like.html

Thank you,

Antonis

[Former Member]

I am glad to hear that 4.1 will include this feature. The thing is that until we migrate to 4.1, quite some time will pass. I believe this applies to many others too.

[former_member184594]

Freeze panes will be supported in BO 4.1 which is planned to be released in Q3 2013.

Check the details below:

http://scn.sap.com/community/businessobjects-web-intelligence/blog/2013/03/10/what-is-new-with-web-i...

http://scn.sap.com/community/business-intelligence/blog/2013/01/31/sap-businessobjects-bi-41-update-...

http://scn.sap.com/community/business-intelligence/blog/2013/02/01/sap-businessobjects-bi-41-update-...

[Former Member]

Hi Nick

For your second question regarding freeze panes , it will be release soon in sap bo 4.1 ,

Regards

sunil

[Former Member]

Hey Nick,

Can you post RunningSum() formula in each case(Before & After sorting)

possible to freeze the headers in each column?

>> As you applied Breaks,

1. Select any Break header and go to Properties, Uncheck Repeat Break Header

2. Select whole block and properties, Check Show Table Header.

Thank You!!

Sent from iPhone

[Former Member]

Hi BOCP-BOE

Thanks for your response, my formula is =RunningSum(Percentage([Sales Query].[Sales Qty TY YTD]); ([Merchandise Category]))

When I remove the Descending Sort option from column Sales Qty TY YTD then the results returned are as expected

eg.

6.35%

12.70%

14.92%

19.68%

22.54%

etc

up to 100% for each Merchandise Category

However after the sort is applied the results returned are

6.35%

12.70%

47.30%

31.43%

19.68%

etc

Think the running sum does not recalculate after the sort and gives the original % result.

As for the headers, I meant to say do we have the same functionality as within excel to be able to freeze

the column headers so as to make viewing easier when scrolling through many rows of data.

Thanks for any help with this.

Regards

Nick

[Former Member]

Hey Nick,

Formula is correct. The value for RunningSum needs to be reset to zero for each Merchandise Category. Which you already did. Only Sort will do the sorting. As per formula it will return/show values as Merchandise Category listed out in report.

Freeze: For this we have to go with Excel freeze settings.

Thank You!!

H2H

[Former Member]

HI BOCP-BOE

Thanks for this, but do you have any ideas on how I can get a solution

for this?

Regards

Nick

[Former Member]

Hi BOCP-BOE

For information, sorted this out by using Previous(Self) formula to get the cumulative then worked

this out as a % of the section total to give me my Cumulative % mix.

Regards

Nick

[Former Member]

Good to hear from you Nick. So your problem got solved, Correct.

Gracias...!!

[Former Member]

Hi Nick,

Freeze panes is not currently available with Webi reports.

This feature is nevertheless being considered for a future release with a good priority.

I encourage you to submit this enhancement request on the Idea Place portal for WebI :

https://ideas.sap.com/community/products_and_solutions/sbowebi

so it can be properly tracked down.

Hope that helps,

David.

[Former Member]

Hi David,

Will do. Thanks for the positive response.

In the Retail enviroment I work in, the ability to scroll through article level

data quickly showing exceptions / alerters is invaluable.

thanks again

Nick

[Former Member]

Thanks for getting back.

I can see that you are also relying on Web Intelligence Alerters (that will slightly be changed with the next major release -XI 4.0-, and will be renamed Conditional Formatting ).

Feel free to submit any remark or feedback about this tool on the forums (as well as enhancement ideas on Web Intelligence Idea Place portal).

Best regards,

David.