Tag Archive: API

Report Automation

I have used SQL Server’s TSQL to generate Excel spreadsheets from Vision data, our ERP, and then deliver the document to various employees. The data in the document is customized to that individual and there are multiple types of audiences that have different reasons for the report, thus need a customized version of it. Even better, Vision can schedule and run the TSQL to make all this happen.

But, I found a better way.

I rebuilt the reports in SQL Server Reporting Services (SSRS). The version available to me does not have the built-in dynamic scheduling capabilities, so I needed a plan b. I turned to the SSRS API and found that some simple C# .NET code could easily call a service and generate a report in various formats, and could accept arguments for the report parameters.

There are 4 versions of most reports that I create: Project Manager, Team Leader, Program Manager, and Management. All of these reports needed to be able to display customized data based on who was actually viewing the report. I easily found a way to pass in a URL based parameter to identify the proper employee data to display. This was really handy for the code, because code calls the report via a URL and I could easily supply each employee, one at a time and get all the reports I needed. With a lot of searching ,and trial and error, I found a tricky way to identify the person that is viewing the report in the browser. Not only could I use SSRS to generate these reports, the very same report can be used by all employees, showing them only the data they need versus all data.

This solution required C# .NET, Vision API, SSRS API, and a number of Views and Stored Procedures using TSQL on MS SQL Server 2008.

.NET, API, C#, Database, ERP, Programming, Reporting, SQL Server, SSRS, TSQL, Vision, Visual Studio

ERP Data to Active Directory

The ERP I work with is called Vision and is created by Deltek. This holds all the data for all departments in the company, including employee data. Active Directory (AD) is Microsoft’s repository of usernames and passwords that can access a network. But AD stores information about the employees which is then used for display in Outlook and Lync: Such as phone, mobile, address, title, name, supervisor, and so on. Having this information accurate is critical. Rather than creating some kind of procedure or policy to govern it, I created code.

I could create a custom workflow DLL using Vision’s API. The catch was, at the time, I had to go back to Visual Studio 2003 to do this, per the requirements of Vision. No problem: Microsoft will send me a copy and I can learn it quick. Using C# .NET, I was able to read the active employee list from Vision, impersonate a network administrator, query AD using LDAP, and match Vision employees to AD objects. Then it was a simple matter of determining what AD called the fields in code and just mapping in the appropriate data from Vision. (more…)

.NET, API, C#, ERP, Programming, Vision, Visual Studio

Clients on a Map

We wanted the ability to show our clients on a map, for various reasons. The GIS department was never able to produce this and anytime I asked about it, there was discussion about using the zip. I wanted a pin point on the address, so I took it upon myself to figure that out.

I knew that I could easily convert an address to latitude and longitude coordinates on Google Maps so I needed to do this for our client addresses stored in our ERP. After some digging around I found out this was called geocoding. Great. Then hunt for geocoding web services began. I had about 15,000 addresses to do and most of the APIs on the Internet would allow a couple hundred in a day, or 1,000 total. Until I found the MapQuest Goecoding API, which provided a very open service that I allowed 5,000 addresses per day. (more…)

.NET, API, C#, ERP, JavaScript, Programming, Vision, Visual Studio

Our Public Website

MSA Professional Services, Inc. Public Website 2012

Visit Site >

In 2011, we hired out the redesign of the public website to a consultant. I was involved only briefly and was able to weigh on the hosting provider as well as the Content Management System (CMS) that was going to be used. That turned out to be less than stellar. We stuck with the host and CMS, but decided have the Marketing Manager and myself redesign the content, graphics, and organization. And we ended up adding features as we went along.

My main role was functionality. The Marketing Manager outsourced the design to the same designer that had been retooling our print materials. That consultant created a mock-up of the site in Photoshop that we acquired and were able to better translate to the web medium. I began modifying the layout and would pass it through Marketing for tweaks and approval, so the graphics and user interface quickly became my responsibility. Tthere were other design elements for the features we were adding that needed graphic treatment as well, putting me in the great position of back-end development all the way through to the user front-end. I loved it. (more…)

.NET, API, C#, ERP, Graphic Design, JavaScript, jQuery, Programming, Project Management, Vision