Get Max value from the list of records in Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Get Max value from the list of records in Table

Author

Topic

rvaraganti
Starting Member

22 Posts

Posted - 2009-01-23 : 10:07:42

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.

Thanks,
Rachitha.

revelator
Starting Member

32 Posts

Posted - 2009-01-23 : 10:16:08

In an expression, you could use:

=Max(Fields!Sales.Value, "YourDataset")

-----------------------------------
Waiting for the Great Leap Forwards

rvaraganti
Starting Member

22 Posts

Posted - 2009-01-25 : 05:57:22

hi, if i have single record for each product. then using Max() would be fine.
But.. my dataset has multiple records for each product. i want to calculate the sum of sales of each product. After doing so, i want to get the Maximum value of sales.

Thanks,
Rachitha.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-25 : 12:17:52

quote:
Originally posted by rvaraganti

hi, if i have single record for each product. then using Max() would be fine.
But.. my dataset has multiple records for each product. i want to calculate the sum of sales of each product. After doing so, i want to get the Maximum value of sales.

Thanks,
Rachitha.

you cant apply aggregate over aggregate. just bring data grouped by product and taking sum from table. then inside report take max using expression.

rvaraganti
Starting Member

22 Posts

Posted - 2009-01-26 : 04:28:24

hi Visakh,

"just bring data grouped by product and taking sum from table"

Table in above sentence: mean SSRS table or DB Table?
If SSRS Table: let me know how to use Max(Expression) in "Chart" or "textbox". What will be the field value and scope value of Max Function.

Thanks,
Rachitha.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 08:40:51

i meant sql table not ssrs table.
in case of ssrs table you use max as

=Max(Fields!yourfield.value,"your dataset name")

rvaraganti
Starting Member

22 Posts

Posted - 2009-01-26 : 08:50:11

hei visakh...
=Max(Fields!yourfield.value,"your dataset name") as per ur suggestion:

here, yourfield.value is not the what we are getting from dataset.
We need get the cumulative "YourField.Value" group by Product.
Among all products, i want the maximum.. cumulative value. Which i will use to set my chart MAximum scale (Y-Axis) limit on alll pages.
Main intension of this process is to have Y-Axis limits over all the pages.

Thanks,
Rachitha.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 09:02:44

your value can be a cumulative value no problem in that. just write the backend query like


SELECT Product,SUM(Sales) AS SalesCumulative
FROm Table
GROUP BY Product

then in report group again by Product and take

=MAX(Fields!SalesCumulative.value,"YourcreatedGroup")

rvaraganti
Starting Member

22 Posts

Posted - 2009-01-27 : 00:58:31

FYI: backend is MDX Queries. I have said this in starting itself..

backend queries doesn't work here

Thanks,
Rachitha.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 12:35:32

so what just use sum in MDX


With MEMBER [Measures].[x] AS 
SUM(yoursethere,your aggregated quantity here)
SELECT [Measures].[x] ON ROWS,
..... ON COLUMNS
FROM [YourCube]

rvaraganti
Starting Member

22 Posts

Posted - 2009-01-29 : 08:33:05

Hi Visakh,

sorry for late reply..
2days back itself we achieved our requirement using Custome code.
Custom code calculate each products cumulative value and finally returns the maximum among them.

.. tq so much for your help..!! will come back to you with some more tricky questions

Thanks,
Rachitha.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources