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.

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

Some background

Last year when I was evaluating the new PostgresQL data server (appropriately named “Managed Data Access” or MDA) for ADP, I asked them after at about the 6 month mark if they had a way to query the appropriate General Ledger tables such that we could analyze accounting schedules in Excel. In particular I was very intent on utilizing new Business Intelligence add-in, PowerPivot. Sadly, there was not a one among the dev team that was an accountant and they showed little enthusiasm. I challenged them to write a SQL query that could bring into a PowerPivot data model what I needed to review 28 dealership’s accounting schedules (to make sure the books are clean you see) in one day.

I am a CPA by education (UCLA Accounting), experience (practicing California Automotive CPA for years) and training (keeping up with technology). I knew early on that to be effective in financial management one would need to be able to analyze data, accounting and other operational data. To this end, I have always been an early adopter of technology that would help me do so. I bought my first PC early on with a copy of Lotus 1-2-3 and never looked back.

Throughout those years the PC and spreadsheet software was limited in its usefulness by the availability of data connections in which to get data into the machine to analyze. When I was starting out there were several EDP (Electronic Data Processing) systems in the marketplace to help auto dealers keep track of things. Even though I have worked with a number of different systems, the ones that seemed to dominate always was ADP and Reynolds and Reynolds (R&R). Luckily both of these vendors had built into their systems a feature to extract data. It was slow and it wasn’t pretty. It wasn’t relational. R&R called it “Query Builder” and ADP called it “RPX” (a la RePort Generator EXtract maybe). They both used the Kermit file transfer protocol running in your Telnet session. You see, ADP and R&R both chose the PICK operating system to develop their EDP system. I don’t know the history of these two companies but I smell a partner fall-out and a competitive firm starting up with the renegade partners.

PICK was awesome but it seems a bit like list processing and it did not have a relational database built in. Apparently the business logic and the data logic was all combined together. You could pull information out of this list or that list and work with them locally in Excel but you couldn’t join lists. The query language (ENGLISH) had no functions except the standard non-logical operators. Why was PICK awesome? Because list items could be functions (pointing to other list items) or stored procedures and that was all powerful before SQL Server 1.0 came on the scene in 1989.

I had the pleasure of working with a large auto dealership group that started out with Saturn dealerships in the early 90s and, as a result, was using the EDS dealership system. The EDS system utilized the IBM iSeries AS400 with a DB2 relational database and I was very happy. I could write SQL statements all day long with extensive joins and functions and pull data using ODBC connections to Excel, Tableau, SharePoint and a host of other solutions. ADP bought EDS and quickly moved to convert its dealership base to Drive.

The sad part of this whole story is that it was only last year when there was a viable, relational database in either Reynolds and Reynolds or ADP. [I don’t mention UCS here because even though they had a relational database (DB2), I believe connectivity was blocked].

So the winner is ADP now CDK Global…..

As a condition of conversion from EDS to ADP was that ADP provide the DAP (Data Access Program) to us as many of you also did. Although I didn’t, many of you spun up your own data servers and put together a relational database using ADP‘s data objects; figuring out the relationships etc. I applaud all of you who went that route because you were able to give your dealerships real Business Intelligence (BI) utilizing such tools as Microsoft’s SQL Server Analysis and Reporting Services while the rest of us waited. At the time I worked for a group that didn’t see the capabilities and/or benefits of BI so it was never a priority. The SharePoint (Excel Services) reporting system I implemented and utilized to keep my managers informed ceased to be maintained after I moved on.

At this point, because of the recent addition of the PostgresQL data server to the ADP (CDK Global) system, I will spend my time talking about CDK Global to any dealership that is interested in besting its competitors. My esteem for ADP arises not only from the new data server but the data model itself is incredible. Obviously it was developed over the years with care and completeness by professionals. It is a pleasure to work with such a database and now that it is relational, I am ready to develop exciting data applications.

