Главная страница Microsoft Project УПРАВЛЕНИЕ ПРОЕКТАМИ
Спрашивайте! Наши эксперты ответят.
 
 FAQFAQ   ПоискПоиск   ПользователиПользователи   ГруппыГруппы   РегистрацияРегистрация 
 ПрофильПрофиль   Войти и проверить личные сообщенияВойти и проверить личные сообщения   ВходВход 

 
Для оперативного ответа рекомендуем задать вопрос в нашей группе Facebook
Более 11.000 человек уже в нашей группе по MS Project
На этом форуме запрещены ссылки, но они разрешены в группе Facebook
 
SQL-отчеты по данным из БД MS Project 2010

 
Начать новую тему   Ответить на тему    Список форумов УПРАВЛЕНИЕ ПРОЕКТАМИ -> Разработка, программирование и интеграция MS Project
Предыдущая тема :: Следующая тема  
Автор Сообщение
Shamakrus
Практик


Зарегистрирован: 08.11.2007
Сообщения: 57

СообщениеДобавлено: Вт, 09 Апр 2013, 10:52    Заголовок сообщения: SQL-отчеты по данным из БД MS Project 2010 Ответить с цитатой

Добрый день!

Столкнулся с тем, что куб расписаний в MS Project Server 2010 неправильно отображает кастомные поля, добавленные как измерения.
Все необходимые данные получил с помощью обращения к БД Reporting через SQL запрос.

В продолжение темы обмена опытом по составлению запросов к БД 2007ой версии MS Project Server, предлагаю в этой теме сделать подборку запросов для 2010ой версии.

SQL-запрос: выборка подробных данных о фактических трудозатратах сотрудников из расписаний. Плюс, подтягиваем кастомные поля.

-------------------------------------------------
use SLSHP2010_ProjectServer_Reporting

select
Projects.ProjectName as Проект,
IsNull(task.TaskName, ADMtask.TaskName) as Задача,
dt.TimeYear as Год,
dt.TimeQuarter as Квартал,
dt.TimeMonthOfTheYear as Месяц,
dt.TimeWeekOfTheYear as Неделя,
dt.TimeDayOfTheMonth as День,
convert(date, Per.StartDate, 101) as [Дата начала недели],
convert(date, Per.EndDate, 101) as [Дата окончания недели],
TSLine.Comment as [Что сделано за неделю],
IsNull(CFValue1.MemberValue, ISNULL(CFValue2.MemberValue, '-')) as Контракт,
case when CFValue1.MemberValue is null then '-' else CFValue2.MemberValue end as [Доп_соглашение],
Resources2.ResourceName as Сотрудник,
Resources2.ResourceGroup as Отдел,
replace(convert(varchar,tz.ActualWorkBillable),'.',',') as [Фактические трудозатраты] -- заменяем . на , т.к. при copy&past в Excel он не понимает разделитель - ","
,TSStatus.Description as [Статус расписания]
,Boss.ResourceName as Руководитель
,case when CFValue2.MemberValue is null then 'Административная' else 'Производственная' end as [Тип задачи]
,case when Proizvod.Capacity = 0 then tz.ActualWorkBillable else tz.ActualWorkBillable/Proizvod.Capacity end as Утилизация

