sort/rank based on a subtotal of Sum in breaks

system · August 26, 2010, 4:02pm

Hi all,
I’ve tried to search for the similar forums as below but came to no solution.

What I currently have in my report is a break on State, which shows Sales Revenue for the Stores in each states. e.g. the following:

California-Store 1-50K
-Store 2-50K
-Store 3-50K
SUM-150K

Canada-Store 1-10K
-Store 2-10K
-Store 3-10K
SUM-30K

Florida-Store 1-30K
-Store 2-30K
-Store 3-30K
SUM-90K

Based on the SUM for each States, is it possible to sort the states with the highest Sales Revenue first? Tht means the report will be sorted: California > Florida > Canada

I’m using WebI Rich Client 3.0. Please advise.
Thanks.

ccool2000 (BOB member since 2008-12-09)

system · August 27, 2010, 8:02am

This is the only workaround I have come up with.

Create a rank object: Rank =rank([Sales Revenue];[State])
Insert rank object preferably as a first column
Sort rank object in descending order
Break rank
Hide the rank column

Hope it works for you.

Cheers,
Mhy

mhy_pie03 (BOB member since 2009-02-05)

system · August 30, 2010, 3:08am

Hi mhy_pie03,

It works fine! However, I have problem trying to hide the rank column. Can I know how did you do it?

Cheers~

ccool2000 (BOB member since 2008-12-09)

system · August 30, 2010, 9:31am

It’s another workaround,

reduce the column width to minimum (4)
change the text colour to ur background coluor
hide the boarders

BO doesn’t have HIDE option.

avetrivel (BOB member since 2010-05-27)

system · August 31, 2010, 1:55am

Yeah, it’s just s workaround and the only way to hide it is change the background color, font color and border line.

mhy_pie03 (BOB member since 2009-02-05)

system · August 31, 2010, 2:57am

Perhaps BO should come out with the hide option. That would be brilliant

Thanks again~

ccool2000 (BOB member since 2008-12-09)

system · August 21, 2011, 2:19pm

Hi,

One another option to hide can be use of alerter; using hardcoded expressions along with format the cell and keep text = " " in the alerter variable works. The field doesnt come up even in save as excel or pdf.

Ethan

ethaihunt (BOB member since 2010-10-07)

system · November 18, 2011, 5:34am

BO 4 is having conditional hiding feature…

Vills (BOB member since 2007-10-24)

system · January 16, 2013, 9:01pm

Replying to this helpful post as it is the same situation I am in.

Like the original poster, I am ranking based on subtotal, then breaking on rank.

My question: in the break footer, I would like to display the rank (e.g. first break displays Rank 1, second break displays Rank 2). I get a #CONTEXT error when displaying the rank object in the break footer/header, and I cannot find the correct context formula to use for this. Can someone help?

Attachment 1 image: sort on rank object works correctly

Attachment 2 image: after breaking on rank, the breaks are sorted correctly, however the rank object returns #CONTEXT errors in break footer.

I would like the footer in first break to display “1” (for Rank 1), and the footer in the second break to display “2” (for Rank 2), and so on.

adamNYC (BOB member since 2012-10-16)