How to get the Max value from the list of records in Table

Question

Post reply

How to get the Max value from the list of records in Table

Rachitha

SSC-Addicted

Points: 441
More actions
January 23, 2009 at 8:07 am

#196465

Hi,
I am using SSRS 2005. I want to get the maximum value from the list of records in SSRS Table.
Example: I got total sales of each product in Reort table:(Grouped by product field, and got sum(sales)
Product Name Sales
Prod1 67
Prod2 600
Prod3 50
Now from this above result set, i want to get the Max value i.e "600". This max value i will use in setting Maximum Scale limit of a Line Graph using ReportItems!XXX.Value
FYI: backend is MDX Queries. I want to get above requirement @ Reporting level itself.
Please provide your valuable suggestions.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 1

Hi Rachitha,

Try this:

=Max(Fields!FieldName.Value, "DataSetName")

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Rachitha SSC-Addicted Points: 441 More actions · Answer 2

haii hari...

i cannot use Max() function as u suggested bcuz...

DataSet returns:

Prod1 200

prod1 300

prod1 400

prod2,... and prod3....

using table.. i am adding up sales for each prod.

prod1 900

prod2 ...

prod3 ....

now i want to get the max. value from above table.

Please suggest...

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 3

Hi Rachitha,

I am not getting your question at all... Could you make it more clear?

If you want to calculte the Max value from your dataSet then you can go for Max(FieldName!Value,"DataSetName").

If you want to calculate the max value for each Product then you can go for row grouping.

If you can make ur question more clear, you'll probably get more clear answer.

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Rachitha SSC-Addicted Points: 441 More actions · Answer 4

Hai Hari..

I want to get the Max("Runningvalue(sales) by product")

1st step: get Running Total of Sale for each product..

the result of this will be

Prod.Name Runningtotal

Prod1 1500

Prod2 2500

Prod5 750

From this result... i want to get MAx(RunningTotal)...i.e "2500"

Pl. let me know if this is not clear.. i will clearly explain in detail by taking sample data.

Thanks,

Rachitha

Paul -244830 Mr or Mrs. 500 Points: 501 More actions · Answer 5

Hai Hari..
I want to get the Max("Runningvalue(sales) by product")
1st step: get Running Total of Sale for each product..
the result of this will be
Prod.Name Runningtotal
Prod1 1500
Prod2 2500
Prod5 750
From this result... i want to get MAx(RunningTotal)...i.e "2500"
Pl. let me know if this is not clear.. i will clearly explain in detail by taking sample data.
Thanks,
Rachitha

I think I know what you want:

Example table:

Table Prod

ID Int IDENTITY

Name varchar(50)

Sales decimal(8,2)

1. Get the running totals:

SELECT Name, MAX(Sales) AS RunningTotal

FROM Prod

GROUP BY Name

2. Get the Top selling product (Max RunningTotal): (easy but sloppy way)

SELECT MAX(RunningTotal) FROM

(

SELECT Name, MAX(Sales) AS RunningTotal

FROM Prod

GROUP BY Name

)

Rachitha SSC-Addicted Points: 441 More actions · Answer 6

hi ..

we achieved the requirement using customer code 🙂

Thanks,

Rachitha