Rank Function - Top 10 else 'Other'

[antya76]

Help!! I am building a list in Report Studio based on a Relational Database (I wish it was Dimensional but that is a topic for another day) and would like for the list to display the Top 'N' products for a specific customer and sum the remainder and call it 'Other'. Here is what I've done so far:

1. [Items_Sold] is a summarized field from the database
2. In the query, I created a field called 'Product_Rank' with the following calculation: rank([Items_Sold] for [CustomerID])
3. I then created another field called 'Adjusted_Product' with the following calculation: case when [Product_Rank] < 11 then [Product_Name] else 'Other' end

When I display the list with both the 'Product_Name' and 'Adjusted_Product' fields, it works fine. However when I remove the field 'Product_Name', the list only shows 1 item, and the [Items_Sold] is incorrect.

How can I resolve this?

[schrotty]

Hi,

in the list properties inside Data -> Properties add your field 'Product_Name' .

schrotty

[antya76]

Hi,

in the list properties inside Data -> Properties add your field 'Product_Name' .

schrotty

Thanks! I tried this and it worked, but it showed me mulitple lines for 'Other', rather than summing the [Items_Sold] for those where [Adjusted_Product] = 'Other'

Customer	Adjusted_Product	Items_Sold
ABC Company	Product 1	24,798
ABC Company	Product 2	673
ABC Company	Product 3	398
ABC Company	Other	530
ABC Company	Other	241
ABC Company	Other	246

[rockytopmark]

total([Items_Sold] for [Adjusted Product])  may work

[antya76]

Unfortuantely this still is not working. I completely removed the mesaures and am still getting duplicate 'Other'

[MFGF]

Hi,

Does this post help?

MF.

[antya76]

YES!!! Once Again MFGF, you are a prince among mortals.