Top 10 Records

[joecog]

Hi,

I would like to generate a crosstab report that show only Top 10 Salesman for user selected month.
Let say in the prompt value, user select May 2010, in the report will show 10 records where first row is the salesman with highest sales amount and the 10th row the lowest among these 10 salesman.

I had try create a data item known as TopMth with expression topcount ([SelectedMth],10,[NetAmount]) but system still show more than 10 records and those with zero figure is still shown in the report as well.


Any idea on how to solve this?

[MFGF]

Hi,

You don't seem to be using your Salesmen anywhere in your expression.

Try the following:

topcount([Your Salesman Level],10,tuple([SelectedMth],[NetAmount]))

Regards,

MF.

[joecog]

Hi MFGF,

I had tried the expression but report still show more than 10 rows and those salesman with zero figure is still shown as well.

crosstab Report layout should be as below :-

NetAmout      SelectedMth
Salesman1     150
Salesman2     140
Salesman3     100


How to fix this?

[MFGF]

Hi,

Just to clarify, are you using this expression in a Query Calculation in your rows, not as a detail filter?  If so, this should work...

MF.

[joecog]

Hi MF,


I try again and got it...
Many thanks.

[joecog]

Hi MF,

Though manage to get the results, but I still do not understand why do we need to use tuple in the expression given.
May I know what is function of tuple ?

[MFGF]

Hi,

A tuple presents a value at the specified intersection of members.  So in your example above, tuple([SelectedMth],[NetAmount]) provides the NetAmount value specifically for the SelectedMth member.

This is used as the context for the topCount() function - ie show the top 10 Sales Reps based on NetAmount for your selected month.

Regards,

MF.

[joecog]

Hi MF,

Thanks for the explanation.
Thanks again.