Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 819
  • Last Modified:

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

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
MIKE
Asked:
MIKE
1 Solution
 
seenallCommented:
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
 
MIKESoftware Solutions ConsultantAuthor Commented:
Actually, I need the SSRS "Expression" formula to do this within SSRS....

I"m using a Matrix
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Tackle projects and never again get stuck behind a technical roadblock.
Join Now