Generating ADP (CDK Global) Accounting Schedules in Excel PowerPivot – Part II

The last post on Generating ADP (CDK Global) Accounting Schedules in Excel PowerPivot – Part I had a lot of information in it and I spent some time this week working on the perfect workbook setup to present the schedules to the end user. As a result, I made some changes to Part I relative to the use of the glschedulesetup_v view.

To begin with, the first dealership whose data I worked with violated one of my assumptions that all scheduled accounts would have at least one schedule setup with only that account on it. That is, the account would appear in a schedule by itself. Since this wasn’t the case, I quickly made some changes to the post.

I left the JOIN of the glschedulesetup_v view in the SQL query with the caveat that:

We are making a large assumption here though; that the dealership has single account schedules for all scheduled accounts. If not, we won’t want to JOIN the glschedulesetup_v view at all. In that case, we will deal with duplications with our slicers in the resulting pivot table.

Well for my work this week, I ended up eliminating the JOIN of the glschedulesetup_v entirely because it didn’t help and, as I will show you, restricting the pivot table to one set of schedule transactions per account is fairly straight forward with the use of slicers.

Another change I made to the original post was in an attempt to optimize the query. Using the EXPLAIN function in PostgresQL I determined that the cost of running a query with a LEFT OUTER JOIN versus an INNER JOIN between the glschedulexref_v view the gljedetail_v view was significant. I thought about it a bit and realized that by definition there had to be a one-to-one relationship between the cross reference entry and the GL detail entry in order for the report to actually be a schedule. We will test this however, to make sure.

So for these reasons, the query implementing the accounting schedule data connection on PowerPivot was reduced to the following:

SELECT a.cora_acct_code, a.hostitemid, a.accountnumber, g.accountdescription, a.companyid, c.journalid, c.refer, a.control, c.accountingdate, c.controltype, c.control2, c.controltype2, c.detaildescription, c.postingamount, c.postingsequence, c.postingtime, a.currentmonth, a.dateofoldestscheditem, a.schedulenumber, b.sequenceno
FROM ed.glschedule_v a
INNER JOIN ed.glschedulexref_v b ON (a.cora_acct_code = b.cora_acct_code AND a.schedulenumber::text = b.schedulenumber::text AND a.accountnumber::text = b.accountnumber::text AND a.control::text = b.control::text AND a.currentmonth = b.currentmonth)
INNER JOIN ed.gljedetail_v c ON (b.dtlcompanyid = c.companyid AND b.dtlaccountingdate = c.accountingdate AND b.accountnumber = c.accountnumber AND b.dtljournalid = c.journalid AND b.dtlpostingsequence = c.postingsequence AND b.dtlpostingtime = c.postingtime AND b.dtlrefer = c.refer AND b.control = c.control)
INNER JOIN ed.glcoa_v g ON (a.cora_acct_code = g.cora_acct_code AND a.accountnumber = g.accountnumber)
WHERE a.currentmonth={ts '2014-11-01 12:00:00 AM'} AND a.cora_acct_code 'XXX-A'

In my experimentation this week I was pleasantly surprised at how wonderful this solution really is. With PowerPivot we can join all kinds of data tables to our schedules to augment the information presented with names, phone numbers on receivable schedules, employees, vehicle details etc. In my final workbook I added queries to pull A/R customer data, employee data, vendor data, car deal data and vehicle inventory data. I made special sheets containing pivot tables for vehicle inventory related accounts and car deal related accounts. It made the accounting schedule review process so much more productive.

We can now move on to the second part of this series where we expand the query to handle “Balance Forward” schedules.

In order to accommodate “Balance Forward” schedules we have to add all the “Balance Forward” detail to the schedule detail table created using the above MDA query. This “Balance Forward” detail is maintained in these two views:

  • glschedulexrefbalfwd_v
  • glschedulebalfwddetail_v

which are analogous to these two views for “Detail Forward” schedules:

  • glschedulexref_v
  • gljedetail_v

