Avatar of Aakriti Choube
Aakriti Choube

asked on 

How to Get "top N" and "Other Than Top N" values in SQL and SSRS?

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):

User generated image
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
SSRSSQL

Avatar of undefined
Last Comment
Pawan Kumar
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Question abandoned.
Provided suggestion/solution.
SQL
SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo