Не работает публикация отчетов в CRM. Помогите!!!!

[Dicora]

Я сделал как Вы просили, добавил права на таблицы, я так понял это нужно было добавить туда где у меня запрос SQL?

Grant Select
ON dbo.Salesplan
TO [NT AUTHORITY\NETWORK SERVICE]
GO

Grant Select
ON dbo.SalesplanData
TO [NT AUTHORITY\NETWORK SERVICE]
GO

Grant Select
ON dbo.Salesfact
TO [NT AUTHORITY\NETWORK SERVICE]
GO

Grant Select
ON dbo.OpportunityBase
TO [NT AUTHORITY\NETWORK SERVICE]
GO

Grant Select
ON dbo.OpportunityExtensionBase
TO [NT AUTHORITY\NETWORK SERVICE]
GO

Grant Select
ON dbo.New_trendBase
TO [NT AUTHORITY\NETWORK SERVICE]
GO

Grant Select
ON dbo.New_trendExtensionBase
TO [NT AUTHORITY\NETWORK SERVICE]
GO

Grant Select
ON dbo.New_departmentBase
TO [NT AUTHORITY\NETWORK SERVICE]
GO

Grant Select
ON dbo.New_departmentExtensionBase
TO [NT AUTHORITY\NETWORK SERVICE]
GO

Grant Select
ON dbo.SystemUserBase
TO [NT AUTHORITY\NETWORK SERVICE]
GO

declare @unix_time_now int
select @unix_time_now = DATEDIFF(s, 'Jan 1 1970',GETUTCDATE())
declare @unix_time_start2 int
declare @unix_time_end2 int

declare @unix_time_start int
declare @unix_time_end int

declare @D datetime
set @D = getdate()

select @unix_time_start2 = DATEDIFF(s, 'Jan 1 1970',DateAdd(  Year , DateDiff(Year , 0, @D)   , 0)   )
select @unix_time_end2 = DATEDIFF(s, 'Jan 1 1970',DateAdd(  day, -1, DateAdd(  Year , DateDiff(Year , 0, @D) +1, 0))   )

select @unix_time_start = DATEDIFF(s, '01.01.1970', @aStartDate   )
select @unix_time_end = DATEDIFF(s, '01.01.1970', @aEndDate )