In all fairness to R&R, I don’t know if they have anything new in this area but I know Bob Brockman’s attitude toward a dealer’s data and it doesn’t seem compatible with open connectivity. Until someone tells me different, I will cease to follow R&R, UCS or any other system while I pursue developing BI solutions for dealers with ADP. I think ADP is moving in the right direction like no other. Perhaps DealerTrack is also on the right path as they are IBM based but, in spite of their unique operating system, ADP is the front runner. It’s kind of like aligning with Microsoft if you want to build powerful business applications. In this blog and in others you will hear me talk about ADP, PostgresQL and Microsoft products for the most part with the occasional Android and iOS mobile device development thrown in because they are everywhere.

One more thing. I write these how-to type posts so the community can take the work I’ve done and build on it. If you read back through my old posts, I blogged about pulling data from DAP using PowerShell mostly and I didn’t do it right or perfect or even efficiently because I am not a PowerShell expert. But it was enough to get a lot of you started quickly and easily pulling data from DAP and working with it in Excel. Some of you are using Out-SQL now and using PowerShell to MERGE in the data differentials I would imagine.

In my blog posts I typically don’t work through the development completely unless it comes from a project for a client (and the client didn’t mind me sharing it with the community). I’ve included in this post enough information to produce accounting schedules in PowerPivot accurately and efficiently. It should also help you to extend the schedule pivot to include data that isn’t available in an ADP printed schedule unless the data is in one of the schedule fields. That’s not to say that the PivotTable isn’t cumbersome at times and with a little extra time couldn’t be improved. Rather than lay it all out for you I expect you all to run with it and make it better and blog about it on your own blog or put the improvements in the comments below.

Schedule related views in the PostgresQL database

I knew that being able to create schedules in PowerPivot would be possible and would depend on my being able to decipher the following to get a pivot table that balances to the GL, namely:

  • What views would be needed
  • What the relationships between the views are and,
  • How can we pivot the results by date, month, account numbers and schedule numbers

The views involved in the process are:

  • glschedule_v
  • glschedulesetup_v
  • glschedulexref_v
  • glschedulexrefbalfwd_v
  • glschedulebalfwddetail_v
  • gljedetail_v

glschedule_v view

The glschedule_v view gives us all of the control numbers on all scheduled accounts by schedule number. If the GL account appears on more than one schedule, the control number will appear as many times. Here’s the query I ran to inspect this table and the first few lines of output:

glschedule

glschedule_v view (click to enlarge)

Most of the columns in the above query output are straightforward. However, the interesting column here is the currentmonth column. After perusing the file and running a few different queries it seems to me that the currentmonth column gives us all the control numbers for the schedule that existed for the currentmonth whether that month was in fact the previous month or the actual current month. For example, running the above query today (11-30-14) with glschedule_v.currentmonth = “11-01-14″ gives me 909 rows of control numbers for the schedule numbers where the “23100” account appears. If I run the same query again with glschedule_v.currentmonth = “10-01-14″, it gives me 828 rows of control numbers for the same schedule numbers. The difference being, it seems to me, that in moving from October to November the net number of control numbers on the schedule dropped due to more control numbers becoming a zero balance than were newly added to the account.

glschedulesetup_v view

The glschedulesetup_v view contains a row for each schedule number setup for the company. This table helps us to restrict the number of times account number detail appears to once even though the account may appear on several different schedule numbers (side-by-side and alone). Here’s the query I ran to inspect this table and the first few lines of output:

glschedulesetup_v view (click to enlarge)

glschedulesetup_v view (click to enlarge)

Off to the right we can see the schedulenumber column which is unique. The interesting column is the account2 column and we can see by the query up top that I selected for records where account2 was null. This means that I only want single account schedules not side-by-side schedules (such as with inventory and flooring together on a schedule). It is possible to add up to 7 accounts on a schedule so there are fields through account7.  For our purposes though, our query works because we reject any schedules with a value in account2 and that eliminates anything other than single account schedules. 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.