To do this, we simply UNION it in. We copy the SELECT statement we made in the last post (above) and paste it again at the end of the first one and insert the word UNION between them. We then change the “Detail Forward” tables in the SELECT statement after the UNION to the “Balance Forward” tables and change the aliases so they don’t conflict (pardon the reversal of e and f) . The result is:

SELECT a.cora_acct_code, a.hostitemid, a.accountnumber, g.accountdescription, a.companyid, c.journalid, c.refer, a.control, c.accountingdate, c.controltype, c.control2, c.controltype2, c.detaildescription, c.postingamount, c.postingsequence, c.postingtime, a.currentmonth, a.dateofoldestscheditem, a.schedulenumber, b.sequenceno
FROM ed.glschedule_v a
INNER JOIN ed.glschedulexref_v b ON (a.cora_acct_code = b.cora_acct_code AND a.schedulenumber::text = b.schedulenumber::text AND a.accountnumber::text = b.accountnumber::text AND a.control::text = b.control::text AND a.currentmonth = b.currentmonth)
INNER JOIN ed.gljedetail_v c ON (b.dtlcompanyid = c.companyid AND b.dtlaccountingdate = c.accountingdate AND b.accountnumber = c.accountnumber AND b.dtljournalid = c.journalid AND b.dtlpostingsequence = c.postingsequence AND b.dtlpostingtime = c.postingtime AND b.dtlrefer = c.refer AND b.control = c.control)
INNER JOIN ed.glcoa_v g ON (a.cora_acct_code = g.cora_acct_code AND a.accountnumber = g.accountnumber)
WHERE a.currentmonth={ts '2014-11-01 12:00:00 AM'} AND a.cora_acct_code  In ('XXX-A')
UNION
SELECT d.cora_acct_code, d.hostitemid, d.accountnumber, h.accountdescription, d.companyid, e.journalid, e.refer, d.control, e.accountingdate, e.controltype, e.control2, e.controltype2, e.detaildescription, e.postingamount, e.postingsequence, e.postingtime, d.currentmonth, d.dateofoldestscheditem, d.schedulenumber, f.sequenceno 
FROM ed.glschedule_v d
INNER JOIN ed.glschedulexrefbalfwd_v f ON (d.cora_acct_code = f.cora_acct_code AND d.schedulenumber::text = f.schedulenumber::text AND d.accountnumber::text = f.accountnumber::text AND d.control::text = f.control::text AND d.currentmonth = f.currentmonth)
INNER JOIN ed.glschedulebalfwddetail_v e ON (f.dtlcompanyid = e.companyid AND f.dtlaccountingdate = e.accountingdate AND f.accountnumber = e.accountnumber AND f.dtljournalid = e.journalid AND f.dtlpostingsequence = e.postingsequence AND f.dtlpostingtime = e.postingtime AND f.dtlrefer = e.refer AND f.schedulenumber::text = e.schedulenumber::text AND f.currentmonth = e.currentmonth AND f.control = e.control)
INNER JOIN ed.glcoa_v h ON (d.cora_acct_code = h.cora_acct_code AND d.accountnumber = h.accountnumber)
WHERE d.currentmonth={ts '2014-11-01 12:00:00 AM'} AND d.cora_acct_code In ('XXX-A')

It should be noted at this point that this query takes a considerable amount of time. I needed to review the accounting schedules on 7 dealerships this week so in my WHERE clause I used a.cora_acct_code In (‘XXX1-A’,’XXX2-A’,’XXX3-A’,’XXX4-A’,’XXX5-A’,’XXX6-A’,’XXX7-A’).  To run the query on the seven dealerships it took about an hour. That’s quick compared to the queries I tested earlier using LEFT OUTER JOIN. For the 7 stores it returned 195,000 rows.

In the next post in the series I will add some other tables to the PowerPivot data model and we will build some pivot tables to present the schedules.

Stay tuned.

Author: John Donnelly

A professional in the automobile business.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s