from dbo.MSP_TimesheetLine as TSLine
left outer join dbo.MSP_Timesheet as TS on TS.TimesheetUID = TSLine.TimesheetUID
left outer join dbo.MSP_TimesheetPeriod as Per on Per.PeriodUID = TS.PeriodUID
left outer join dbo.MSP_TimesheetStatus as TSStatus on TSStatus.TimesheetStatusID = TS.TimesheetStatusID
left outer join [MSP_TimesheetActual] as tz on tz.TimesheetLineUID = TSLine.TimesheetLineUID
left outer join dbo.MSP_TimeByDay as dt on dt.TimeByDay = tz.TimeByDay
left outer join dbo.MSP_TimesheetTask as ADMtask on ADMtask.TaskNameUID = TSLine.TaskNameUID
left outer join dbo.MSP_EpmAssignment as assignment on (assignment.AssignmentUID = TSLine.AssignmentUID or assignment.TaskUID = ADMtask.TaskUID)
left outer join dbo.MSP_EpmTask as task on task.TaskUID = assignment.TaskUID
left outer join dbo.MSP_TimesheetResource as ADMResources on ADMResources.ResourceNameUID = TS.OwnerResourceNameUID --
left outer join dbo.MSP_EpmResource as Resources2 on ADMResources.ResourceUID = Resources2.ResourceUID --left outer join dbo.MSP_EpmResource as Resources2 on assignment.ResourceUID = Resources2.ResourceUID
left outer join dbo.MSP_EpmResource as Boss on Resources2.ResourceTimesheetManagerUID = Boss.ResourceUID
left outer join dbo.MSP_TimesheetProject Projects on Projects.ProjectNameUID = TSLine.ProjectNameUID
left outer join dbo.MSP_EpmCPTaskUid0 as con on con.EntityUID = task.TaskUID
left outer join [dbo].[MSP_EpmLookupTable] as CFValue2 on CFValue2.MemberUID = con.CFVal2 --Номер столбца, содержащего значение поля Объект затрат определяется только опытным путем. На тестовом сервере это столбец №1 CFVal1
left outer join [dbo].[MSP_EpmLookupTable] as CFValue1 on CFValue1.MemberUID = CFValue2.ParentMemberUID
left outer join dbo.MSP_EpmResourceByDay as Proizvod on (Proizvod.ResourceUID = ADMResources.ResourceUID and Proizvod.TimeByDay = tz.TimeByDay)
where --Projects.ProjectName = 'Административный' and
tz.ActualWorkBillable > 0
and Resources2.ResourceGroup like '%143%' -- Только департамент 1430 и подчиненные отделы
order by ProjectName, task.TaskName, Resources2.ResourceName
-------------------------------------------------
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Лазутов Сергей (RaraAvis)
Корпоративный Эксперт


Зарегистрирован: 04.10.2007
Сообщения: 2321
Откуда: Москва

СообщениеДобавлено: Вт, 09 Апр 2013, 12:39    Заголовок сообщения: Ответить с цитатой

"left outer join [dbo].[MSP_EpmLookupTable] as CFValue2 on CFValue2.MemberUID = con.CFVal2 --Номер столбца, содержащего значение поля Объект затрат определяется только опытным путем. На тестовом сервере это столбец №1 CFVal1
left outer join [dbo].[MSP_EpmLookupTable] as CFValue1 on CFValue1.MemberUID = CFValue2.ParentMemberUID "
Вот так писать не обязательно.
Можно все данные брать из view с названиями "%UserView"

Ваш способ перестанет работать при первом же восстановлении полей из административного бекапа.
Проверено Smile
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
SanSem
Профи


Зарегистрирован: 28.04.2006
Сообщения: 236
Откуда: Kiev

СообщениеДобавлено: Вт, 09 Апр 2013, 21:38    Заголовок сообщения: Ответить с цитатой

Сергей, какие тогда рекомендации лучших собаководов?
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Shamakrus
Практик


Зарегистрирован: 08.11.2007
Сообщения: 57

СообщениеДобавлено: Пн, 15 Апр 2013, 12:59    Заголовок сообщения: Ответить с цитатой

SanSem писал(а):
Сергей, какие тогда рекомендации лучших собаководов?


Если после переноса или восстановления базы отчет стал сбоить, то нужно изменить имя поля "CFVal2", поставив ему другой порядковый номер (CFVal1 или CFVal3 и т.д.). По другому нельзя, т.к. в самих штатных представлениях Проджект подставляет коды полей, а не наименования для связки. В качестве альтернативы можно подтягивать данные из представлений, которые Проджект уже связал с нужными полями "MSP_EpmTask_UserView" или "[MSPLT_ИМЯ ТАБЛИЦЫ ПОДСТАНОВКИ_UserView] , например "[MSPLT_Наши контракты_UserView]" в базе SLSHP2010_ProjectServer_Reporting.
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
SQLPowerUser
Активный участник


Зарегистрирован: 19.04.2009
Сообщения: 42

СообщениеДобавлено: Чт, 18 Апр 2013, 14:04    Заголовок сообщения: Ответить с цитатой

Коллеги, приветствую! Ниже представлены 2 блока запросов, которые успешно применяются в нашей отчетности. Большинство выборок идут без применения OLAP/кубов/view, сразу, напрямую к таблицам, что ускоряет процесс.
Для удобочитаемости не стал применять тег [Code], но немного съехали пробелы...

Блок 1. Про корпоративные поля CFVal
Список корпоративных полей (они хранятся в базе ProjectServer_Published)
select MD_Prop_Name, * from ProjectServer_Published..MSP_CUSTOM_FIELDS order by 1


Теперь запросы к базе Reporting

Хороший select, который получает все данные про корпоративные поля
select * from MFN_Epm_GetAllCustomFieldInfoData() order by AttributeName

Как получить список значений из таблицы подстановки.
Пусть есть некое поле "Business Unit". В тонком клиенте Project 2010, заходим в корпоративные поля и проваливаемся в таблицу подстановки "Business Unit". Ниже списка значений раскрываем узел "Идентификационные данные системы". Пусть там идентификатор GUID = f271df4f-957a-486e-b52d-014c5a7f8c12 . Соответственно, выборка будет такой:
select * from MSP_EpmLookupTable t1
where LookupTableUID =
'f271df4f-957a-486e-b52d-014c5a7f8c12'
order by MemberValue

Узнать таблицу и столбец где хранится значение конкретного поля (на примере того же 'Business Unit')
Здесь интересен столбец DimensionUID. Он = LookupTableUID из предыдущего запроса.
declare @v uniqueidentifier
select @v = AttributeTypeUID from MSP_EpmMetadataAttribute where AttributeName =
'Business Unit'
select ColumnPoolTableName [Таблица], ColumnNameInPoolForCFValue [Столбец], CustomFieldTypeUID [UID самого поля],
* from MFN_Epm_GetCustomFieldInfo (@v)



Далее запросы к базам Draft и Published
Список проектов с привязанными к нему корпоративными полями. Можно узнать тип проекта, статус и т.д.
Абсолютный список
use ProjectServer_Draft
select t.Proj_UID, t.Proj_Name [Проект], t.MD_Prop_Name [Название поля],
isnull(cast(CFV.Num_Value as varchar(90)), isnull(CFV.text_Value, cast(ltv.LT_Value_Text as varchar(max)))) as [Значение поля]
from (
select p.Proj_Name, cf.MD_Prop_Name, p.Proj_UID, cf.MD_Prop_UID
from ProjectServer_Published.dbo.MSP_Custom_Fields cf, MSP_Projects p
where cf.MD_Ent_Type_UID = 'CECFE271-6660-4ABE-97ED-208D3C71FC18'
and MD_Prop_UID <> '00003211-DC7F-4FCC-B740-53B2AE7FE2C0'--Поле "Статус" почему-то не видно ни в толстом ни в тонком клиенте
) as t
left join MSP_Proj_Custom_Field_Values cfv on (t.Proj_UID = cfv.Proj_UID and t.MD_Prop_UID = cfv.MD_Prop_UID)
left join ProjectServer_Published.dbo.MSP_Lookup_Table_Values ltv on ltv.LT_Struct_UID = cfv.Code_Value
order by 2,3

Только заполненные поля
use ProjectServer_Published
select p.Proj_UID, p.Proj_Name [Проект], cf.MD_Prop_Name as [Название поля],
isnull(cast(CFV.Num_Value as varchar(90)), isnull(CFV.text_Value, cast(ltv.LT_Value_Text as varchar(max)))) as [Значение поля]
from ProjectServer_Draft.dbo.MSP_Proj_Custom_Field_Values cfv
inner join MSP_Custom_Fields cf on cfv.MD_PROP_UID = cf.MD_PROP_UID
inner join ProjectServer_Draft.dbo.MSP_Projects p on p.Proj_UID = cfv.Proj_UID
left join MSP_Lookup_Table_Values ltv on ltv.LT_Struct_UID = cfv.Code_Value
order by 2,3