glschedulexref_v view

The glschedulexref_v view is the one that tells us where in the gljedetail_v view to get the records to populate the schedule. This is the linking table that tells what General Ledger entries still need to be reported on the schedule (i.e. ones that have not zeroed out by control numbers). Here’s the query I ran to inspect this table and the first few lines of output:

glschedxref_v view (click to enlarge)

glschedxref_v view (click to enlarge)

We can see from the output that the xref part of this view are the columns where the names start with dtl. These fields give us information with which to JOIN this cross reference view (glschedulexref_v) with the gljedetail_v view. The columns with names that don’t begin with dtl are column names that would be used to JOIN this view with glschedule_v view. The view is aptly named “xref” because it simply cross references schedule meta-data in the glschedule_v view with schedule detail found in the gljedetail_v view.

So, the goal is to JOIN the gljedetail_v view to the glschedulexref_v view via the dtl columns and the non-dtl columns to the glschedule_v view. First I will present the SQL query which 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.glschedulesetup_v s ON (a.cora_acct_code = s.cora_acct_code and a.schedulenumber::text = s.schedulenumber::text and a.accountnumber::text = s.account1::text)
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' AND s.account2 IS NULL

The SELECT statement needs to contain all the possible columns I might need in my eventual PowerPivot data model and, my primary view to be queried is glschedule_v which I aliased as “a”. In order to get the desired result set we need to setup the following JOINs with the glschedule_v view:

  • INNER JOIN with the glschedulesetup_v (alias “s”) view so we can select only single account schedules and so we can also SELECT for the s.schedulename column so we will have that in our result set and in the PowerPivot data model.
  • INNER JOIN with glschedulexref_v (alias “b”) view ON glschedulesetup_v (alias “s”) view.
  • INNER JOIN with gljedetail_v (alias “c”) ON glschedulexref_v (alias “b”) view.
  • INNER JOIN with the glcoa_v (no alias) view (the chart of accounts view) so we get an account description to include in our results and in our PowerPivot data model.

Finally, in my WHERE clause I place the following conditions:

  • I require that a.currentmonth equals November AND,
  • I require that the dealership code is a.cora_acct_code AND,
  • I require that the schedule detail returned is only for single account schedules (i.e., no duplication of account number detail allowed)

Next time I will show the results in a pivot table and show you how to deal with “Balance Forward” schedules.

I hope you can see that this type of data extraction would not be possible with DAP and that MDA is really what dealerships need to gain incredible efficiencies going forward. Knowledge is power and efficiency.

Stay tuned.

Using MS Query in Excel to Access ADP’s new MDC PostgreSQL Data Server

Here at Automotive Systems Training we have been reviewing the new MDC (Managed Download Center) product that ADP is offering. It is a PostgreSQL database that mirrors most of the DAP (Data Access Program) Standard Business Data Objects. It’s nice to have a SQL Data Server, however the PostgreSQL database is currently only updated once every 24 hours at night whereas my VBA code and Powershell scripts hitting the DAP data objects directly give me real time data right now.

But in the interest of fairness I am using the very ancient MS Query application (circa 1997) to pull data into my Excel 2010 with PowerPivot CFO Dashboard to let me monitor all aspects of all 28 dealerships every day. And it works. Cumbersome at times, but it works.

Question for those out there that pull data into Excel; what are you using? ADO.Net in VBA code, MS Query. I have been using all-of-the-above plus calling Powershell scripts from VBA. I would like to see what folks are finding to be most robust.

Anyway yesterday I was building a PowerPivot Pivot Table to view daily RO Averages and I had a fit trying to pull Closed ROs that tied to the financial statement counts. Finally I got it to work (within ~1%) by joining the gljedetail table to the servicesalesclosed table. It wasn’t as straight-forward as I had hoped. I am writing this to get it in my head and down on paper how to do these joins in MS Query. In Showcase it was easy as the GUI allowed for multiple outer joins and made it intuitively easy to set them up. In MS Query however, at first glance using the GUI, it tells you basically “ONLY ONE OUTER JOIN ALLOWED PER QUERY”.

