Top 100 using Rank

[jaymoore1756]

I am trying to rank the top 100 water users based on water charges in a report. The users (account) have multiple account numbers so I am totaling the water charges. I want to rank on the total water charges. This report uses a relational data base. I have created the following

1) Data item called rank with the expression ----  rank(water charges for Account)
2) A filter  -- Rank <= 6 set to after aggregation

Currently I have a sample of 6 accounts that I am testing with.

What should the Aggregate function and rollup function be set to? I have tried multiple ways but I never get 1-6, I am getting numbers like 386,155,92

Any help would be appreciated

[Lynn]

Sometimes I find that an aggregate function of None works when I've got the scope explicitly defined with a "for" clause. You've already told it precisely what you want it to do so further aggregation rules just confuse the matter.

If your query is at the account level of granularity anyway then you might be able to just omit the for clause and just use rank ( [water charges] ). In that case I'd try calculated or automatic as the aggregate function.

I'm not sure the rollup aggregation function will matter in your case because you are not going to do anything to aggregate the ranks further. It wouldn't make sense to get the total of 1+2+3+4+5+6, or average those numbers. You generally worry about the rollup aggregate when you've got a list or crosstab that needs to show a higher level summary of something, above the granularity of your details.

[jaymoore1756]

I changed the rank to just water charges set aggregate to auto, rollup to auto and ran the report. When I do this data comes up missing, so I disabled the filter and got the six accounts .. The ranks are out of order and there are duplicate ranks.

[Lynn]

I changed the rank to just water charges set aggregate to auto, rollup to auto and ran the report. When I do this data comes up missing, so I disabled the filter and got the six accounts .. The ranks are out of order and there are duplicate ranks.

I'm not entirely following. What do you mean when you say data comes up missing? If you disabled the filter then you should be getting all the results ranked, not just the six, right?

Are the ranks incorrect based on the charges or is the out of order problem just a job for sorting in the layout?

As for duplicate ranks, that is possible in cases of a tie. The rank function does Olympic ranking so you could get, for example, ranks of 1 2 3 3 5 where  two guys have the same value. In this example there are two number 3 ranks and no number 4 rank because of the tie.