New Partnership for Deltek Vision and Deltek Professional Services consulting

I am excited to announce the official launch of Version X Solutions!

Version X Solutions is a new partnership that brings together a combined 30 years of Deltek Vision knowledge and experience under one umbrella.

We offer a wide and practical range of products and services including custom modules, custom reports, and even a cloud hosting service.

We’ve put together the beginnings of an inventory, and will continue to add to it as we come up with new ideas and do more interesting work for clients. If you have an extension we need, we’d love to hear about it!

Of course we still offer hourly consulting for nebulous or “figure it out as we go” projects.

You can read more about us and our current offering here.

What does this mean for existing customers?

Our new partnership will not adversely affect the existing relationships I have built with you over the years.

You will continue to get the same great friendly, responsive and most importantly, knowledgeable consulting services you have come to rely on from me.

Thank you for your continued business and I look forward to working with you in the near future.

From now on, please refer to the Version X Solutions site for information about products and services offered.

 

 

 

Credit card transaction import module finished

I have been remiss on posting to my site/blog. I have been swamped, buried in work.

The credit card import module has been completed and running now for 1 week in a production environment with no bugs yet.

Here are the basic functions of the module..

1. User downloads credit card transaction detail from Chase as a CSV file, then drops file into designated directory. User can name file whatever they want.

2. Module imports csv data, archives file and renames it. All imported data is linked back to that specific file so user can look up individual transactions with ease, all the way back to original csv data. If user imports the same file several times or if there are duplicate transactions the process cleans it up.

3. Module processes imported transactions into employee expense reports, adding transactions to appropriate open expense report or creating a new one if needed. Transactions will be added to either the appropriate report based on transaction date, or in current expense report if the appropriate one has been submitted.

4. Employees open their automagically created expense reports and add project number information as well as account for each detail, adding descriptions if needed.

5. User runs a report which shows any import errors, employee expense entry errors or missed imported transactions.

6. When expense reports have been completed, they are posted using normal Vision procedures.

7. When a payment is made to the credit card company, user locates payment serial number in imported data (all transactions are imported, even chargebacks and payments) and processes that payment using a workflow. The workflow creates journal entry files to allocate the cash effect to all projects based upon which details were paid by that payment, and where the employees assigned them. This shows cash flow on a per project basis when that credit card payment is made.

8. Journal entry file can then be posted through normal Vision procedures.

If you would like more information about this module please contact me.

CREDIT CARD TRANSACTION IMPORT AUTOMATION – PART 3

Ok I’ve made a lot of progress over the past two weeks. This is quite a complex project actually… but it’s about finished.

The final results look basically like this:

1. use SSIS (Sql Server Integration Services) to grab the csv files from designated directory and import them into a raw data table.

2. move any transactions from raw import data to the processing table… exclude any transactions that have already been imported.

3. using some data transformations in the form of user defined functions (UDF), make the data look like expense detail transactions

4. insert the expense detail transactions into the expense reports for the corresponding employees

5. allow employees to edit their expense transaction details in their pre-populated expense report

6. show the entirety of credit card expense transactions in a report… that shows the imported data, next to the matching expense detail records in Vision, allowing employees to accurately fill out details on their expenses, and make sure that everything has been accounted for.

7. for the payment to the credit card company, assign the cash disbursement to the different project, phase and tasks reflected in the expense detail transactions so that projects show the cash out for those expenses.

So I’ve completed everything except step 7, which I will complete over the next day or two.

Some really cool things this does:
– puts the CSV files in an archive folder and assigns a unique, logical name to them so you don’t have to worry about losing raw data history

– allows you to compare imported data from the CSV file to the actual data in expense reports by employee and expense report

– allows you to trace any imported transaction back to the actual CSV file it came from… it shows you the file name on the report

– reconciles the amounts imported vs. the amounts in the employee’s expense report so you can make sure you didn’t miss anything

– shows data that was imported into the processing section, but didn’t actually make it into an employee expense report

In a couple weeks I will provide more details on how it actually works with some screen shots of the results.

In the mean time, here is a snapshot of the entire process looked up wrapped up in an elegant diagram in SSIS. Of course, this doesn’t show any details or code… just the big picture logic of it. It’s interesting all the same.

Have an excellent day!