select
       new_departments.New_name as Company,
       COALESCE(
		(select Sum(Salesplan.Price)
		from Salesplan
		where Salesplan.ParentSalesplanId = (select SalesplanId	
								   from Salesplan 
								   where @unix_time_now BETWEEN DateStart AND DateEnd
								   and ParentSalesplanId is NULL)
        and Salesplan.CreatedBy = trend.OwningUser)
       , 0) as PlanCompany,	
       new_trend.New_Department as GroupName,
	   COALESCE (
		(select Sum(Salesplan.Price)
		from Salesplan
		where Salesplan.ParentSalesplanId = (select SalesplanId	
								   from Salesplan 
								   where @unix_time_now BETWEEN DateStart AND DateEnd
								   and ParentSalesplanId is NULL)
        and Salesplan.ManagerId = new_trend.New_systemuser_new_trend)
       , 0) as PlanGroup,	
 	   Coalesce (
        ( 
        select Sum(Salesfact.Price)
		from Salesfact, OpportunityExtensionBase, OpportunityBase, SystemUserBase
		where Salesfact.DateFact BETWEEN @unix_time_start AND @unix_time_end
        and Salesfact.AgreementCode = OpportunityExtensionBase.New_Agreement_Code
		and OpportunityBase.OpportunityId = OpportunityExtensionBase.OpportunityId
        and ( (OpportunityBase.OwningUser = new_trend.New_systemuser_new_trend
			    and OpportunityBase.OwningUser = SystemUserBase.SystemUserId) or
              (OpportunityBase.OwningUser = SystemUserBase.SystemUserId
                and SystemUserBase.ParentSystemUserId = new_trend.New_systemuser_new_trend)
	     )
        )
       , 0) as FactGroup,		
	   new_trend.New_name as Department,
       COALESCE (
		(select Sum(SalesplanData.Price)
		from SalesplanData
		where SalesplanData.SalesplanId = (select SalesplanId	
								   from Salesplan 
								   where @unix_time_now BETWEEN DateStart AND DateEnd 
								   and ParentSalesplanId is NULL)
        and SalesplanData.DepartmentId = new_trend.New_trendId
        and SalesplanData.Period <> 5 and SalesplanData.Period is NOT NULL
        and SalesplanData.ProductstypeId is NOT NULL
        and SalesplanData.ManagerId is NOT NULL)
       , 0) as PlanDepartment,	
 	   Coalesce (
        ( 
        select Sum(Salesfact.Price)
		from Salesfact, OpportunityExtensionBase, OpportunityBase, SystemUserBase
		where Salesfact.DateFact BETWEEN @unix_time_start AND @unix_time_end
        and Salesfact.AgreementCode = OpportunityExtensionBase.New_Agreement_Code
		and OpportunityBase.OpportunityId = OpportunityExtensionBase.OpportunityId
        and ( (OpportunityBase.OwningUser = new_trend.New_systemuser_new_trend
			    and OpportunityBase.OwningUser = SystemUserBase.SystemUserId) or
              (OpportunityBase.OwningUser = SystemUserBase.SystemUserId
                and SystemUserBase.ParentSystemUserId = new_trend.New_systemuser_new_trend)
	     )
        )
       , 0) as FactDepartment,
       new_trend.New_trendId as DepartmentId,
       new_trend.New_systemuser_new_trend as GroupUserId,
       new_departments.New_departmentId as CompanyId,
      Coalesce (
 (select Sum(O.EstimatedValue_Base) as Sum
 from OpportunityBase as O, OpportunityExtensionBase as OBase
 where O.OpportunityId = OBase.OpportunityId
 and DATEDIFF(s, '01.01.1970 00:00:00', O.ModifiedOn) BETWEEN @unix_time_start and @unix_time_end
 and O.CloseProbability >= 80
 and O.statecode = 0
 and OBase.New_blid = new_trend.New_trendId
)
  
,0) as OpportunityClosedSum,
      Coalesce (
 (select Sum(O.EstimatedValue_Base) as Sum
 from OpportunityBase as O, OpportunityExtensionBase as OBase
 where O.OpportunityId = OBase.OpportunityId
 and DATEDIFF(s, '01.01.1970 00:00:00', O.ModifiedOn) BETWEEN @unix_time_start and @unix_time_end
 and O.CloseProbability < 80
 and O.CloseProbability >= @aProbability
 and O.statecode = 0
 and OBase.New_blid = new_trend.New_trendId
)
  
,0) as OpportunityOpenSum
from
        New_departmentExtensionBase as new_departments
        left outer join New_departmentBase as departments
                        on departments.New_departmentId = new_departments.New_departmentId
                        and departments.statecode = 0			

        left outer join New_trendExtensionBase as new_trend
                        on new_trend.New_buid = departments.New_departmentId
        left outer join New_trendBase as trend
                        on new_trend.New_trendId = trend.New_trendId
                        and trend.statecode = 0
where trend.statecode is NOT NULL
	  and departments.statecode is NOT NULL
	  and new_departments.New_name NOT LIKE '%СНГ%'
order by new_departments.New_name, new_trend.New_Department, new_trend.New_Name

Затем я добавил строку подключения не в Shared Data Source, вот скрин,







Но появляются ошибки.

[Dicora]

Также я удалил справа DataSource1.rds в Shared Data Source

[a33ik]

Этот запрос надо запустить один раз, чтобы дать права на таблицы и не более.

[Dicora]

а где его запустить в SQL-е? вручную???

[Dicora]

Запустил эти запросы

GRANT Select и т.д.

ко всем используемым таблицам в запросе которые я использую

в SQL Management Studio единожды.

затем сохранил отчет в Visual Studio и постандартной схеме выгрузил его в CRM.

та же ошибка

    .
       "dsSalesPlanFact".
      ,

Черт, что же такое??