Tag Archive: Visual Studio

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.

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…)

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…)