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.

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