Список ресурсов с привязанными корпоративными полями. Можно узнать СДРес, Грейд, Роль и т.д.
use ProjectServer_Published
select RES.Res_UID as Res_UID, RES.Res_NAME as [Сотрудник], CF.MD_Prop_Name as [Название поля],
isnull(cast(CFV.Num_Value as varchar(100)), cast(LTV.LT_Value_Text as varchar(8000))) as [Значение поля]
from MSP_Res_Custom_Field_Values CFV
inner join MSP_Custom_Fields CF on CFV.MD_Prop_UID = CF.MD_Prop_UID
inner join MSP_Resources RES on RES.Res_UID = CFV.Res_UID
left join MSP_Lookup_Table_Values LTV on LTV.LT_Struct_UID = CFV.Code_Value
order by 2,3,4




Блок 2. Связь "Проект -> Задача -> Ресурс" с выводом на экран некоторых показателей
Из базы Reporting
use ProjectServer_Reporting
select p.ProjectUID, t.TaskUID, r.ResourceUID, p.ProjectName, t.TaskName, r.ResourceName,
sum(ab.AssignmentCost) [Плановые затраты],
sum(ab.AssignmentActualCost) [Фактические затраты],
sum(ab.AssignmentWork) [Плановые ТРУДОзатраты],
sum(ab.AssignmentActualWork) [Фактические ТРУДОзатраты]
from MSP_EpmAssignmentByDay ab
inner join MSP_EpmAssignment a on ab.AssignmentUID = a.AssignmentUID
inner join MSP_EpmProject p on a.ProjectUID = p.ProjectUID
inner join MSP_EpmTask t on a.TaskUID = t.TaskUID
inner join MSP_EpmResource r on a.ResourceUID = r.ResourceUID
group by p.ProjectUID, t.TaskUID, r.ResourceUID, p.ProjectName, t.TaskName, r.ResourceName
order by p.ProjectName, t.TaskName, r.ResourceName

Из базы Published (наиболее полная картина, но без трудозатрат)
use ProjectServer_Published
select t.Res_UID, r.Res_Name, p.Proj_Name, t.Assn_UID, t.Task_Name,
convert(varchar,t.StartDate,104) [Task_StartDate], convert(varchar,t.FinishDate,104) [Task_FinishDate], r2.Res_Name [Управляющий назначениями]
from (
select isnull(ass.Proj_UID, a.Proj_UID) [Proj_UID], isnull(ass.Task_Name, a.Task_Name) [Task_Name], isnull(ass.Res_UID, a.Res_UID) [Res_UID],
isnull(ass.Assn_Start_Date, a.Assn_Start_Date) [StartDate], isnull(ass.Assn_Finish_Date, a.Assn_Finish_Date) [FinishDate],
isnull(ass.Assn_UID, a.Assn_UID) [Assn_UID], isnull(ass.WRes_UID_Manager, a.WRes_UID_Manager) [WRes_UID_Manager]
from MSP_Assignments_Saved ass
full join MSP_Assignments a on (ass.Assn_UID = a.Assn_UID)
) as t
left join MSP_Projects p on t.Proj_UID = p.Proj_UID
left join MSP_Resources r on t.Res_UID = r.Res_UID
left join MSP_Resources r2 on t.WRes_UID_Manager = r2.Res_UID
order by r.Res_Name, p.Proj_Name, t.Task_Name


Шаблон, который у нас используется в каждом втором отчете. Выводятся на экран только ГЛАВНЫЕ проекты, а сумма с учетом ПОДПРОЕКТОВ (люди могли списать время на проект, который закопан на большой глубине иерархии)
use ProjectServer_Reporting
;with cte as (
select t1.ParentProjectUID [ChildUID], t1.ParentProjectUID [ParentUID], 0 [lev]
from MSP_EpmInternalProjectHierarchies t1
where not exists (select t2.ParentProjectUID from MSP_EpmInternalProjectHierarchies t2 where t1.ParentProjectUID = t2.ChildProjectUID)
union all
select t.ChildProjectUID, cte.ParentUID, lev+1
from MSP_EpmInternalProjectHierarchies t
inner join cte on t.ParentProjectUID = cte.ChildUID
), h as (
select distinct ParentUID [MainParentUID], ChildUID, lev
from cte
), t as (
select p.ProjectUID,
sum(ab.AssignmentWork) [PlanWork],
sum(ab.AssignmentActualWork) [FactWork],
sum(ab.AssignmentCost) [PlanCost],
sum(ab.AssignmentActualCost) [FactCost]
from MSP_EpmAssignmentByDay ab
inner join MSP_EpmProject p on p.ProjectUID = ab.ProjectUID
group by p.ProjectUID
)
select isnull(h.MainParentUID,t.ProjectUID) [Proj_UID], sum(PlanWork) [PlanWork], sum(FactWork) [FactWork], sum(PlanCost) [PlanCost], sum(FactCost) [FactCost]
from t
left join h on t.ProjectUID = h.ChildUID
group by isnull(h.MainParentUID,t.ProjectUID)
option (maxrecursion 0)


