FSM Report - Item Master Order History

Does anyone know how I might complete this type of report in FSM? Is there a canned report that I have missed?

This is an example of one item for column headers, but I would like to be able to execute this on the entire item master.

Item	Added Date	Status	Orders Placed	Last Ordered
6001547	12/15/2020	Active	307	10/20/2023

In V10, having access to SQL made this fairly simple (example of code below); however, in FSM I do not know how this type of aggregation could be completed unless I am exporting the needed fields from the entire Item and PurchaseOrderLine Business Classes -- of which the latter would be an obviously huge file that I imagine would take an extremely long time to process based on other large data sets I have tried to pull from FSM -- and then perform the aggregations afterwards. Not an efficient rinse and repeat.

Thank you to anyone who might have some guidance on this topic!

SELECT
ITE.ITEM
,FORMAT(ITE.ADDED_DATE, 'yyyy-MM-dd') AS 'ADDED_DATE'
,ITE.ACTIVE_STATUS
,COUNT(PLI.ITEM) AS 'COUNT'
,FORMAT(MAX(PCR.PO_DATE), 'yyyy-MM-dd') AS 'LAST_PO_DATE'

FROM ITEMMAST ITE
LEFT JOIN POLINE PLI
ON PLI.ITEM = ITE.ITEM
LEFT JOIN PURCHORDER PCR
ON PCR.COMPANY = PLI.COMPANY AND PCR.PO_CODE = PLI.PO_CODE AND PCR.PO_NUMBER = PLI.PO_NUMBER AND PCR.PO_RELEASE = PLI.PO_RELEASE

WHERE ITE.ACTIVE_STATUS = 'A'

GROUP BY ITE.ITEM, ITE.ADDED_DATE, ITE.ACTIVE_STATUS

ORDER BY COUNT ASC

Find more posts tagged with

Accepted answers

Stace Webley

You can accomplish this with Replications Sets. You would need to copy the tables to an on-premise server and continue to use SQL or just use Infor's data lake. The initial load will take some time, but subsequent loads would only be the deltas which if processed nightly would take just a few minutes. You can also use Columnar data sets which perform aggregations on tables really quickly. With that option you would need to build a report and deploy it in a menu or just to the report catalog. There are several options is my point and I apologize if these terms are not yet familiar to you since it sounds like you are new to FSM. We struggled initially finding alternatives to our SQL reports, but that was in 2019 and the reporting tools have come a long way since then.

All comments

Stace Webley

ryan-schumacher

Thank you for the suggestions, Stace! We do use Replication Sets in various ways today, though I had hoped there might be a "better" way to access and analyze the data. Historically, I have been pointed towards the use of Compass or Birst, but we have embraced neither -- the former having security restriction concerns and the latter with us not having full licensure of the tool.

Regarding Columnar, is this a tool within Infor or a type of data structure that one can set up with Replication Sets? Admittedly, I have not yet learned exactly what Columnar means in the Infor space, though I am familiar with Columnar Databases (VertiPaq) with regard to importing tables to the Data Model within Excel for use with DAX. Not sure if this is related in some fashion, but it makes sense to me when you note columnar data sets performing quick aggregations.

robert-nevinger

Ryan:

Infor also offers a jdbc driver to datamine the datalaike, which restores a SQL option to produce data sets or reports. You can deploy the jdbc driver onprem and route to alternative BI tools, or cloud based applications deployed to azure or aws which allows you to leverage aws or azure analytic service offerings.

Stace Webley

Columnar is a data structure setup within a replication set. I am not knowledgeable enough on the topic to explain how they are different from a regular replication set, but they do process big data fast. We've built some reports using columnar and our users are happy with how fast they run. Setting them up is probably the easy part because building the report will require the use of LPL. I'm sure there is training available on Infor U on both topics. We are far from experts and do a LOT of trial and error to get what we need.

ryan-schumacher

Thanks, Robert! This is something I can at least mention to our IT staff.

ryan-schumacher

Thanks again, Stace! We can certainly relate to the trial and error, but your explanation helped answer some high-level questions I had.

Copyright © 2025 Infor. All rights reserved.