Exclude Hidden Rows from Aggregates in SSRS

Epicor ERP 10
,
1

Hello all!
I am working on an SSRS in Report Builder 3.0. Here is a simplified version of my problem.

I have designed a BAQ that lists the operations, materials, and assembly part numbers for a specific job. For example in the BAQ I might get 10 rows for a job that has 5 operations and two materials. Each op is listed twice, once for each material.

This is fine for my report. I can use the hidden property, along with the ‘previous’ function to hide the duplicate op row. However, when I look at the hourly totals for a job (sum of all ops), the total is including the sum from the duplicate ops as well. My problem is that I can hide the extra op rows, but the data still gets added to the total row.

How can I tell my total row (or expression) to sum only each unique operation for a job, or better yet, sum only the visible rows?

Thanks!
Nate

2

You can use the OVER or PARTITION modifiers to a SUM() function. Look them up.

3

Thanks! I found this site with a bit more info. After some trial and error it seems to be reporting correctly now. :slight_smile:
https://blog.tallan.com/2012/10/30/ssrs-summing-problem-solved/
I ended up adding this calculated field to my BAQ: OpRow =

ROW_NUMBER() OVER (PARTITION BY Jobs.JobHead_PartNum, Jobs.JobAsmbl_PartNum, Jobs.JobOper_OprSeq ORDER BY Jobs.JobHead_PartNum, Jobs.JobAsmbl_PartNum, Jobs.JobOper_OprSeq )

Then in my SSRS total expression, I changed it to:

=Sum(if(Fields!Calculated_OpRow.Value=1,Fields!JobOper_EstSetHours.Value,Nothing))

Thanks for the nudge!
Nate

4

A “nudge” was the best I could give, as I’m not really up to speed on the proper use of them. Only that I did use them in the past. Like you, it was mostly trial and error. :slight_smile:

5

images

6

I just ran into an issue where this row number function works for one BAQ, but gives a server error on another BAQ. I had to increase the queryTimeout to allow extra processing time.