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

The last post entitled Generating ADP (CDK Global) Accounting Schedules in Excel PowerPivot – Part II wrapped up derivation of the SQL statement that we need to use to create the perfect workbook for presenting accounting schedules in Excel using PowerPivot. Continuing to fine tune our project, I made some changes to Part I and Part II so be sure and copy the new SQL statements if you have been following along.

Today we create the PivotTable in a workbook and next time in Part IV I will show you in a video how to customize and use it effectively.

The final version of the query (we created last time) we will use is as follows:

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-12-01 12:00:00 AM'} AND a.cora_acct_code  In ('XXXX-A','YYYY-A','ZZZZ-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-12-01 12:00:00 AM'} AND d.cora_acct_code In ('XXXX-A','YYYY-A','ZZZZ-A')

Notice that I do not include a JOIN with the glschedulesetup_v view because I know that the dealerships whose data I am using in this example do not have single account schedules for all the accounts I may be interested in viewing.

In order to work through this exercise you must have PowerPivot enabled in Excel 2013. It may work with PowerPivot in Excel 2010 but I have not tried it. Once you open Excel and open a blank workbook click on the POWERPIVOT tab as shown below:

Excel 2013 Ribbon POWERPIVOT tab
Excel 2013 Ribbon with POWERPIVOT tab selected (click to enlarge)

In the Data Model section of the POWERPIVOT ribbon click on Manage which will open up POWERPIVOT as follows:

PowerPivot Data Model Manage Ribbon
PowerPivot Data Model Manage Ribbon (click to enlarge)

So in order to add a table to the data model we will us an existing connection to the Managed Data Access server located at your dealerships CDK Global Drive IP address. If you need to create a connection, CDK will be happy to walk you through installing the npgsql OLEDB or  pgsql ODBC drivers etc. In this post I am assuming the adp_report database already has a connection setup on your PC.

To add a table to your data model containing the schedule detail we need, click on Existing Connections in the Get External Data section of the ribbon presented in the Home tab of POWERPIVOT. It will look like this:

"Select an Existing Connection" dialog
The “Select an Existing Connection” dialog box (click to enlarge)

Make sure the adp_report connection has been highlighted in blue and click on the Open button at the bottom which will take you to the first page of the Table Import Wizard like so:

Choose How to Import
Table Import Wizard (click to enlarge)

You could explore all the views available on adp_report by selecting the first item but since we already know what we want and have the SQL statement already built we will select the option to “Write a query that will specify the table to import” which then takes you to the next dialog of the Table Import Wizard as follows:

Specify a SQL Query
Specify a SQL Query (click to enlarge)

Next you change the query name and paste in our query in the large box and click on Validate and you will see that the query validates like so:

SQL Query Validated
Specify a SQL Query -Validated (click to enlarge)

You then click on the Finish button and the query runs and the table is imported like so:

Importing Data
Importing Data (click to enlarge)

The query ran for approximately 20 minutes for two dealerships, one very large and one medium size. When it is done the Table Import Wizard will report Success and tell you the total number of rows imported (in this case 108,071 rows were imported):

Success
Success (click to enlarge)

So now you will see the schedule detail show up in a tab in PowerPivot. The tab will have the same name as the query which will be in this case, Schedule_Query_40. I have renamed it ScheduleDetail (you can rename tables by right clicking on the tab and selecting Rename). Next you will need to click on PivotTable (which PowerPivot will then create in Excel) on the Home ribbon in PowerPivot like so:

Create PivotTable in PowerPivot
Create PivotTable selection in PowerPivot (click to enlarge)

Excel will then exit the Manage window and open the workbook window and will ask you where you want to place the new PivotTable:

Create PivotTable
Create PivotTable (click to enlarge)

Here you should place the PivotTable on a new sheet as we will build an interactive environment around the PivotTable using slicers. After you click OK the sheet with the empty PivotTable on it should look like this:

Empty PivotTable
Empty PivotTable (click to enlarge)

Now that we have a PivotTable there are many useful ways to build your interactive accounting schedules. The best way to show you the possibilities is to continue the process in Part IV utilizing video.

Stay tuned.

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.