I realize that MAS 500 customers can add fields to the native BI reports (or have their VAR do it for them), but nonetheless, I would like to suggest that some fields be added to some of the out-of-the-box BI reports.
Purchase Order Lines BIE
standard unit cost of the item
receipt date (I know this is problematic in that there can be multiple dates per PO line, but I am just saying it would be useful if it were possible.)
Stock Receipt Lines BIE
item class ID
item userFld 1-6
post date
Items BIE
hazMat yes/no
Sales Order Lines BIE
cust primary addrLine1
cust primary addrLine2
cust primary city
cust primary state ID
cust primary postal code
cust primary country ID
ship to addr ID
ship to addrLine1
ship to addrLine2
ship to city
ship to state ID
ship to postal code
ship to country ID
order qty in stock UOM
stock UOM
salesperson ID
Shipment Lines BIE
cust primary addrLine1
cust primary addrLine2
cust primary city
cust primary state ID
cust primary postal code
cust primary country ID
ship to addr ID
ship to addrLine1
ship to addrLine2
ship to city
ship to state ID
ship to postal code
ship to country ID
hazMat yes/no
Invoice Lines BIE
cust primary addrLine1
cust primary addrLine2
cust primary city
cust primary state ID
cust primary postal code
cust primary country ID
ship to addr ID
ship to addrLine1
ship to addrLine2
ship to city
ship to state ID
ship to postal code
ship to country ID
invoice status
Customer Payments BIE
sales order #
invoice #
(I realize both of the above would be problematic because a payment can be made for multiple orders and invoices, but again, it would be useful if it was possible. I guess the report would need to have a dynamic number of columns depending on the dataset, which is probably not feasible.)
Sales History BIA
cust primary addrLine1
cust primary addrLine2
cust primary city
cust primary state ID
cust primary postal code
cust primary country ID
ship to addr ID
ship to addrLine1
ship to addrLine2
freight amount
sales order #
cust PO #
invoice #
sales qty in sales UOM
returns qty in sales UOM
netSalesQty in sales UOM
default sales UOM of the item
actual sales UOM used in transaction
item short description
fiscalYear
fiscalPeriod
fiscalYearPeriod
item userFld 1-6
Customer Sales History BIA
sales order #
cust PO #
invoice #
by: Ralph A. | over a year ago | 4 - Reports / BI
Comments
The Sales History BIA report should also include tranDate dayOfMonth and postDate dayOfMonth to allow for creating year-over-year YTD reports in SMI.
Nevermind about adding the dayOfMonth fields to this view for SMI reporting purposes. It can easily be accomplished by adding a SQL Expression to the SMI Container.
Also, please add Standard Cost and Standard Price to the Items BIE.
Please add timItemClass.ItemClassID and timWarehouse-->tciAddress.StateID to Posted Inventory Transactions BIE.
Please add unit selling price to the Sales History BIA. Yes, it can be added as a calculated column, but it should be there by default.
Please add warehouse state (ship to) to Sales History BIA.
A recurring theme that keeps coming up as I talk to end users about Business Insights reporting is that MAS would be more user friendly if they did not have to go to so many various reports in order to get the information they need. In other words, instead of having so many reports in each module, whenever possible they would rather have a single gigantic report that could, when they wanted it to, include ALL relevant fields. This would limit the amount of times they have to jump between reports to get to the data they are looking for. It would also allow them to more easily print a single report that has all the data they need in one place.
For example, the Posted Inventory Transactions BIE report in IM is very useful, but as usual it lacks some of the fields the users would like to see. For all Receipt From Vendor transactions, they would like the Posted Inventory Transactions report to include the VendorID, VendorName, and PO#. They realize there are other reports that provide this info, but that’s not what they want. Also, for any transactions in which the inventory being reported on has Lot#s, they want the Lot#s to be shown on the report. They realize there is an Inventory Lots report that shows Lot#s, and that they can set the view for the Posted Inventory Transactions BIE so that the Lot#s for the selected record appear in the Preview area of the screen, but again, that’s not what they're asking for. They want the Lot# to be shown in the main area of the Posted Inventory Transactions BIE. The Posted Inventory Transactions BIE is just one example. The same general concept of what our users would like to see is true for other modules as well. Although, in some ways it may not make sense to limit the concept to modules, since in this example regarding an IM report, users are asking to see VendorName and PO# data on it. By the nature of an ERP system, there will be some module overlap. But in any case, I do think they have valid points in that reducing the total number of BI reports, while making sure the reports that ARE included have ALL relevant data, would make MAS 500 a more user friendly product.
In addition to the requested change to the Shipment Lines BIE report above, please add Lot#. There is no native report that shows which Lot#s went to which customers or which customers received which Lot#s!
Shipment Lines BIE also needs salesperson and sales territory.
Since this seems to be my own personal idea/enhancement thread, and since I seem to be the only one who cares about the missing fields on BIA/BIE reports, I may as well just reiterate some things here.
After encountering this again today, I can't stress this enough... The Sales Order Lines, Shipment Lines, and Invoice Lines BIE reports really should include ALL fields related to BOTH the billing and the shipping addresses, including contact, email address, and phone number. All of these fields should be on all three reports. How does one create a sales or shipment report, and NOT include ADDRESS and contact information?! "WHO ordered it" and "WHERE was it sent" are two of the most basic and important aspects of any sales or shipment report! Thanks for reading!
Here is another example illustrating why the BI reports desperately need your attention.
An accountant wants a report showing 2015 sales dollars for items shipped to customers in California, and also for items that were shipped from any warehouse in California, by month. Because she wants it by month, I asked which date she wanted the report to be based on. I.e., order create date, order ship date, order post date, invoice date, or invoice post date. She said, “invoice date.”
In considering the BI reports that might work, I looked at SO Lines, Shipment Lines, Invoice Lines, and Sales History. None of those will work! The only one that includes invoice date is the Invoice Lines report, and it obviously doesn’t include ship to/from address.
The accountant asked for what should be a very simple and straight forward report, and yet Sage 500 cannot provide it. That’s crazy! This needs to be addressed.
Please add tsoSOLine.Description to the Sales History BIA.