I’ll be at Deltek Insight in a couple weeks, and will be teaching a break out session:
PS-80 Putting Data in the Right Place

If you happen to be at Insight feel free to come say “hello” and give me some feedback on my blog!

Cheers!

Do you have a client using ADP open invoice? – CSV Exporting available for Deltek Vision 6.x and beyond

I have a client firm who’s client recently switched from Oildex to ADP Open Invoice.

In order to submit invoices to ADP Open Invoice, you have to use 1 of the following methods:
1. log in to your client’s ADP interface and us the “one at a time” “create a new invoice” method. With this method you have to enter the header information (or a customer number) and each line item on your invoice one at a time. If you have lots of labor entries, this would be cumbersome at best.

2. log in to your client’s ADP Open Invoice and upload a properly formatted CSV file for each invoice. The csv file contains all the information in the invoice. This means all header information, line items (labor, fees, expenses, consultants, addons, taxes, units) are included in that csv file. Of course, to do this you would need some sort of application or Deltek Vision add on that generates the csv invoice files based on your invoices. This is what I just created for a client, so if you need it, please get in touch with me. NO, it’s not free.

3. implement the PIDX LIDO b2b server to server post methodology. This would be a web app written in .net, which uploads xml based files to your client’s server and saves them in ADP Open Invoice for review and attaching backup. I do not have a solution for this one yet.

4. implement the PIDX RINF RosettaNet application which does everything in #3 for you, but which requires the purchase, install, configuration and maintenance of n add-on application to Vision.

I just completed developing a sql application which exports the invoice data correctly into csv files for import into the ADP Open Invoice application.

This particular client had over 300 invoices for July for this one client. Keep in mind that each invoice requires a separate file for uploading to the Open Invoice site. It took my script 42 seconds to extract the data and create the files (one for each invoice).

The best part is… I can attach this script to a workflow button. Now, when Deltek Vision 7.x comes out, with custom infocenters, I can create an infocenter called “administration”, and put the workflow button there. My client will be able to put in a start date, end date, client number, and click the button… and voila! Magical invoice files! Hundreds of them!

As a reward for my excellent work… I get some chocolate pudding from Whole Foods for dessert!

Cheers!

Case Study – Vision Implementation Restructure

Ronald Lu & Partners, HK had implemented Deltek Vision 5.1 in 2008 and had been live for 2 years.

I received an email from the founder’s son, Bryant Lu saying that although they had been live for 2 years, their accounting department was still doing a lot of accounting and reporting on spread sheets. After I spent a week in their offices I found the following major issues that needed correcting:

0. They needed to convert from Vision 5 to 6, which we accomplished in 2 weeks.

1. Their work break down structure for every project was the same, displaying the 8 standardized phases of a project that they took each project through, but not reflecting the actual milestones and deliverables of the project.  Thus, they were unable to produce meaningful reports from Vision that could show budget vs. actuals, expected revenues or EAC reports tied to project milestones.  Instead, they were maintaining a completely separate set of financials outside of Vision on an organized but extensive set of Excel spreadsheets so they could track consultant expenses, milestones, invoices, and receivables.

2. Their entire set of P+L statements and Project Earnings reports were being produced from spreadsheets in order to show P+L across companies or divisions. The reason for this was because after going live they had adopted a new organizational structure which consisted of studios (which is common in architectural firms).  Because the studios were not represented in the data in Vision, it was not possible to produce any reports based upon studio from the application.  This affected all of their performance reporting and financial reporting.

Just tackling these two issues implied that I would have to do the following:

1. Restructure the work break down structure (WBS) for every project in the database to reflect true milestones and deliverables (or start over from scratch with a new database and a new go live date, which would mean re-importing historical data… essentially doing their implementation completely over).

2. Either convert their current organization scheme to one that considered the studios, or find some way to generate their entire financial report set using a custom field in the project infocenter which would reflect the studio a project was assigned to.
While the problems and solutions described above are only numbered 1 and 2, there are many implied requirements that would come into play as I executed this project over the next 4 months.  Some of the issues I encountered and resolved were:

– In Deltek Vision, when you key convert a WBS item like a phase or task to move it to another project, phase or task (not merely changing the number), it completely ignores the invoice history data during the key conversion process.  Since we decided we would key convert every project into a new, meaningful work breakdown structure, this meant that I had to write sql scripts to convert the invoice history as well.

– The projects that RLP undertakes can last many years, and often have over 100 phases and tasks. The interface for Deltek Vision does not yet allow a user to update fee or budget amounts in a grid like format that shows the entire project structure.  During the execution of this project I built a Microsoft Access based application they could use for both data entry for the conversion (to enter the WBS for projects into Vision) and to use after the conversion for adjusting fees and budgets for projects, as well as enter fees and budgets for new projects.

– Since it was decided not to restructure their organizational scheme in Vision (this was a management decision) it was necessary to develop custom reports to satisfy their entire financial reporting set.  This included P+L reports, balance sheet reports and forecasting reports.

– Because of the language barriers (Vision is not yet localized in Cantonese, the Hong Kong version of Chinese), it was decided we would not implement Resource Planning.  Also, some projects could actually commence and continue in execution for months before actually becoming a signed project.  Thus, it was necessary to in a sense “bill time to opportunities”.  So instead we came up with a solution for revenue and opportunity forecasting using the project info center only.

There were of course many more issues that we worked through and solved throughout the course of the project.  Bottom line is that we completed and went live with the new project structure and report set by December of that year.  Working on site, together with their executive and accounting departments we were able to complete 6 to 8 months of work in 5 months.  

Here are some example images and reports from after their implementation:

Click for full size.

This shows the mission critical business path, order of priority for phased restructure, area of effort and timeline for the phased restructuring of their Vision implementation.
(I will be adding to this case study with more images and samples… later this week)

What exactly is a stored procedure? (for the laymen)

Ah… the magical stored procedures!

I have heard people talk about stored procedures for Vision, heard people ask about them, heard people say they can write them.  But what exactly IS a stored procedure?

A stored procedure is a batch of programming code written in T-SQL (short for transact SQL) that can be called upon from within SQL Server.  It is a set of instructions with a beginning, an end, a name, and some parameters (variables) that can be passed to it.

Stored procedures are constructed like this:

create procedure z_custom_my_fabulous_magical_code(@variable1 varchar(50), @variable2 int)
as
BEGIN

…. code goes here…

END

That’s the basic idea. What happens where “…code goes here…” is entirely up to the creator of that stored procedure… the programmer.

Because of the nature of the SQL programming language, and because of the power of it, it is important to have a test database upon which someone can test their programming talents or essays so that critical data is not deleted, altered or otherwise destroyed.

Because SQL is a programming language, then really only programmers (aka coders) have any business mucking around in there.

Thus, if someone tells you they can write/craft or otherwise produce those magical “stored procedures” to move data around or otherwise manipulate Deltek Vision for you. Be cautious.  Make sure that person can actually do what they claim.. that they have lots of programming experience, that they know the Deltek Vision database schema well, that in effect they know what the hell they are doing!

So how many stored procedures have I written?  Hundreds.  As well as hundreds of inline functions, scalar functions, views and crafted entire databases and applications from merely a clean slate and my brain.  That’s because I’m a programmer!

Don’t have one of those?  Go get one… or call me.

Cheers!

p.s. here’s an interesting one I wrote for analyzing Vision data… just for kicks.

ALTER procedure [dbo].[z_pr_pgc_dependent_table_assessor](@databasename varchar(255), @findstring varchar(255), @andclause varchar(8000), @optionPrint varchar(1)=’Y’)

as
BEGIN

— SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘tbTest’) AND indid < 2

select ‘TABLE COLUMNS ANALYSIS’ AS DESCRIPTION
, @DATABASENAME AS DATABASENAME
, @FINDSTRING AS FINDSTRING
, @ANDCLAUSE AS ANDCLAUSE

create table #mytables(objectid varchar(255), tablename varchar(255), columnname varchar(255), relevantrowcount bigint)

declare @runsql varchar(8000)

— get relevant table list
set @runsql = ‘
insert #mytables(objectid, tablename, columnname, relevantrowcount)
select t.object_id, t.name as ”table_name”, c.name as ”column_name”, -1 as ”rowcount”
from [‘+@databasename+’].sys.tables t
inner join [‘+@databasename+’].sys.all_columns c
on c.object_id = t.object_id

where c.name like ”%’+@findstring+’%” ‘
+ isnull(‘ and ‘+@andclause,”) + ‘
group by t.object_id, t.name, c.name
order by t.name, c.name ‘

if @optionprint = ‘y’ print @runsql
exec(@runsql)

declare @objectid varchar(255), @tablename varchar(255), @thiscolumn varchar(255), @rowcount bigint

select top 1 @objectid = objectid, @tablename = tablename, @thiscolumn = columnname
from #mytables where relevantrowcount = -1

— loop through relevant tables getting relevant row counts
while isnull(@objectid,”) <> ”
begin

set @runsql =
‘update #mytables
set relevantrowcount = (select count(*) from [‘+@databasename+’].dbo.[‘ + @tablename + ‘] where isnull(‘ + @thiscolumn + ‘,””)<> ”” )
where tablename = ”’ + @tablename + ”’ and columnname = ”’ + @thiscolumn + ”’ and relevantrowcount = -1 ‘
if @optionprint = ‘y’ print @runsql
exec(@runsql)

set @objectid = ”

select top 1 @objectid = objectid, @tablename = tablename, @thiscolumn = columnname
from #mytables where relevantrowcount = -1
end

— spit out table stats
select *
from #mytables
order by tablename, columnname

declare @oldtable varchar(255)=”
— now sample data from those tables where it looks relevant
set @tablename = ”

select top 1 @tablename = tablename
, @thiscolumn = columnname
, @rowcount = relevantrowcount
, @runsql = case when relevantrowcount > 0 then
(case when relevantrowcount < 10
then ‘select * from [‘+@databasename+’].dbo.’ + tablename
else ‘select top 3 * from [‘+@databasename+’].dbo.’ + tablename
end )
else ‘print ””’
end
from #mytables
where tablename != @oldtable
and tablename > @tablename

while @tablename <> ‘**all done **’ and @tablename != @oldtable
begin
select @tablename as tablename, @thiscolumn as thiscolumn, @rowcount as [rowcount]

if @optionPrint=’Y’
begin
print @runsql
end
ELSE
begin
EXEC(@runsql)
end

set @oldtable = @tablename

select top 1
@tablename = isnull(tablename,’**all done **’)
, @thiscolumn = columnname
, @rowcount = relevantrowcount
, @runsql = case when relevantrowcount > 0 then
(case when relevantrowcount < 10
then ‘select * from [‘+@databasename+’].dbo.’ + tablename
else ‘select top 3 * from [‘+@databasename+’].dbo.’ + tablename
end )
else ‘print ””’
end
from #mytables
where tablename != @oldtable
and tablename > @tablename

end

drop table #mytables

END

Don’t be fooled

I’m sorry for not posting for so long.   I’ve been buried in work.

Today is a short post because I’m getting a bit frustrated with something with regards to Deltek Vision.

Do you remember the term “Desktop Publishing”?

Desktop Publishing became very popular in the 90’s when CEO’s were putting PC’s and Macs with Pagemaker and telling their secretaries to do their graphic design.

This was due to the misconception that given the right tools, almost anyone can do the job.

It seems that there is a similar misconception with custom reporting and Deltek Vision.

No matter what anyone tells you… here is the blunt truth:

1. You have to be a programmer/developer to be able to create good custom reports.  SSRS is a reporting framework and an IDE.  If you don’t know what an IDE is, you really have no business tackling the task.  Just knowing that custom reports are “in the RDL file” isn’t going to get you or any other consultant far at all.

2. Even if someone has the qualifications of a programmer or developer, it does not mean they will be able to develop custom reports against a Deltek Vision database.  It takes at least a good understanding of general accounting principals as well as a deep understanding of how the data works within the database to both create reports from scratch and/or modify existing reports from the standard set.  In fact, despite what anyone may have told you about simply “taking an existing report and modifying it a little bit to add a column here or change a sorting option there”… it is not a simple task at all.

3. SQL – which stands for “structured query language” is a programming language complete with logical structures, variables, objects in the form of views, functions, stored procedures etc… if those technical terms are over someone’s head, it will take more than just a few weeks, a few months or even a few years to become proficient at database architecture, SQL programming, extracting data… or writing custom reports against any database let alone Deltek Vision.

So the next time you want to get some custom reporting done, talk to a real professional.

For all you consulting firms out there who are trying to provide custom reporting services to your customers that have Deltek Vision 6.x installed, find staff members who have the required skill set so you can give your customers good service.

Cheers!!!!