BAQ SUM Group By

Epicor ERP 10
1

I know this has been beaten to death, but I’m not getting this.

I want to connect GLJrnDtl to InvcHed and have it show one line per invoice number

Just as a test, I created the BAQ with just the GLJrnDtl table. In the Display Fields tab I have the ARInvoiceNum & Group By selected. I also created a Calculated field (InvoiceAmt) with the formula:
sum(GLJrnDtl.CreditAmount)

Group By is also selected for that field.

But when I try to analyze, I get this error message:
Severity: Error, Table: , Field: , RowID: , Text: Column ‘Erp.GLJrnDtl.JournalNum’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Query returned 0 row(s).
Query has no more records to return.
Query execution total time: 24.8276 ms.

What am I missing?

2

I think that You are using somewhere JournalNum field also. Find out where you use it or set Group By for the JournalNum field also.

3

Okay, I took that out and got it to work to show one line.

I also added a table criteria at the Query Builder but the sum function is ignoring the filter (InvoiceAmt should be $4,056):

image
image.png781×357 25.9 KB

1 Like
4

could you screenshot your BAQ tables, relationship, so people can see what are you doing ?

5

Are you checking the “group by” box? From the results that you showed, it looks like you changed the calculated field. Can you post the SQL statement?

6

Sure. I took out the group by stuff, and now it’s just showing all the details.

Design

DisplayFields
DisplayFields.png723×419 7.94 KB

and for @Banderson, here’s the SQL statement:

select
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[InvcHead].[Plant] as [InvcHead_Plant],
[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
[InvcHead].[InvoiceDate] as [InvcHead_InvoiceDate],
[InvcHead].[ApplyDate] as [InvcHead_ApplyDate],
[GLJrnDtl].[JEDate] as [GLJrnDtl_JEDate],
[InvcHead].[DueDate] as [InvcHead_DueDate],
(GLJrnDtl.CreditAmount-GLJrnDtl.DebitAmount) as [Calculated_ExtAmt],
(GLJrnDtl.SegValue4 + ‘-’ + GLJrnDtl.SegValue2 + ‘-’ + GLJrnDtl.SegValue1 + ‘-’ + GLJrnDtl.SegValue3) as [Calculated_GLAccount]
from Erp.InvcHead as InvcHead
inner join Erp.GLJrnDtl as GLJrnDtl on
InvcHead.Company = GLJrnDtl.Company
And
InvcHead.InvoiceNum = GLJrnDtl.ARInvoiceNum
and ( GLJrnDtl.SegValue1 >= ‘600000’ )

inner join Erp.Customer as Customer on
InvcHead.Company = Customer.Company
And
InvcHead.CustNum = Customer.CustNum

The Segvalue1 filter is to show only the revenue accounts and not the asset entry