We are using MS SQL 2000. I do some of the math in the db. I am very weak with rollup, so maybe you can see what I am missing. Example:
Project: Task1 --Task1.1 --Task1.2 -- Has hours --Task1.2.1 --Task1.2.2 -- Has hours --Task1.2.3 --Task1.3 Task2 Task3---Has hours The rollup can get me the totals for a Project, CostArea and each individual task, but not the roll ups for tasks like Task1.2 or Task1. Here's a query I am playing with:(limiting to one project for faster testing :o). SELECT P.PROJ_NAME, TF.TEXT_VALUE AS CostArea, T.TASK_UID, T.TASK_NAME, GA.res_name, SUM(GA.Cost) AS cost, SUM(GA.HOURS) AS hours FROM TBLAllProjects P INNER JOIN ProjectServer_2003.dbo.MSP_TASKS T ON P.PROJ_ID = T.PROJ_ID LEFT OUTER JOIN TBLGovActuals GA ON P.PROJ_ID = GA.proj_id AND T.TASK_UID = GA.TASK_UID LEFT OUTER JOIN ProjectServer_2003.dbo.MSP_TEXT_FIELDS TF ON T.TASK_UID = TF.TEXT_REF_UID WHERE (TF.TEXT_FIELD_ID = 188744479) AND (TF.TEXT_VALUE IS NOT NULL) AND (T.TASK_UID > 0) AND (P.PROJ_ID = 840) GROUP BY P.PROJ_NAME, TF.TEXT_VALUE, T.TASK_UID, T.TASK_NAME, GA.res_name, T.TASK_NAME WITH ROLLUP The only fields that tell you were a Task is, are: Task_lvl(1,2,3, etc) and Task_number(1, 1.1, 1.2, 1.1.1, ext). The Query doesn't know that 1.1, 1.1.1 and 1.2 get rolled up into 1. So I don't see were it's possible to do the roll ups for the top lvl tasks. Thanks Rodney -----Original Message----- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: Thursday, April 20, 2006 5:22 AM To: CF-Talk Subject: Re: StructFindKey Path and evaluate Yes, I was thinking that a cube of this would probably provide most of the desired info in a query (although I haven't looked closely enough to be sure). Does your DB do cube and rollup? On 4/20/06, Thomas Chiverton <[EMAIL PROTECTED]> wrote: > On Wednesday 19 April 2006 17:24, Bruce, Rodney S C-E LCMC > HQISEC/Signal Solutions wrote: > > If there is a better way to do this, please let me know. I don't really > > like the way I am doing it, but unfortunetly I havent come up with > > anything better and I do seem to do things the hard way. > > Getting the DB to do the maths ? -- CFAJAX docs and other useful articles: http://jr-holmes.coldfusionjournal.com/ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238334 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54