One problem that we run into frequently is customers wanting Crystal Reports off of point in time data. What I mean by point in time data is something like a part inventory value, it regularly changes as transactions are made but the analysis you want to do is at based on the inventory value at some specific time.
So as an example, let’s say that we want to compare the daily inventory values of a warehouse over a 3 month period of time. Many ERP systems, like Epicor Vantage or Infor ERP Visual, don’t record daily stock levels. They record it at specific intervals (like monthly), or they track the current value, but then you have to roll back transactions to find out what that value was on a specific date. It makes for a much more complex report and depending on the level of transactions it can be a very long report to run.
But by using another tool, which may already be built into your database system, you can easily create new point in time tables which give you a lot of flexibility in your analysis. This tool is called an Extract Transform and Load (ETL) package. A good example of this would be Data Transformation Services (DTS) built into Microsoft SQL Server or a third party package like Pentaho Data Integrator.
The term Extract Transform and Load is quite descriptive for what this tool does. It extracts data from one source, allows you to transform it or modify it, and then load it into another source. So in our example, we would create a new analysis table which has the part and warehouse information, the date, and the quantity. We first extract the data from our existing tables. In the transform step, we are going to summarize the transactions down so we have that point in time value. Then we load it into our new table.
We can then schedule this process to run once a day to give us daily inventory values. Once it is in this format it becomes very easy to perform any complex analysis we want.
Click here to learn more about our Crystal Reports development and programming services!
