if exists(select * from tempdb..sysobjects where id = object_id('tempdb..##qryConsole_51_3'))
drop table ##qryConsole_51_3

SELECT
Бюджеты.sp16116 [Период $Справочник.ПериодыБюджетов]
, Бюджеты.sp16115 [Статья $Справочник.СтатьиБюджетов]
, Бюджеты.sp16127 [Объект $Справочник.ОбъектыСтроительства]
, Бюджеты.sp16126 [Подразделение $Справочник.Подразделения]
, Бюджеты.sp16110 План 
, Бюджеты.sp16111 Факт
INTO ##qryConsole_51_3 FROM 
ra16109 AS Бюджеты
JOIN sc16079 as спр_статьи (nolock) on спр_статьи.id = Бюджеты.sp16115
SET NOCOUNT ON
SELECT Count(*) FROM sc16105 WITH (nolock)
if exists(select * from tempdb..sysobjects where id = object_id('tempdb..##h511'))
drop table ##h511
CREATE TABLE ##h511 (ID CHAR(9) PRIMARY KEY CLUSTERED, DESCR CHAR(34))
INSERT INTO ##h511 SELECT sc.ID, sc.DESCR FROM sc16105 sc WITH (nolock)  WHERE Exists(SELECT *  FROM ##qryConsole_51_3 WHERE ##qryConsole_51_3.[Период $Справочник.ПериодыБюджетов] = sc.ID) 
UPDATE ##h511 SET DESCR = RTrim(sc.DESCR) + ' (' + LTrim(RTrim(sc.CODE)) + ')' FROM sc16105 sc WITH (nolock) INNER JOIN (SELECT ds.DESCR FROM ##h511 ds GROUP BY ds.DESCR HAVING Count(*) > 1) sq ON sc.DESCR = sq.DESCR WHERE ##h511.ID = sc.ID SELECT @@rowcount
UPDATE ##qryConsole_51_3 SET [Период $Справочник.ПериодыБюджетов] = 'ZZZZZZZZZ' WHERE [Период $Справочник.ПериодыБюджетов] NOT IN (SELECT sc.ID FROM sc16105 sc WITH (nolock))
IF @@rowcount > 0
	INSERT INTO ##h511 VALUES('ZZZZZZZZZ', '<>')
SELECT Count(*) FROM sc16079 WITH (nolock)
if exists(select * from tempdb..sysobjects where id = object_id('tempdb..##h512'))
drop table ##h512
CREATE TABLE ##h512 (ID CHAR(9) PRIMARY KEY CLUSTERED, PARENTID CHAR(9), DESCR CHAR(110))
INSERT INTO ##h512 SELECT sc.ID, sc.PARENTID, sc.DESCR FROM sc16079 sc WITH (nolock)  WHERE Exists(SELECT *  FROM ##qryConsole_51_3 WHERE ##qryConsole_51_3.[Статья $Справочник.СтатьиБюджетов] = sc.ID) WHILE 1=1 BEGIN INSERT INTO ##h512 SELECT sc.ID, sc.PARENTID, sc.DESCR FROM sc16079 sc WITH (nolock)  WHERE (sc.ID IN (SELECT PARENTID FROM ##h512))  AND NOT (sc.ID IN (SELECT ID FROM ##h512)) IF @@rowcount = 0 BREAK END 
UPDATE ##h512 SET DESCR = RTrim(sc.DESCR) + ' (' + LTrim(RTrim(sc.CODE)) + ')' FROM sc16079 sc WITH (nolock) INNER JOIN (SELECT ds.DESCR FROM ##h512 ds GROUP BY ds.DESCR HAVING Count(*) > 1) sq ON sc.DESCR = sq.DESCR WHERE ##h512.ID = sc.ID SELECT @@rowcount
UPDATE ##qryConsole_51_3 SET [Статья $Справочник.СтатьиБюджетов] = 'ZZZZZZZZZ' WHERE [Статья $Справочник.СтатьиБюджетов] NOT IN (SELECT sc.ID FROM sc16079 sc WITH (nolock))
IF @@rowcount > 0
	INSERT INTO ##h512 VALUES('ZZZZZZZZZ', '     0   ', '<>')
SELECT Count(*) FROM sc16081 WITH (nolock)
if exists(select * from tempdb..sysobjects where id = object_id('tempdb..##h513'))
drop table ##h513
CREATE TABLE ##h513 (ID CHAR(9) PRIMARY KEY CLUSTERED, PARENTID CHAR(9), DESCR CHAR(109))
INSERT INTO ##h513 SELECT sc.ID, sc.PARENTID, sc.DESCR FROM sc16081 sc WITH (nolock)  WHERE Exists(SELECT *  FROM ##qryConsole_51_3 WHERE ##qryConsole_51_3.[Объект $Справочник.ОбъектыСтроительства] = sc.ID) WHILE 1=1 BEGIN INSERT INTO ##h513 SELECT sc.ID, sc.PARENTID, sc.DESCR FROM sc16081 sc WITH (nolock)  WHERE (sc.ID IN (SELECT PARENTID FROM ##h513))  AND NOT (sc.ID IN (SELECT ID FROM ##h513)) IF @@rowcount = 0 BREAK END 
UPDATE ##h513 SET DESCR = RTrim(sc.DESCR) + ' (' + LTrim(RTrim(sc.CODE)) + ')' FROM sc16081 sc WITH (nolock) INNER JOIN (SELECT ds.DESCR FROM ##h513 ds GROUP BY ds.DESCR HAVING Count(*) > 1) sq ON sc.DESCR = sq.DESCR WHERE ##h513.ID = sc.ID SELECT @@rowcount
UPDATE ##qryConsole_51_3 SET [Объект $Справочник.ОбъектыСтроительства] = 'ZZZZZZZZZ' WHERE [Объект $Справочник.ОбъектыСтроительства] NOT IN (SELECT sc.ID FROM sc16081 sc WITH (nolock))
IF @@rowcount > 0
	INSERT INTO ##h513 VALUES('ZZZZZZZZZ', '     0   ', '<>')
SELECT Count(*) FROM sc16085 WITH (nolock)
if exists(select * from tempdb..sysobjects where id = object_id('tempdb..##h514'))
drop table ##h514
CREATE TABLE ##h514 (ID CHAR(9) PRIMARY KEY CLUSTERED, PARENTID CHAR(9), DESCR CHAR(68))
INSERT INTO ##h514 SELECT sc.ID, sc.PARENTID, sc.DESCR FROM sc16085 sc WITH (nolock)  WHERE Exists(SELECT *  FROM ##qryConsole_51_3 WHERE ##qryConsole_51_3.[Подразделение $Справочник.Подразделения] = sc.ID) WHILE 1=1 BEGIN INSERT INTO ##h514 SELECT sc.ID, sc.PARENTID, sc.DESCR FROM sc16085 sc WITH (nolock)  WHERE (sc.ID IN (SELECT PARENTID FROM ##h514))  AND NOT (sc.ID IN (SELECT ID FROM ##h514)) IF @@rowcount = 0 BREAK END 
UPDATE ##h514 SET DESCR = RTrim(sc.DESCR) + ' (' + LTrim(RTrim(sc.CODE)) + ')' FROM sc16085 sc WITH (nolock) INNER JOIN (SELECT ds.DESCR FROM ##h514 ds GROUP BY ds.DESCR HAVING Count(*) > 1) sq ON sc.DESCR = sq.DESCR WHERE ##h514.ID = sc.ID SELECT @@rowcount
UPDATE ##qryConsole_51_3 SET [Подразделение $Справочник.Подразделения] = 'ZZZZZZZZZ' WHERE [Подразделение $Справочник.Подразделения] NOT IN (SELECT sc.ID FROM sc16085 sc WITH (nolock))
IF @@rowcount > 0
	INSERT INTO ##h514 VALUES('ZZZZZZZZZ', '     0   ', '<>')
SET NOCOUNT OFF
PROVIDER=MSOLAP;Persist Security Info=False;
DATA SOURCE=C:\Users\5791~1\AppData\Local\Temp\qryConsole1.cub;
SOURCE_DSN="Provider=SQLOLEDB;Data Source=ВАСИЛИЙ-ПК\SQLEXPRESS;Initial Catalog=master;Extended Properties=""APP=1CV7"";Use Procedure for Prepare=0;Persist Security Info=False;Current Language=russian";
SOURCE_DSN_SUFFIX="User ID=remt_login;Password=remtpass";
CREATECUBE=CREATE CUBE qryConsole (
	DIMENSION [Период], LEVEL [Все] TYPE ALL, LEVEL [Период] OPTIONS(UNIQUE_KEY, SORTBYNAME), 
	DIMENSION [Статья] DIMENSION_STRUCTURE PARENT_CHILD OPTIONS(UNIQUE_KEY, ALLOWSIBLINGSWITHSAMENAME) MEMBERS_WITH_DATA_IF NON_LEAF_MEMBERS_VISIBLE, 
		HIERARCHY [По группам], LEVEL [Все] TYPE ALL, LEVEL [Статья] ROOT_MEMBER_IF ROOT_IF_PARENT_IS_MISSING, 
		HIERARCHY [Все], LEVEL [Все] TYPE ALL, LEVEL [Статья], 
	DIMENSION [Объект] DIMENSION_STRUCTURE PARENT_CHILD OPTIONS(UNIQUE_KEY, ALLOWSIBLINGSWITHSAMENAME) MEMBERS_WITH_DATA_IF NON_LEAF_MEMBERS_VISIBLE, 
		HIERARCHY [По группам], LEVEL [Все] TYPE ALL, LEVEL [Объект] ROOT_MEMBER_IF ROOT_IF_PARENT_IS_MISSING, 
		HIERARCHY [Все], LEVEL [Все] TYPE ALL, LEVEL [Объект], 
	DIMENSION [Подразделение] DIMENSION_STRUCTURE PARENT_CHILD OPTIONS(UNIQUE_KEY, ALLOWSIBLINGSWITHSAMENAME) MEMBERS_WITH_DATA_IF NON_LEAF_MEMBERS_VISIBLE, 
		HIERARCHY [По группам], LEVEL [Все] TYPE ALL, LEVEL [Подразделение] ROOT_MEMBER_IF ROOT_IF_PARENT_IS_MISSING, 
		HIERARCHY [Все], LEVEL [Все] TYPE ALL, LEVEL [Подразделение], 
	MEASURE [Сумма План] FUNCTION Sum, 
	MEASURE [Сумма Факт] FUNCTION Sum) ;
InsertInto=INSERT INTO qryConsole (
	[Период].Key, [Период].Name, 
	[Статья].Key, [Статья].Name, [Статья].Parent, 
	[Объект].Key, [Объект].Name, [Объект].Parent, 
	[Подразделение].Key, [Подразделение].Name, [Подразделение].Parent, 
	[Сумма План], 
	[Сумма Факт]) 
SELECT 
	h1.ID, h1.DESCR, 
	h2.ID, h2.DESCR, h2.PARENTID, 
	h3.ID, h3.DESCR, h3.PARENTID, 
	h4.ID, h4.DESCR, h4.PARENTID, 
	qryConsole.План, 
	qryConsole.Факт 
FROM ##qryConsole_51_3 qryConsole
	, ##h514 h4
	, ##h513 h3
	, ##h512 h2
	, ##h511 h1
WHERE qryConsole.[Период $Справочник.ПериодыБюджетов] = h1.ID
	 AND qryConsole.[Статья $Справочник.СтатьиБюджетов] = h2.ID
	 AND qryConsole.[Объект $Справочник.ОбъектыСтроительства] = h3.ID
	 AND qryConsole.[Подразделение $Справочник.Подразделения] = h4.ID;
if exists(select * from tempdb..sysobjects where id = object_id('tempdb..##qryConsole_51_3'))
drop table ##qryConsole_51_3