There are many times when importing data into the staging tables for Sage Intacct integrations that I am looking for just a subset of data and not all the data. Importing everything takes time, and if you filter your retrieval in the staging area, you can save time on the import and spend that time on more impactful things.
The screenshots below show you a few ways in which you can apply some of these filters.
In Sage Intacct, the General Ledger Details table contains just that – all the details for all types of transactions, including for all time and all reporting methods.
What if you didn’t want all that data in the staging area? How could you limit it?
For starters, in this example, you would click on the down arrow icon next to the General Ledger Detail table name, and select Table Settings from the context menu presented, see below.
A new screen will pop up with the options you now see.
In the area labeled “Data Import Mode,” set that to All records that fall within a period range transmitted. If we look down to Period Range to import, we see that this is set to a “parameter” named CurrentYearToDate.
This parameter was created by clicking on the Edit Parameter link right under the Period Range to Import. When you click on this, you then have the option to create a Period Range parameter as seen below.
Once you click on the parameter, you have the options to define what that parameter will look like and the range it will grab, it can either be static, dynamic, or a combination of both. For this parameter, we designed it to grab two years of data from whatever the current month and year is. The Start Period is two years backwards from the ending period, which is the current month and year.
Once that is in place, you can then select that parameter as the Period Range to Import and match it up against a column in the table named Entry Date. If the data in the Entry Date column is within the date range of the defined parameter, it will be imported into the staging area table.
Two years of data is still a lot of data. In this example, we only want data that has a Reporting Method of “ACCRUAL” in the General Ledger Detail table. There are many reporting methods available within Sage. If you are only reporting on Accrual data, then you only need to import the Accrual data. As such, our filter now only imports two years of data that meet the reporting method criteria. This shaved quite a bit of time off importing this subset of data into the staging tables.
Revelwood is an award-winning, Platinum Solution Provider for Workday Adaptive Planning. We build solutions for the Office of Finance that minimize your risk by seamlessly incorporating business analytics into your everyday thinking. By combining the software with our best practices and out-of-the-box applications, we help businesses achieve their full potential with Workday Adaptive Planning.
Read more Workday Adaptive Planning Tips & Tricks:
Workday Adaptive Planning Tips & Tricks: Matrix Report “Save” vs “Save As” Options
Workday Adaptive Planning Tips & Tricks: Automating Workflows and Approval Processes