Contact Us:  855-335-6442  |  sales@saberlogic.com

Using Crystal Reports with Point in Time Data

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!

526 S Main St.,Suite 805, Akron, OH 44311 USA
855.335.6442
sales@saberlogic.com

  Custom Development
Integration
Support
Consulting
  Custom Development
Integration
Support
Consulting
  Custom Development
Crystal Reports Viewer
Support
Consulting

saberlogic

 

Epicor ERP

 

Visual ERP

 

Crystal Reports

Copyright © 2002-2018 SaberLogic, Inc  |  Privacy Policy  |  Terms & Conditions  |  Site Map

Epicor® is a registered trademark of Epicor Software Corporation.
VISUAL is a registered trademark of Infor.