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?
Hi,
in the list properties inside Data -> Properties add your field 'Product_Name' .
schrotty
Quote from: schrotty on 11 Nov 2015 10:15:40 AM
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 |
total([Items_Sold] for [Adjusted Product]) may work
Unfortuantely this still is not working. I completely removed the mesaures and am still getting duplicate 'Other'
Hi,
Does this (http://www.cognoise.com/index.php/topic,8934.msg29234.html#msg29234) post help?
MF.
YES!!! Once Again MFGF, you are a prince among mortals.