Dynamics Community Forum Thread Details

Report Writing Assistance - Project Accounting

I have a situation where I need to figure out how to present data for a client in the simplest format possible for the senior management, but at the same time to allow the controller and a couple of the project managers to access the data and dig deep into the information.

Here is - in a nut shell what we are looking for.

From Project Accounting we are looking at the data in the PA30101 Timesheet Detail History and the PA10001 Timesheet Detail (current). I have created a VIEW that is the union of these two tables - as they share for my purposes, the same column names.

What the client needs is to see a summary of the data by PADepartment and displayed on a line by line basis on EmployID with the columns being the PAPROJNUMBER, PABase_QTY, PABase_Unit_Cost and the kicker is that we want to see this detail for each of the 18 (currently) defined PACOSTCATID's in the system.

I am not a SQL Master by any definition, but I have figured out the following script that gets the union created, however, all the PACOSTCATID are listed in a single column. What I would ideally like is a script that breaks out the detail lines and provides a separate coloumn for the PACOSTCATID

We have as example - LABOUR, VACATION, STAT HOLIDAY

Currently there is one column for PACOSTCATID and then a column for the QTY and Dollars, I would like a separate column for each of the "DIFFERENT" PACOSTCATID and list the QTY and DOLLARS under each of them.

Is that possible? What is involved? I am not a programer but I knwo what I need.

I will attach the script to this post separately.