Help with Query for Filtered Report

[Hadi]

Hello Experts,

I am using MS Access to directly access the MS Project Server tables for a
report that I want to generate. I have everything working (tables, queries,
reports) but I dont think my query is very effecient. Basically I have a
crosstab query that uses the following fields as rows (PROJ_ID, PROJ_Name,
TASK_ID, TASK_Name, TASK_START_DATE) and CONV_STRING as a Column, and
TEXT_VALUE as a Value.

I want to report on certain project schedules on the Server and filter on a
couple of Activities that share a common name across all of the schedules (we
are using standard templates). I am using the PROJ_ID to callout the specific
schedules that I want to report on.

The problem I'm having is in the query design view under "Criteria" I am
having to list 3 criterias for each schedule. the First one is to callout the
specifc project ID that want AND to show TASK ID 0. I need the TASK ID 0
because this where all of the Enterprise Text Fields show where I have the
Project Manager's Name, the Area, the Progarm ID that are specific to our
line of work. I have about 120 schedules that I need to report on and you can
imagine that i need to have about 360 criterias in my query. I'm not much of
a SQL programmer but i'm sure there is an easier way to design this query.
This is a sample SQL code for 2 project schedules. Your help will save me a
ton of time

TRANSFORM First(MSP_TEXT_FIELDS.TEXT_VALUE) AS FirstOfTEXT_VALUE
SELECT MSP_TASKS.PROJ_ID, MSP_WEB_Projects.PROJ_NAME, MSP_TASKS.TASK_ID,
MSP_TASKS.TASK_NAME, MSP_TASKS.TASK_START_DATE
FROM ((MSP_TASKS INNER JOIN MSP_WEB_Projects ON MSP_TASKS.PROJ_ID =
MSP_WEB_Projects.PROJ_ID) LEFT JOIN _MSPS_MSP_TEXT_FIELDS AS MSP_TEXT_FIELDS
ON (MSP_TASKS.PROJ_ID = MSP_TEXT_FIELDS.PROJ_ID) AND (MSP_TASKS.TASK_UID =
MSP_TEXT_FIELDS.TEXT_REF_UID)) LEFT JOIN MSP_CONVERSIONS ON
MSP_TEXT_FIELDS.TEXT_FIELD_ID = MSP_CONVERSIONS.CONV_VALUE
WHERE (((MSP_TASKS.PROJ_ID)=1362) AND ((MSP_TASKS.TASK_ID)=0)) OR
(((MSP_TASKS.PROJ_ID)=1362) AND ((MSP_TASKS.TASK_NAME) Like "*Design/Eng*"))
OR (((MSP_TASKS.PROJ_ID)=1362) AND ((MSP_TASKS.TASK_NAME) Like "*Packet to
RMC*")) OR (((MSP_TASKS.PROJ_ID)=1246) AND ((MSP_TASKS.TASK_ID)=0)) OR
(((MSP_TASKS.PROJ_ID)=1246) AND ((MSP_TASKS.TASK_NAME) Like "*Design/Eng*"))
OR (((MSP_TASKS.PROJ_ID)=1246) AND ((MSP_TASKS.TASK_NAME) Like "*Packet to
RMC*"))
GROUP BY MSP_TASKS.PROJ_ID, MSP_WEB_Projects.PROJ_NAME, MSP_TASKS.TASK_ID,
MSP_TASKS.TASK_NAME, MSP_TASKS.TASK_START_DATE
ORDER BY MSP_WEB_Projects.PROJ_NAME
PIVOT MSP_CONVERSIONS.CONV_STRING;

Thank you

 

[Ray McCoppin]

I think that I would build two views. One with Project Level data (task 0)
the other with task level information. For the final query jion these table
together by the Project ID and filter the query by using a SQL IN clause.
The views would be reusable in future reports.
--
Ray McCoppin

http://www.randsmanagement.com
Project Server 2007 Automation Tools
Project Server 2007 Archive Tool
SRS gantt charts