I’m wondering if anyone has written some SQL to calculate resource loading using the relevant database tables, where MSSQL is used as the backend.
In the past we’ve used a VBA application to make resource loading information available to non-P6 users. The application connects to the database, runs each day, and posts resource loading to an Excel file in a shared folder with bar charts that users can filter for their work groups. The results are equivalent to histograms available using the Resource Usage Profile in the UI. The application does this by querying the related tables and using functions to parse calendar data and get resources totals by week for a twenty week window.
The application is overly complex and requires regular maintenance to address things like changes to Windows or MS Office (e.g. 32 bit to 64 bit). I intend to replace most of that complexity with a stored procedure that would avoid much of the data jugging currently required by the application. I’d also like to return totals by day in addition to weekly totals. Hoping someone has already done this in TSQL.
Much thanks for any input and thoughts!