BAQ / Dashboard Help

Epicor ERP 10
21

If you set it to 3/1/2019 and 5/31/2019, does it include any results from April?

22

yes I do and returns qucikly. I have found if I run it 3/26 to 5/3 it will grab end of march, April and begin of May. Odd I can’t get any closer. I wonder if there is some corrupt data April.

23

So 3/26 - 5/3 works and 3/27 - 5/3 doesn’t ?

What about 3/27 - 3/30?

And then slowly adding days on to the end date (4/1, then 4/2, then 4/3, etc …)?

24

I’d be curious enough to add parameters to the BAQ and run it from in the BAQ desginer to see if you get similar results. To see if it is in the Data/BAQ or when the dashboard does its thing

25

We have the same issue with a dashboard that uses date filters. There were basically two reasons at the time, could be more. If there was no data for the start date, it would not return data. In such cases if we put only the start date, it sometimes worked. Another reason was a calculated field resulting in division by zero somewhere resulting in dashboard not getting any data. We corrected the calculated field to take care of such instances but the first reason still remains.

Thanks

Vinay Kamboj

26

Unfortunately none of that applies to us :frowning:

27

I think there might be an issue with the fact that APInvNumbers aren’t unique.

At least, not by themselves. You could have two different vendors that both submit invoice# 12345.

It’s only when you combine the vendor and the InvoiceNum, do you get a unique record.

Adding a join of:

GLJrnDtl.GroupID = APInvHed.GroupID

helps, doesn’t guarantee that two invoices in the same group (from different suppliers) won’t accidentally match.

EDIT

You need to have

WHERE GLJrnDtl.Company = APInvHed.Company 
  AND GLJrnDtl.APInvoiceNum = APInvHed.InvoiceNum 
  AND GLJrnDtl.GroupID = APInvHed.GroupID 
  AND GLJrnDtl.VendorNum = APInvHed.VendorNum

and your condition of and ( GLJrnDtl.CreditAmount <> 0 ) to only get half the transactions

It might have been crashing when you run April date ranges, because that’s where an APInvNum may have matched multiple past GL postings

Probably why your BAQ showed a GLJrnDtl associated with an invoice from 8 years earlier.

1 Like
28

thanks I will mess with it and see what happens

29

I added the GLJrnDtl.GroupID = APInvHed.GroupID

I ran the Dashboard starting 4/1/2019 and adding a day each time. It ran until I hit 4/19/2019. So I ran from the BAQ only and if I try to run it for just 4/19 it doesn’t work. Could there be a corrupt file somewhere? At least now I think it is more a data issue than a BAQ issue.

30

Did you add the VendorNum relationship too?

1 Like
31

Yes

32

By “doesn’t work” you mean the results are: a) errors out, b) missing some records, c) returns no records, d) returns extra/incorrect records?

33

no returned records

34

@Kimberley - if you query just GLJrnDtl and APInvHed separately (and for the dates giving you problems), do you get records that you can manually match up?

35

If I am understanding you correctly, yes.

36

Try temporarily removing the criteria

GLJrnDtl.CreditAmount <> 0
37

I was wrong I didn’t have the VendorNum join on the tables and I added it. April works now.

1 Like