Solved

Need to SUM the MIN() of Rows in a Matrix in SSRS 2008.

Posted on 2010-09-08
2
813 Views
Last Modified: 2012-08-14
Experts:

I need to SUM the MIN() AMOUNT field wthin a row of data that is pulled into SSRS 2008. I have a Matrix and need the CODE that will tell the ROW CELL to Summarize the MIN of an Amount_Field because due to the report requirement, I have numerous rows of duplicated Amounts by ORDER_ID. I need to first of all tell the matrix to pick the MIN and then summarize all the MINS.

Currently my Amount Summary is way over-stated because of the duplicates.

0
Comment
Question by:MIKE
2 Comments
 
LVL 3

Accepted Solution

by:
seenall earned 500 total points
ID: 33631697
Not sure I understand what aspect of code you refer to (Report Expressions etc)

But from an SQL Perspective - OLAP windowing comes into its own for this question, if I understand it correctly



select  sum(res.amount_field) amount_summary
from (
select order_id
     , amount_field
     , rank() over 
       (partition by order_id order by amount_field) ranked_amount
  from matrix_table
) res
where res.ranked_amount = 1

Open in new window

0
 
LVL 17

Author Comment

by:MIKE
ID: 34418116
Actually, I need the SSRS "Expression" formula to do this within SSRS....

I"m using a Matrix
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question