However, one can modify the SQL statement directly and have multiple outer joins in MS Query. If you do this though, you can’t subsequently use the GUI to edit the query. You can however continue to edit the query in MS Query using the SQL button which brings up a text editor.

The final query that worked for me (below) selects all rows in gljedetail that have accountnumber 46000, 46100 & 46003 (CP Labor Sale accounts) along with companyid 17 (Anytown Motors) and an accounting date in Sept 2013. These conditions can be seen in the WHERE clause.

The FROM clause then is saying that if the refer (document number – LEFT OUTER JOIN #1) and the cora_acct_code (accountingaccount – LEFT OUTER JOIN #2) in those rows matches (in both ways) any row in the servicesalesclosed table, bring back the data from that row in the servicesalesclosed table and append it to the gljedetail row.

I have put superscripts in places in the query to explain as follows:

SELECT gljedetail_v.accountingdate, gljedetail_v.accountnumber, gljedetail_v.cora_acct_code, gljedetail_v.refer, servicesalesclosed_v.cora_acct_id, servicesalesclosed_v.accountingaccount, servicesalesclosed_v.actualhours, servicesalesclosed_v.actualhourscustomerpay, servicesalesclosed_v.actualhoursinternal, servicesalesclosed_v.actualhourswarranty, servicesalesclosed_v.apptdate, servicesalesclosed_v.apptflag, servicesalesclosed_v.appttime, servicesalesclosed_v.cashierno, servicesalesclosed_v.closedate,…etc…..,servicesalesclosed_v.year, servicesalesclosed_v.cora_acct_code FROM { oj1 ed.gljedetail_v gljedetail_v LEFT OUTER JOIN2 ed.servicesalesclosed_v servicesalesclosed_v ON3 (gljedetail_v.cora_acct_code = servicesalesclosed_v.accountingaccount and gljedetail_v.refer = servicesalesclosed_v.ronumber)4 }5 WHERE ((gljedetail_v.companyid=’17’) AND (gljedetail_v.accountnumber In (‘46000′,’46100′,’46003′)) AND (gljedetail_v.accountingdate>={ts ‘2013-09-01 00:00:00′} And gljedetail_v.accountingdate<={ts ‘2013-09-30 00:00:00′}))

  1. This little “oj” appears to be an indicator to MS Query to expect an outer join coming up.
  2. This LEFT OUTER JOIN statement tells MS Query to return all rows of gljedetail (the first table mentioned right after “oj”) that match the WHERE clause irrespective of the JOIN condition but to return joined rows from servicesalesclosed (the succeeding table mentioned) that satisfy the JOIN condition(s) that is, the condition(s) in the ON clause.
  3. The start of the ON clause mentioned above.
  4. These are the JOIN conditions. In this case there are two LEFT OUTER JOINS (which MS Query GUI doesn’t support). This query works though; you just can’t use the drag and drop etc. I would assume if you added another table to the FROM clause you could add another “And” in the JOIN condition statement of the ON clause.
  5. From curly brace to curly brace is going to be the entire FROM clause including all JOINs, INNER, OUTER, SELF and otherwise.

Any comments or suggestions please feel free. I hope this helps someone.

Getting CSV File Data into SQL Azure

I have been using a trial to see if SQL Azure might be a good place to store regular data extractions for some of my auto dealership clients. I have always wanted to develop using SQL Server because of the (it seems) universal connectivity ability. Nearly all the web development frameworks I use make connection to SQL Server.

So I decided to give it a try and after building a vehiclesalescurrent database as follows:

I then researched the proper way to load the table. BULK INSERT is not supported in SQL Azure; only the Bulk Copy Program (bcp Utility) is available to import csv files. The bcp Utiltiy, being a command line program is not very intuitive. It is rather powerful though as it can do import (in), export (out) and create format files. Whatever possible switch you can think of, it has. I forged ahead and tried to do the import without a format file (which is possible apparently). The Format File is an XML file that tells bcp how the csv file maps to the table in the database.  I received error after error mostly relating to cast issues and  invalid end of record. I was under the impression that a csv file had a rather common end of record code known as CRLF or carraige return/line feed. I opened my csv file in notepad++ with the view codes option on to make sure there wasn’t anything unusual going on. There wasn’t.

Having no success the easy way I decided to create a Format file which would tell bcp what to look for definitively as an end of record. The bcp utility will accept either an XML or non-XML Format file and bcp utility will create either for you. I chose the XML format file because I just felt it might be less buggy. This spit out a file easily but I still had to make a few modifications to the resulting XML file. In particular, bcp got the column separator wrong (“\t” for tab) and I changed it to a comma (“\,” ) as the file was csv. Also the last column of data, in my case column 21 needed the terminator “\r\n” which is the offending return and newline (CRLF) codes! Make sure the slashes are the right way; for some reason (I think I saw it in a blog post!) I put forward slashes and I needed the help desk to straighten me out. Anyway here is the bcp command to create an XML format file:
bcp MyDB.dbo.VehicleSalesCurrent format nul -c -x -f C:\JohnDonnelly\VehicleSalesCurrent.xml -U johndonnelly@xxxxxxxx -S tcp:xxxxxxx.database.windows.net -P mypassword

And here is the final correct Format file that you would  include in the same directory as the vehiclesalescurrent.txt data file when running the bcp utility to upload the data to SQL Azure:

<?xml version=”1.0″?>

<BCPFORMAT xmlns=”http://schemas.microsoft.com/sqlserver/2004/bulkload/format&#8221; xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”&gt;

<RECORD>

<FIELD ID=”1″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”12″/>

<FIELD ID=”2″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”24″ COLLATION=”SQL_Latin1_General_CP1_CI_AS”/>

<FIELD ID=”3″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”24″ COLLATION=”SQL_Latin1_General_CP1_CI_AS”/>

<FIELD ID=”4″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”24″ COLLATION=”SQL_Latin1_General_CP1_CI_AS”/>

<FIELD ID=”5″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”16″ COLLATION=”SQL_Latin1_General_CP1_CI_AS”/>

<FIELD ID=”6″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”11″/>

<FIELD ID=”7″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”16″ COLLATION=”SQL_Latin1_General_CP1_CI_AS”/>

<FIELD ID=”8″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”100″ COLLATION=”SQL_Latin1_General_CP1_CI_AS”/>

<FIELD ID=”9″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”100″ COLLATION=”SQL_Latin1_General_CP1_CI_AS”/>

<FIELD ID=”10″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”100″ COLLATION=”SQL_Latin1_General_CP1_CI_AS”/>

<FIELD ID=”11″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”12″/>

<FIELD ID=”12″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”11″/>

<FIELD ID=”13″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”12″/>

<FIELD ID=”14″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”30″ COLLATION=”SQL_Latin1_General_CP1_CI_AS”/>

<FIELD ID=”15″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”100″ COLLATION=”SQL_Latin1_General_CP1_CI_AS”/>

<FIELD ID=”16″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”100″ COLLATION=”SQL_Latin1_General_CP1_CI_AS”/>

<FIELD ID=”17″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”40″ COLLATION=”SQL_Latin1_General_CP1_CI_AS”/>

<FIELD ID=”18″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”12″/>

<FIELD ID=”19″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”34″ COLLATION=”SQL_Latin1_General_CP1_CI_AS”/>

<FIELD ID=”20″ xsi:type=”CharTerm” TERMINATOR=”,” MAX_LENGTH=”30″/>

<FIELD ID=”21″ xsi:type=”CharTerm” TERMINATOR=”\r\n” MAX_LENGTH=”12″/>

</RECORD>

<ROW>

<COLUMN SOURCE=”1″ NAME=”ID” xsi:type=”SQLINT”/>

<COLUMN SOURCE=”2″ NAME=”StockNo” xsi:type=”SQLNVARCHAR”/>

<COLUMN SOURCE=”3″ NAME=”DealType” xsi:type=”SQLNVARCHAR”/>

<COLUMN SOURCE=”4″ NAME=”SaleType” xsi:type=”SQLNVARCHAR”/>

<COLUMN SOURCE=”5″ NAME=”Branch” xsi:type=”SQLNVARCHAR”/>

<COLUMN SOURCE=”6″ NAME=”SalesDate” xsi:type=”SQLDATE”/>

<COLUMN SOURCE=”7″ NAME=”CustNo” xsi:type=”SQLNVARCHAR”/>

<COLUMN SOURCE=”8″ NAME=”Name” xsi:type=”SQLNVARCHAR”/>

<COLUMN SOURCE=”9″ NAME=”Address” xsi:type=”SQLNVARCHAR”/>

<COLUMN SOURCE=”10″ NAME=”City” xsi:type=”SQLNVARCHAR”/>

<COLUMN SOURCE=”11″ NAME=”Zip” xsi:type=”SQLINT”/>

<COLUMN SOURCE=”12″ NAME=”BirthDate” xsi:type=”SQLDATE”/>

<COLUMN SOURCE=”13″ NAME=”Year” xsi:type=”SQLINT”/>

<COLUMN SOURCE=”14″ NAME=”Make” xsi:type=”SQLNVARCHAR”/>

<COLUMN SOURCE=”15″ NAME=”Model” xsi:type=”SQLNVARCHAR”/>

<COLUMN SOURCE=”16″ NAME=”Body” xsi:type=”SQLNVARCHAR”/>

<COLUMN SOURCE=”17″ NAME=”Color” xsi:type=”SQLNVARCHAR”/>

<COLUMN SOURCE=”18″ NAME=”Mileage” xsi:type=”SQLINT”/>

<COLUMN SOURCE=”19″ NAME=”VIN” xsi:type=”SQLNVARCHAR”/>

<COLUMN SOURCE=”20″ NAME=”Down” xsi:type=”SQLMONEY”/>

<COLUMN SOURCE=”21″ NAME=”Term” xsi:type=”SQLINT”/>

</ROW>

</BCPFORMAT>

As you can see there is a section that defines what the bcp utility should expect regarding each of the fields and a section that defines what column name goes where in the receiving table.

As I mentioned above the Field Terminators are important (of course) and I found that bcp had them incorrect (“\t”) for a CSV file. Also it had the field terminator for the last field the same as all of the others fields (which was “\t”). This needs to be set to the CR LF codes. Also as I mentioned above the terminator on my last column (21) needed to have back slashes, and I think my google search yielded bad advice and I had forward slashes so:

<FIELD ID=”21″ xsi:type=”CharTerm” TERMINATOR=”/r/n” MAX_LENGTH=”12″/>

Obviously it should be:

<FIELD ID=”21″ xsi:type=”CharTerm” TERMINATOR=”\r\n” MAX_LENGTH=”12″/>

With the good Format FIle I ran the following bcp utility statement to handle the import:
# This one works, starts importing at Row 2, make sure dates are YYYY-MM-DD
bcp MyDB.dbo.VehicleSalesCurrent in C:\JohnDonnelly\VehicleSalesCurrent.csv -F 2 -f C:\JohnDonnelly\VehicleSalesCurrent.xml -U johndonnelly@xxxxxxx -S tcp:xxxxxxxx.database.windows.net -P mypassword -e C:\JohnDonnelly\err.txt

The -e switch throws out a nice err.txt file that gives you much more information about errors than the console does. For complete switch explanation follow the bcp Utility link above.

With the above Format File things got a little better however I had to open a case because I kept getting an “Invalid character value for cast specification” error which cited a SQLDate column type.

I learned from the help desk that the SQLDATE columns in my csv file needed to look like yyyy-mm-dd so I had to use a custom format each time if I opened the csv in Excel. You have to reformat the date columns as yyyy-mm-dd then save the file. That is the quick way anyway.

The support person said there was a bug in bcp relating to dates which leads to the last item and that is out of 9,665 rows there were 34 rows that wouldn’t move due to this error:

“Col 12 – Invalid character value for cast specification”

Column 12 was a birth date of SQLDATE type.

Furthermore 32 of the 34 rows were null in Column 12 so I agree with support that bcp Utility or SQL Azure is buggy with regards to SQLDATE.

I hope this helps someone battling the bcp utility with SQL Azure!

Converting to Outside Payroll

Got a call from a programmer who was helping a company convert a 5-store auto dealership company from Reynolds and Reynolds in-house payroll to an outside payroll service. The payroll company I had never heard of but they were very astute and specialized from what I could tell.

The impetus for the change seemed to be a time and attendance software vendor who appeared to use this payroll service company to to produce the payroll using data from his system. It could have been the other way around, the payroll company offered the Time and Attendance software as an important part of their service. I did not get a demo of the Time and Attendance system but the installer and I shared a remote PC connection and it seemed very comprehensive. Time and Attendance systems, if properly setup and managed (the critical part), saves dealerships money; serious money.

So the programmer who contacted me needed help extracting check history and YTD balance data from the Reynolds system. The programmer, Jim Barbour of Sequencing, Inc. was very professional and managed the project very well. Jim needed me to pull all check history as well as pay, tax and deduction year to date totals for the employees. I gave him a quote that made sense and I went to work. I was successful pulling what I thought was all the data and I forwarded a file for his review. Upon verification by the client it was determined that we did not have it all. In particular there were a few deduction items on a several employees that were unaccounted for. Having experience with Reynolds file structures, I planned for this possibility and I had built time in for the possibility that we might need to do extra digging for the odd item. I found the offending miscellaneous deduction codes and related year-to-date totals buried in a multi-valued field called “I/D”. I extracted that data which was tricky because I wanted my condition statement to filter for only those values I was looking for in the multi-valued field. It didn’t work that way. Depending on the field attributes one may have to grab it all and filter programmatically. All was well although the flat file output was rather ugly. Jim being a good sport was able to select for and import the few codes into his conversion.

The interesting thing about this conversion was that the client needed an export for tech hours flagged to go each payday to the service bureau (old term I know, I did payroll in the early 80’s on IBM 360’s for a service bureau subsequently purchased by ADP called Stat-Tab……I was the payroll manager!)  I wrote the export query and it worked out well however the concern on the client’s side was how to make sure the technician’s flags (time entries) are only paid once in cases where the flags are adjusted or if the RO is re-opened and closed again (as in possibly a warranty ticket). The answer was two-fold; one was to isolate flags by the Work Date field (in the query) rather than the RO date or any other date field. Work Date is the date the time entry is actually written, not the date the work is done or the RO was finished or closed. If we stick with the Work Date condition, during the pay cycle only those flags or adjustments done during the cycle get paid regardless of what goes on with the RO. In addition, the client had Body Shops and they wanted to pay flags (time) on ROs as the job was progressing regardless of whether or not the RO was closed in the pay cycle. Using Work Date allowed them to pay on work in progress as well.

The 2nd step in the process from the dealership’s side (I am an accountant, CPA actually but not current on my license) was to schedule the Work In Process – Labor (WIP) general ledger asset account (usually 247) by RO number. Many dealerships do this to control flagging irregularities and I believe the client had these schedules already in place. However, they became extremely important under the new outside payroll processing system. On the credit side of the of the WIP account will be the ROs total of cost of labor, i.e., all tech flags in total costed out on the RO when it was closed. On the debit side will be all the individual flags paid against that RO’s total costed labor. The RO control subtotal should always zero after closing the RO and the subsequent payroll is applied. The office, payroll clerk or office manager, must monitor the schedule often at the start of outside processing and every pay cycle thereafter for problems or irregularities. Techs are smart and some are inclined to “game” the system. Researching ROs with balances and resolving with either a back flag (a charge-back) or a correction to the RO cost and/or costing procedure is imperative so it doesn’t happen again.

All in all a workable solution for those who want to take time and attendance and payroll to the next step in these automobile dealership systems.

WebImage Helper and WebMatrix

Normally I don’t write about WebMatrix but I have been working on an issue for the better part of an hour and was unable to find anyone online that explained how to do this. So here it is and I hope the search engines grab it and it helps save someone some time.

I wanted to store images in my SQL CE database and I also wanted to use the WebImage helper to resize the uploaded images. I knew that datatype “WebImage” wouldn’t be acceptable input for an image column in SQL CE so I searched for help. I two people who helped me somewhat. Matt Osborne was great but he said putting the pictures in CE wasn’t as interesting as saving them to a folder. Arghhh! Anyway he also refered his readers to a “Simple Data Demo” which unfortunately I couldn’t find. His exact words were “Most of the time the API for saving to a database just take a Byte[] of the image, for instance see the Simple Data demo. The second one that save the image off to the file system is the much more interesting sample.” He was my only hope.

Then on Erik Reitan’s blog I found a listing of all the WebImage object properties and methods with no explanation. But that got me going and I was grateful. I found other blogs that gave specific instruction on how to save an uploaded file to a SQL database in the webmatrix context but I knew that the helper would have been designed to abstract away all that stream arcana and such. My problem was to simply upload a picture, resize it into a thumbnail and a small picture, and then store them both in my database.

I set forth 5 variables ‘photo’ to hold the upload which I typed as WebImage because a WebImage method was grabbing the file for me. A couple of other WebImage variables to hold the resized WebImage objects and 2 byte arrays to hold the result of the WebImage helper method GetBytes() which was the key to problem.

WebImage photo = null;
WebImage UserPicture = null;
WebImage UserThumbnail = null;
byte[] ProfileUserPicture = null;
byte[] ProfileUserThumbnail = null;

Below you will see the method that grabs the image file and that is GetImageFromRequest(“Image”).
I then do to resizes (which Matt Osbornes explains very well) and then I get the bytes and the db.Execute(SQLUPDATE) pulls both pictures right in.

photo = WebImage.GetImageFromRequest("Image");
if(photo != null){
UserPicture = photo.Clone().Resize(width: 400, height: 400, preserveAspectRatio: true, preventEnlarge: true);
UserThumbnail = photo.Clone().Resize(width: 128, height: 128, preserveAspectRatio: true, preventEnlarge: true);

ProfileUserPicture = UserPicture.GetBytes("jpeg");
ProfileUserThumbnail = UserThumbnail.GetBytes("jpeg");
}
else
{
ProfileUserPicture = new byte[0];
ProfileUserThumbnail = new byte[0];
}
var SQLUPDATE = "UPDATE Images SET UserPicture=@1, UserThumbnail=@2 WHERE UserID=@0";
var db = Database.Open("Database");
db.Execute(SQLUPDATE, ProfileUserID, ProfileUserPicture, ProfileUserThumbnail);

And, very important, you have to name the input tag the same as the parameter in the GetImageFromRequest() in this case “Image”.

So the input form markup will look like this:

<form action=”” method=”post” enctype=”multipart/form-data”>
<fieldset>

How about a nice picture?<input type=”file” name=”Image” />
</fieldset>
</form>

In summary, use the WebImage.GetBytes(“jpeg”) to convert to byte array and then upload.