Ну и хорошая тема про получение Списанного времени в real-time http://www.pmdoctor.ru/forum/viewtopic.php?t=5816&highlight=


Последний раз редактировалось: SQLPowerUser (Пт, 15 Май 2015, 11:58), всего редактировалось 2 раз(а)
Вернуться к началу
Посмотреть профиль Отправить личное сообщение Посетить сайт автора
Shamakrus
Практик


Зарегистрирован: 08.11.2007
Сообщения: 57

СообщениеДобавлено: Пт, 26 Апр 2013, 9:38    Заголовок сообщения: Ответить с цитатой

SQLPowerUser писал(а):

Как получить список значений из таблицы подстановки.
Пусть есть некое поле "Business Unit". В тонком клиенте Project 2010, заходим в корпоративные поля и проваливаемся в таблицу подстановки "Business Unit". Ниже списка значений раскрываем узел "Идентификационные данные системы". Пусть там идентификатор GUID = f271df4f-957a-486e-b52d-014c5a7f8c12 . Соответственно, выборка будет такой:
select * from MSP_EpmLookupTable t1
where LookupTableUID =
'f271df4f-957a-486e-b52d-014c5a7f8c12'
order by MemberValue
[/color]


SQLPowerUser, спасибо за отличные примеры запросов, да еще с хорошей визуализацией!
Вопрос: если база будет восстановлена, то GUID справочника останется прежним? Можно ли из базы получить GUID справочника по его наименованию и потом использовать в запросе?
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
SQLPowerUser
Активный участник


Зарегистрирован: 19.04.2009
Сообщения: 42

СообщениеДобавлено: Пт, 26 Апр 2013, 21:35    Заголовок сообщения: Ответить с цитатой

Цитата:
Вопрос: если база будет восстановлена, то GUID справочника останется прежним?
Недавно поднимал BackUp на тестовом сервере, GUID`ы справочников остались прежними.

Цитата:
Можно ли из базы получить GUID справочника по его наименованию и потом использовать в запросе?
GUID справочника по его наименованию, я почти всегда беру из хорошего select, который получает все данные про корпоративные поля
select * from MFN_Epm_GetAllCustomFieldInfoData() order by AttributeName
Из этого select-a нам интересны два поля AttributeName и DimensionUID

И теперь без труда получаем список значений из таблицы подстановки:
select * from MSP_EpmLookupTable t1
where LookupTableUID =
'полученный выше DimensionUID'
order by MemberValue
Вернуться к началу
Посмотреть профиль Отправить личное сообщение Посетить сайт автора
Лазутов Сергей (RaraAvis)
Корпоративный Эксперт


Зарегистрирован: 04.10.2007
Сообщения: 2321
Откуда: Москва

СообщениеДобавлено: Пн, 07 Авг 2017, 17:54    Заголовок сообщения: Ответить с цитатой

Более того, как ни удивительно, через API project можно создавать записи в таблицах подстановки с определенными GUID
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Показать сообщения:   
Начать новую тему   Ответить на тему    Список форумов УПРАВЛЕНИЕ ПРОЕКТАМИ -> Разработка, программирование и интеграция MS Project Часовой пояс: GMT + 3
Страница 1 из 1

 
Перейти:  
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах

© 2003 - 2009, Портал MicrosoftProject.ru. Все права защищены.