Hi,
I have some data that includes Broker names, product they bought, and corresponding values and quantity of the products. I need to show top 4 brokers (each product wise) based on values of the product as well as "other than top 4". If for a particular product, there is only two brokers that have some value and rest of the brokers have just quantities and value equal to 0, then i want their sum to be removed from "top 4" field and added into the "other than top 4" column. I know it sounds really complicated. I am preparing this report on sql and SSRS. Below is the sample of desired report:
Sample Data Table:
product Brokername Values quantity
Casualty ABC 500 2
Casualty XYZ 300 3
Casualty LMN 250 4
Casualty PQR 0 4
Casualty OPQ 0 6
marine XYZ 200 1
marine ABC 300 2
energy LMN 600 4
energy MNQ 0 1
Desired report (On SSRS):

I have inserted the image of the desired report. I am almost done with rest of the data on SSRS. These are some of my queries.
Thanks
Provided suggestion/solution.