Is there a way to generate a report or BAQ to determine what inventory hasn’t had
ANY activity in a certain period of time?
1 Like
Sure you could do a BAQ Report that looks at PartTran against your PartBin table or something of the sort, get the Last PartTransaction Date… where Inventory Tran =1
I’m asked for this every year. The hardest part is determining what “No Activity” means.
No purchases, no sales, no Qty Adj’s, no Cost Adj’s, Didn’t appear on any quotes, or quote job details, even if they weren’t ever processed into orders.
I end up making an ODBC Query in Excel of the PartTran table. I pull in the PartNum, MAX(TranDate), TranType. Grouped on TranType.
That’ll give you the last TranDate of each trantype. Then choose which TranTypes to include or exclude in your final analysis.
Thank you for the replies! I’m still learning queries (boy do I miss Report Builder! LOL) but I’ll definitely give this a try after lunch. Thanks again!
Here’s a quick SQL query that will show the last transaction for each part. If you create a view in your database using this query, you can create an external BAQ that is linked to this view, then create a BAQ report which will allow for filters, cutoffs, etc. Note that parts that have never been transacted will show as 1/1/1900. Disclaimer: I have not tested this query in your environment and am not responsible for its use.
SELECT p.Company, p.PartNum, p.PartDescription, ISNULL(t.LastTran, '1/1/1900')
FROM Erp.Part p
LEFT JOIN (SELECT Company, PartNum, MAX(TranDate) As LastTran FROM Erp.PartTran WHERE InventoryTrans = 1 GROUP BY Company, PartNum) t ON p.Company = t.Company AND p.PartNum = t.PartNum
If you wish to also see this by bin, you could slightly tweak the query as the following:
SELECT p.Company, p.PartNum, p.PartDescription, b.WarehouseCode, b.BinNum, ISNULL(t.LastTran, '1/1/1900') As LastTran
FROM Erp.Part p
INNER JOIN Erp.PartBin b ON p.Company = b.Company AND p.PartNum = b.PartNum
LEFT JOIN (SELECT Company, PartNum, WarehouseCode, BinNum, MAX(TranDate) As LastTran FROM Erp.PartTran WHERE InventoryTrans = 1 GROUP BY Company, PartNum, WarehouseCode, BinNum) t ON b.Company = t.Company AND b.PartNum = t.PartNum AND b.WarehouseCode = t.WarehouseCode AND b.BinNum = t.BinNumJoseph - thank you! However, I’m not familiar enough with SQL yet to know how to put that in to the SQL Server Report Builder. 
I tried writing the BAQ per your suggestion above. I received the following error - I’m not sure what it means???
Column ‘Erp.PartTran.PartNum’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Thanks!!
You need to check the group by checkbox on the columns in the Display Tab,
Thank you! Now - next question, please. My “test” is maxing out at 10,000 rows. How do I get it to dump the entire data to Excel? I’m still in BAQ Designer, if that makes a difference. Thanks again!!!
Put the BAQ on a Dashboard and Export Right Clifk to Excel… though running a BAQ with that many rows just as a “TEST” is probably going to cause some perf issues / hang ups
I’m sorry for the stupid questions - still just learning as I go. Thank you for your patience!
How do I add a BAQ to a dashboard. I tried and first got a bunch of errors. I tried to delete that dashboard - it won’t delete. So I tried creating a new one. No error message but no data is showing either.
Is there a step-by-step instruction somewhere??
Thanks again!!
Hi Tricia,
I highly recommend you go over the Ice Tools user guide to get yourself acquainted with the different tools like BAQ and Dashboards and how to use them. This should prove very useful in getting you up to speed.
https://epicweb.epicor.com/doc/Docs/EpicorICETools_UserGuide_100700.pdf
Thanks. It is asking for login information - how do I get that?
Someone in your company should have an EpicWeb account that they can give you to use.
Thanks! I’ll check in the morning. It’s 5:05 - I’m alone in the office. LOL
While I do recommend familiarizing yourself with dashboard designer and other Ice Tools, it sounds like if you want a full export of the BAQ data and the data is over 10,000 rows, the fastest way to get this using your BAQ is to run the BAQ Export process to dump your data to a CSV. In the BAQ Export process, specify CSV and an absolute network file location (or retrieve it from the server running the task agent).
As for Report Builder, the way you should do this is to create a BAQ Report, specifying the BAQ and any options you want to give the user before they run it. Generating the report will create a .rdl (or .rpt for crystal) file on the server in your custom reports folder. In report builder, you can open this file and create report elements to show the data that is already available in the dataset. There are additional steps if you need help on this – Epicor also offers documentation on EpicWeb, and there are training courses available for this that are given frequently online.
1 Like
One thing to note, is that the BAQ Export doesn’t behave well if you have fields with odd ASCII Characters - like a TAB in a PartDescription, or a Single double quote (like using it for the inch symbol ’ " ').
@ckrusen agreed. If you have any description fields that contain problematic characters, you’ll want to change them to calculated fields in the BAQ that remove char(10), char(13), commas, quotes, etc. Otherwise, you need to take the extra step of opening excel first, then importing data specifying fields and delimiters and sorting accordingly or doing some other post processing.
Runtime dashboards have the same issue, but if you deploy the dashboard, it will allow users to directly export to Excel which keeps column formatting. (just included for posterity)
Wow, thanks @josephmoeller. I’ve been working with Epicor for many years
and never know about this!
Thanks,
Norman Hutchins
System Administrator
Howell Laboratories, Inc.
Just curious, are Chinese characters considered problematic characters? We’re in the process of rolling out E10 to our Asian sites. We don’t allow Chinese characters in key fields (Part Num, etc.) but we do allow them in the descriptions so just looking for any gotchas.
Mike