September 17, 2013 at 12:41 pm
We just upgraded our organization from SQL 2000 to SQL 2008/2012 (must say I've been loving the CTE's, Agg functions, and APPLY!). Now I am upgrading the SSAS and I want to give my accounting / analyst group the ability to create/modify their different GL account structures without relying on me. So they have a GUI tool where they can drag and drop their accounts and make their ragged (parent/child) chart of accounts that I want to flow into the OLAP cubes ... but of course according to multiple sources I should not use parent/child hierarchies, but convert them into fixed levels. The accountants like this too ... they want to be able to drill to a specific level.
So the GUI tool creates a table (with a little love from me) like this (although there is a TreeID as well that I have left out for simplicity) which matches up with my ERP data:
CREATE TABLE [tmpAcctTree](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[AcctID] [numeric](18, 0) NULL,
[iLevel] [numeric](18, 0) NULL,
[iOrder] [numeric](18, 0) NULL,
[ParentID] [numeric](18, 0) NULL,
[IsLeaf] [numeric](18, 0) NULL
)
This will add a fairly simple tree (is this too much to paste into a post?):
INSERT INTO [tmpAcctTree]
([AcctID]
,[iLevel]
,[iOrder]
,[ParentID]
,[IsLeaf])
SELECT '692699', '0', '1', '0', '0' UNION ALL
SELECT '692695', '1', '1', '692699', '0' UNION ALL
SELECT '692698', '1', '2', '692699', '0' UNION ALL
SELECT '692616', '2', '1', '692695', '0' UNION ALL
SELECT '692694', '2', '2', '692695', '0' UNION ALL
SELECT '692697', '2', '1', '692698', '0' UNION ALL
SELECT '692598', '3', '1', '692616', '0' UNION ALL
SELECT '692606', '3', '2', '692616', '0' UNION ALL
SELECT '692615', '3', '3', '692616', '0' UNION ALL
SELECT '692651', '3', '1', '692694', '0' UNION ALL
SELECT '692655', '3', '2', '692694', '0' UNION ALL
SELECT '692660', '3', '3', '692694', '0' UNION ALL
SELECT '692665', '3', '4', '692694', '0' UNION ALL
SELECT '692669', '3', '5', '692694', '0' UNION ALL
SELECT '692675', '3', '6', '692694', '0' UNION ALL
SELECT '692683', '3', '7', '692694', '0' UNION ALL
SELECT '692690', '3', '8', '692694', '0' UNION ALL
SELECT '692693', '3', '9', '692694', '0' UNION ALL
SELECT '692596', '4', '1', '692598', '0' UNION ALL
SELECT '692597', '4', '2', '692598', '0' UNION ALL
SELECT '692600', '4', '1', '692606', '0' UNION ALL
SELECT '692601', '4', '2', '692606', '0' UNION ALL
SELECT '692602', '4', '3', '692606', '0' UNION ALL
SELECT '692603', '4', '4', '692606', '0' UNION ALL
SELECT '692604', '4', '5', '692606', '0' UNION ALL
SELECT '692605', '4', '6', '692606', '0' UNION ALL
SELECT '692612', '4', '1', '692615', '0' UNION ALL
SELECT '692613', '4', '2', '692615', '0' UNION ALL
SELECT '692614', '4', '3', '692615', '0' UNION ALL
SELECT '692619', '4', '1', '692651', '0' UNION ALL
SELECT '692620', '4', '2', '692651', '0' UNION ALL
SELECT '692623', '4', '3', '692651', '0' UNION ALL
SELECT '692624', '4', '4', '692651', '0' UNION ALL
SELECT '692631', '4', '5', '692651', '0' UNION ALL
SELECT '692632', '4', '6', '692651', '0' UNION ALL
SELECT '692633', '4', '7', '692651', '0' UNION ALL
SELECT '692634', '4', '8', '692651', '0' UNION ALL
SELECT '692635', '4', '9', '692651', '0' UNION ALL
SELECT '692645', '4', '10', '692651', '0' UNION ALL
SELECT '692646', '4', '11', '692651', '0' UNION ALL
SELECT '692647', '4', '12', '692651', '0' UNION ALL
SELECT '692648', '4', '13', '692651', '0' UNION ALL
SELECT '692649', '4', '14', '692651', '0' UNION ALL
SELECT '692650', '4', '15', '692651', '0' UNION ALL
SELECT '692653', '4', '1', '692655', '0' UNION ALL
SELECT '692654', '4', '2', '692655', '0' UNION ALL
SELECT '692657', '4', '1', '692660', '0' UNION ALL
SELECT '692658', '4', '2', '692660', '0' UNION ALL
SELECT '692659', '4', '3', '692660', '0' UNION ALL
SELECT '692662', '4', '1', '692665', '0' UNION ALL
SELECT '692663', '4', '2', '692665', '0' UNION ALL
SELECT '692664', '4', '3', '692665', '0' UNION ALL
SELECT '692667', '4', '1', '692669', '0' UNION ALL
SELECT '692668', '4', '2', '692669', '0' UNION ALL
SELECT '692671', '4', '1', '692675', '0' UNION ALL
SELECT '692672', '4', '2', '692675', '0' UNION ALL
SELECT '692673', '4', '3', '692675', '0' UNION ALL
SELECT '692674', '4', '4', '692675', '0' UNION ALL
SELECT '692677', '4', '1', '692683', '0' UNION ALL
SELECT '692678', '4', '2', '692683', '0' UNION ALL
SELECT '692679', '4', '3', '692683', '0' UNION ALL
SELECT '692680', '4', '4', '692683', '0' UNION ALL
SELECT '692681', '4', '5', '692683', '0' UNION ALL
SELECT '692682', '4', '6', '692683', '0' UNION ALL
SELECT '692685', '4', '1', '692690', '0' UNION ALL
SELECT '692686', '4', '2', '692690', '0' UNION ALL
SELECT '692687', '4', '3', '692690', '0' UNION ALL
SELECT '692688', '4', '4', '692690', '0' UNION ALL
SELECT '692689', '4', '5', '692690', '0' UNION ALL
SELECT '692692', '4', '1', '692693', '0' UNION ALL
SELECT '692609', '5', '1', '692612', '0' UNION ALL
SELECT '692610', '5', '2', '692612', '0' UNION ALL
SELECT '692611', '5', '3', '692612', '0' UNION ALL
SELECT '692622', '5', '1', '692623', '0' UNION ALL
SELECT '692626', '5', '1', '692631', '0' UNION ALL
SELECT '692627', '5', '2', '692631', '0' UNION ALL
SELECT '692628', '5', '3', '692631', '0' UNION ALL
SELECT '692629', '5', '4', '692631', '0' UNION ALL
SELECT '692630', '5', '5', '692631', '0' UNION ALL
SELECT '692637', '5', '1', '692645', '0' UNION ALL
SELECT '692638', '5', '2', '692645', '0' UNION ALL
SELECT '692639', '5', '3', '692645', '0' UNION ALL
SELECT '692640', '5', '4', '692645', '0' UNION ALL
SELECT '692641', '5', '5', '692645', '0' UNION ALL
SELECT '692642', '5', '6', '692645', '0' UNION ALL
SELECT '692643', '5', '7', '692645', '0' UNION ALL
SELECT '692644', '5', '8', '692645', '0' UNION ALL
SELECT '1110', '5', '1', '692596', '-1' UNION ALL
SELECT '1111', '5', '2', '692596', '-1' UNION ALL
SELECT '1112', '5', '3', '692596', '-1' UNION ALL
SELECT '1113', '5', '4', '692596', '-1' UNION ALL
SELECT '1114', '5', '5', '692596', '-1' UNION ALL
SELECT '1115', '5', '6', '692596', '-1' UNION ALL
SELECT '1119', '5', '7', '692596', '-1' UNION ALL
SELECT '1121', '5', '8', '692596', '-1' UNION ALL
SELECT '1630', '5', '9', '692596', '-1' UNION ALL
SELECT '1847', '5', '10', '692596', '-1' UNION ALL
SELECT '1903', '5', '11', '692596', '-1' UNION ALL
SELECT '2004', '5', '12', '692596', '-1' UNION ALL
SELECT '2161', '5', '13', '692596', '-1' UNION ALL
SELECT '2181', '5', '14', '692596', '-1' UNION ALL
SELECT '2197', '5', '15', '692596', '-1' UNION ALL
SELECT '1116', '5', '1', '692597', '-1' UNION ALL
SELECT '1652', '5', '2', '692597', '-1' UNION ALL
SELECT '1713', '5', '3', '692597', '-1' UNION ALL
SELECT '1777', '5', '4', '692597', '-1' UNION ALL
SELECT '1778', '5', '5', '692597', '-1' UNION ALL
SELECT '1779', '5', '6', '692597', '-1' UNION ALL
SELECT '1852', '5', '7', '692597', '-1' UNION ALL
SELECT '2059', '5', '8', '692597', '-1' UNION ALL
SELECT '2060', '5', '9', '692597', '-1' UNION ALL
SELECT '2071', '5', '10', '692597', '-1' UNION ALL
SELECT '2077', '5', '11', '692597', '-1' UNION ALL
SELECT '1130', '5', '1', '692600', '-1' UNION ALL
SELECT '1131', '5', '2', '692600', '-1' UNION ALL
SELECT '2234', '5', '3', '692600', '-1' UNION ALL
SELECT '1132', '5', '1', '692601', '-1' UNION ALL
SELECT '1996', '5', '1', '692602', '-1' UNION ALL
SELECT '2092', '5', '2', '692602', '-1' UNION ALL
SELECT '2099', '5', '3', '692602', '-1' UNION ALL
SELECT '2126', '5', '4', '692602', '-1' UNION ALL
SELECT '1134', '5', '1', '692603', '-1' UNION ALL
SELECT '1140', '5', '2', '692603', '-1' UNION ALL
SELECT '1611', '5', '1', '692604', '-1' UNION ALL
SELECT '1878', '5', '2', '692604', '-1' UNION ALL
SELECT '1986', '5', '3', '692604', '-1' UNION ALL
SELECT '1989', '5', '4', '692604', '-1' UNION ALL
SELECT '1990', '5', '5', '692604', '-1' UNION ALL
SELECT '2222', '5', '6', '692604', '-1' UNION ALL
SELECT '1129', '5', '1', '692605', '-1' UNION ALL
SELECT '1137', '5', '2', '692605', '-1' UNION ALL
SELECT '1143', '5', '3', '692605', '-1' UNION ALL
SELECT '2199', '5', '4', '692605', '-1' UNION ALL
SELECT '2200', '5', '5', '692605', '-1' UNION ALL
SELECT '2201', '5', '6', '692605', '-1' UNION ALL
SELECT '2205', '5', '7', '692605', '-1' UNION ALL
SELECT '2206', '5', '8', '692605', '-1' UNION ALL
SELECT '2224', '5', '9', '692605', '-1' UNION ALL
SELECT '2225', '5', '10', '692605', '-1' UNION ALL
SELECT '1158', '6', '1', '692609', '-1' UNION ALL
SELECT '1159', '6', '2', '692609', '-1' UNION ALL
SELECT '1160', '6', '3', '692609', '-1' UNION ALL
SELECT '1161', '6', '4', '692609', '-1' UNION ALL
SELECT '1162', '6', '5', '692609', '-1' UNION ALL
SELECT '1165', '6', '6', '692609', '-1' UNION ALL
SELECT '1166', '6', '7', '692609', '-1' UNION ALL
SELECT '1167', '6', '8', '692609', '-1' UNION ALL
SELECT '1170', '6', '9', '692609', '-1' UNION ALL
SELECT '1774', '6', '10', '692609', '-1' UNION ALL
SELECT '1853', '6', '11', '692609', '-1' UNION ALL
SELECT '1899', '6', '12', '692609', '-1' UNION ALL
SELECT '1931', '6', '13', '692609', '-1' UNION ALL
SELECT '2015', '6', '14', '692609', '-1' UNION ALL
SELECT '2016', '6', '15', '692609', '-1' UNION ALL
SELECT '2024', '6', '16', '692609', '-1' UNION ALL
SELECT '2064', '6', '17', '692609', '-1' UNION ALL
SELECT '2065', '6', '18', '692609', '-1' UNION ALL
SELECT '2066', '6', '19', '692609', '-1' UNION ALL
SELECT '2067', '6', '20', '692609', '-1' UNION ALL
SELECT '2089', '6', '21', '692609', '-1' UNION ALL
SELECT '2195', '6', '22', '692609', '-1' UNION ALL
SELECT '2196', '6', '23', '692609', '-1' UNION ALL
SELECT '2228', '6', '24', '692609', '-1' UNION ALL
SELECT '1163', '6', '1', '692610', '-1' UNION ALL
SELECT '1164', '6', '2', '692610', '-1' UNION ALL
SELECT '1169', '6', '3', '692610', '-1' UNION ALL
SELECT '1171', '6', '4', '692610', '-1' UNION ALL
SELECT '1176', '6', '5', '692610', '-1' UNION ALL
SELECT '1568', '6', '6', '692610', '-1' UNION ALL
SELECT '1591', '6', '7', '692610', '-1' UNION ALL
SELECT '2093', '6', '8', '692610', '-1' UNION ALL
SELECT '2094', '6', '9', '692610', '-1' UNION ALL
SELECT '2095', '6', '10', '692610', '-1' UNION ALL
SELECT '2096', '6', '11', '692610', '-1' UNION ALL
SELECT '2097', '6', '12', '692610', '-1' UNION ALL
SELECT '2098', '6', '13', '692610', '-1' UNION ALL
SELECT '2155', '6', '14', '692610', '-1' UNION ALL
SELECT '2163', '6', '15', '692610', '-1' UNION ALL
SELECT '2170', '6', '16', '692610', '-1' UNION ALL
SELECT '2232', '6', '17', '692610', '-1' UNION ALL
SELECT '1157', '6', '1', '692611', '-1' UNION ALL
SELECT '1922', '6', '2', '692611', '-1' UNION ALL
SELECT '2176', '5', '1', '692613', '-1' UNION ALL
SELECT '2177', '5', '2', '692613', '-1' UNION ALL
SELECT '2182', '5', '3', '692613', '-1' UNION ALL
SELECT '2183', '5', '4', '692613', '-1' UNION ALL
SELECT '2187', '5', '5', '692613', '-1' UNION ALL
SELECT '2188', '5', '6', '692613', '-1' UNION ALL
SELECT '2190', '5', '7', '692613', '-1' UNION ALL
SELECT '2138', '5', '1', '692614', '-1' UNION ALL
SELECT '2141', '5', '2', '692614', '-1' UNION ALL
SELECT '2239', '5', '3', '692614', '-1' UNION ALL
SELECT '2090', '5', '1', '692619', '-1' UNION ALL
SELECT '2204', '5', '2', '692619', '-1' UNION ALL
SELECT '1366', '5', '1', '692620', '-1' UNION ALL
SELECT '1461', '5', '2', '692620', '-1' UNION ALL
SELECT '1462', '5', '3', '692620', '-1' UNION ALL
SELECT '1463', '5', '4', '692620', '-1' UNION ALL
SELECT '1466', '5', '5', '692620', '-1' UNION ALL
SELECT '1467', '5', '6', '692620', '-1' UNION ALL
SELECT '1468', '5', '7', '692620', '-1' UNION ALL
SELECT '1469', '5', '8', '692620', '-1' UNION ALL
SELECT '1470', '5', '9', '692620', '-1' UNION ALL
SELECT '1471', '5', '10', '692620', '-1' UNION ALL
SELECT '1472', '5', '11', '692620', '-1' UNION ALL
SELECT '1609', '5', '12', '692620', '-1' UNION ALL
SELECT '1680', '5', '13', '692620', '-1' UNION ALL
SELECT '2006', '5', '14', '692620', '-1' UNION ALL
SELECT '2198', '5', '15', '692620', '-1' UNION ALL
SELECT '2229', '5', '16', '692620', '-1' UNION ALL
SELECT '2235', '5', '17', '692620', '-1' UNION ALL
SELECT '1180', '5', '1', '692624', '-1' UNION ALL
SELECT '1186', '5', '2', '692624', '-1' UNION ALL
SELECT '1288', '5', '3', '692624', '-1' UNION ALL
SELECT '1557', '5', '4', '692624', '-1' UNION ALL
SELECT '1562', '5', '5', '692624', '-1' UNION ALL
SELECT '1674', '5', '6', '692624', '-1' UNION ALL
SELECT '1708', '5', '7', '692624', '-1' UNION ALL
SELECT '1952', '5', '8', '692624', '-1' UNION ALL
SELECT '1953', '5', '9', '692624', '-1' UNION ALL
SELECT '1954', '5', '10', '692624', '-1' UNION ALL
SELECT '1963', '5', '11', '692624', '-1' UNION ALL
SELECT '1978', '5', '12', '692624', '-1' UNION ALL
SELECT '1979', '5', '13', '692624', '-1' UNION ALL
SELECT '1980', '5', '14', '692624', '-1' UNION ALL
SELECT '1981', '5', '15', '692624', '-1' UNION ALL
SELECT '1982', '5', '16', '692624', '-1' UNION ALL
SELECT '1983', '5', '17', '692624', '-1' UNION ALL
SELECT '2043', '5', '18', '692624', '-1' UNION ALL
SELECT '2044', '5', '19', '692624', '-1' UNION ALL
SELECT '2149', '5', '20', '692624', '-1' UNION ALL
SELECT '2166', '5', '21', '692624', '-1' UNION ALL
SELECT '2244', '5', '22', '692624', '-1' UNION ALL
SELECT '1296', '6', '1', '692626', '-1' UNION ALL
SELECT '1297', '6', '2', '692626', '-1' UNION ALL
SELECT '1298', '6', '3', '692626', '-1' UNION ALL
SELECT '1299', '6', '4', '692626', '-1' UNION ALL
SELECT '1300', '6', '5', '692626', '-1' UNION ALL
SELECT '1301', '6', '6', '692626', '-1' UNION ALL
SELECT '1308', '6', '7', '692626', '-1' UNION ALL
SELECT '1309', '6', '8', '692626', '-1' UNION ALL
SELECT '1310', '6', '9', '692626', '-1' UNION ALL
SELECT '1311', '6', '10', '692626', '-1' UNION ALL
SELECT '1312', '6', '11', '692626', '-1' UNION ALL
SELECT '1313', '6', '12', '692626', '-1' UNION ALL
SELECT '1314', '6', '13', '692626', '-1' UNION ALL
SELECT '1315', '6', '14', '692626', '-1' UNION ALL
SELECT '1318', '6', '15', '692626', '-1' UNION ALL
SELECT '1319', '6', '16', '692626', '-1' UNION ALL
SELECT '1320', '6', '17', '692626', '-1' UNION ALL
SELECT '1322', '6', '18', '692626', '-1' UNION ALL
SELECT '1323', '6', '19', '692626', '-1' UNION ALL
SELECT '1324', '6', '20', '692626', '-1' UNION ALL
SELECT '1325', '6', '21', '692626', '-1' UNION ALL
SELECT '1327', '6', '22', '692626', '-1' UNION ALL
SELECT '1328', '6', '23', '692626', '-1' UNION ALL
SELECT '1329', '6', '24', '692626', '-1' UNION ALL
SELECT '1330', '6', '25', '692626', '-1' UNION ALL
SELECT '1331', '6', '26', '692626', '-1' UNION ALL
SELECT '1332', '6', '27', '692626', '-1' UNION ALL
SELECT '1333', '6', '28', '692626', '-1' UNION ALL
SELECT '1334', '6', '29', '692626', '-1' UNION ALL
SELECT '1335', '6', '30', '692626', '-1' UNION ALL
SELECT '1336', '6', '31', '692626', '-1' UNION ALL
SELECT '1337', '6', '32', '692626', '-1' UNION ALL
SELECT '1338', '6', '33', '692626', '-1' UNION ALL
SELECT '1571', '6', '34', '692626', '-1' UNION ALL
SELECT '1601', '6', '35', '692626', '-1' UNION ALL
SELECT '1602', '6', '36', '692626', '-1' UNION ALL
SELECT '1632', '6', '37', '692626', '-1' UNION ALL
SELECT '1650', '6', '38', '692626', '-1' UNION ALL
SELECT '1662', '6', '39', '692626', '-1' UNION ALL
SELECT '1667', '6', '40', '692626', '-1' UNION ALL
SELECT '1669', '6', '41', '692626', '-1' UNION ALL
SELECT '1697', '6', '42', '692626', '-1' UNION ALL
SELECT '1781', '6', '43', '692626', '-1' UNION ALL
SELECT '1782', '6', '44', '692626', '-1' UNION ALL
SELECT '1783', '6', '45', '692626', '-1' UNION ALL
SELECT '1858', '6', '46', '692626', '-1' UNION ALL
SELECT '1879', '6', '47', '692626', '-1' UNION ALL
SELECT '1958', '6', '48', '692626', '-1' UNION ALL
SELECT '2003', '6', '49', '692626', '-1' UNION ALL
SELECT '2014', '6', '50', '692626', '-1' UNION ALL
SELECT '2160', '6', '51', '692626', '-1' UNION ALL
SELECT '2171', '6', '52', '692626', '-1' UNION ALL
SELECT '2173', '6', '53', '692626', '-1' UNION ALL
SELECT '2208', '6', '54', '692626', '-1' UNION ALL
SELECT '2246', '6', '55', '692626', '-1' UNION ALL
SELECT '1294', '6', '1', '692627', '-1' UNION ALL
SELECT '1295', '6', '2', '692627', '-1' UNION ALL
SELECT '1316', '6', '1', '692628', '-1' UNION ALL
SELECT '1317', '6', '2', '692628', '-1' UNION ALL
SELECT '1326', '6', '3', '692628', '-1' UNION ALL
SELECT '1862', '6', '4', '692628', '-1' UNION ALL
SELECT '1863', '6', '5', '692628', '-1' UNION ALL
SELECT '1864', '6', '6', '692628', '-1' UNION ALL
SELECT '1865', '6', '7', '692628', '-1' UNION ALL
SELECT '1866', '6', '8', '692628', '-1' UNION ALL
SELECT '1867', '6', '9', '692628', '-1' UNION ALL
SELECT '1868', '6', '10', '692628', '-1' UNION ALL
SELECT '1869', '6', '11', '692628', '-1' UNION ALL
SELECT '1302', '6', '1', '692629', '-1' UNION ALL
SELECT '1780', '6', '2', '692629', '-1' UNION ALL
SELECT '1789', '6', '3', '692629', '-1' UNION ALL
SELECT '1801', '6', '1', '692630', '-1' UNION ALL
SELECT '1803', '6', '2', '692630', '-1' UNION ALL
SELECT '1804', '6', '3', '692630', '-1' UNION ALL
SELECT '1805', '6', '4', '692630', '-1' UNION ALL
SELECT '1806', '6', '5', '692630', '-1' UNION ALL
SELECT '1807', '6', '6', '692630', '-1' UNION ALL
SELECT '1808', '6', '7', '692630', '-1' UNION ALL
SELECT '1321', '5', '1', '692632', '-1' UNION ALL
SELECT '1202', '5', '1', '692633', '-1' UNION ALL
SELECT '1208', '5', '2', '692633', '-1' UNION ALL
SELECT '1558', '5', '3', '692633', '-1' UNION ALL
SELECT '1563', '5', '4', '692633', '-1' UNION ALL
SELECT '1670', '5', '5', '692633', '-1' UNION ALL
SELECT '1709', '5', '6', '692633', '-1' UNION ALL
SELECT '2045', '5', '7', '692633', '-1' UNION ALL
SELECT '2046', '5', '8', '692633', '-1' UNION ALL
SELECT '2243', '5', '9', '692633', '-1' UNION ALL
SELECT '1572', '5', '1', '692634', '-1' UNION ALL
SELECT '1573', '5', '2', '692634', '-1' UNION ALL
SELECT '1574', '5', '3', '692634', '-1' UNION ALL
SELECT '1575', '5', '4', '692634', '-1' UNION ALL
SELECT '1576', '5', '5', '692634', '-1' UNION ALL
SELECT '1577', '5', '6', '692634', '-1' UNION ALL
SELECT '1578', '5', '7', '692634', '-1' UNION ALL
SELECT '1725', '5', '8', '692634', '-1' UNION ALL
SELECT '1973', '5', '9', '692634', '-1' UNION ALL
SELECT '1994', '5', '10', '692634', '-1' UNION ALL
SELECT '1224', '5', '1', '692635', '-1' UNION ALL
SELECT '1230', '5', '2', '692635', '-1' UNION ALL
SELECT '1246', '5', '3', '692635', '-1' UNION ALL
SELECT '1252', '5', '4', '692635', '-1' UNION ALL
SELECT '1268', '5', '5', '692635', '-1' UNION ALL
SELECT '1274', '5', '6', '692635', '-1' UNION ALL
SELECT '1559', '5', '7', '692635', '-1' UNION ALL
SELECT '1560', '5', '8', '692635', '-1' UNION ALL
SELECT '1561', '5', '9', '692635', '-1' UNION ALL
SELECT '1564', '5', '10', '692635', '-1' UNION ALL
SELECT '1565', '5', '11', '692635', '-1' UNION ALL
SELECT '1566', '5', '12', '692635', '-1' UNION ALL
SELECT '1671', '5', '13', '692635', '-1' UNION ALL
SELECT '1672', '5', '14', '692635', '-1' UNION ALL
SELECT '1673', '5', '15', '692635', '-1' UNION ALL
SELECT '1710', '5', '16', '692635', '-1' UNION ALL
SELECT '1711', '5', '17', '692635', '-1' UNION ALL
SELECT '1712', '5', '18', '692635', '-1' UNION ALL
SELECT '2047', '5', '19', '692635', '-1' UNION ALL
SELECT '2048', '5', '20', '692635', '-1' UNION ALL
SELECT '2049', '5', '21', '692635', '-1' UNION ALL
SELECT '2242', '5', '22', '692635', '-1' UNION ALL
SELECT '2245', '5', '23', '692635', '-1' UNION ALL
SELECT '1410', '6', '1', '692637', '-1' UNION ALL
SELECT '1412', '6', '2', '692637', '-1' UNION ALL
SELECT '1504', '6', '3', '692637', '-1' UNION ALL
SELECT '1506', '6', '4', '692637', '-1' UNION ALL
SELECT '1507', '6', '5', '692637', '-1' UNION ALL
SELECT '1508', '6', '6', '692637', '-1' UNION ALL
SELECT '1509', '6', '7', '692637', '-1' UNION ALL
SELECT '1880', '6', '8', '692637', '-1' UNION ALL
SELECT '1881', '6', '9', '692637', '-1' UNION ALL
SELECT '1882', '6', '10', '692637', '-1' UNION ALL
SELECT '1883', '6', '11', '692637', '-1' UNION ALL
SELECT '1884', '6', '12', '692637', '-1' UNION ALL
SELECT '1885', '6', '13', '692637', '-1' UNION ALL
SELECT '1886', '6', '14', '692637', '-1' UNION ALL
SELECT '1887', '6', '15', '692637', '-1' UNION ALL
SELECT '1888', '6', '16', '692637', '-1' UNION ALL
SELECT '1889', '6', '17', '692637', '-1' UNION ALL
SELECT '1890', '6', '18', '692637', '-1' UNION ALL
SELECT '1891', '6', '19', '692637', '-1' UNION ALL
SELECT '1892', '6', '20', '692637', '-1' UNION ALL
SELECT '1893', '6', '21', '692637', '-1' UNION ALL
SELECT '1934', '6', '22', '692637', '-1' UNION ALL
SELECT '1809', '6', '1', '692638', '-1' UNION ALL
SELECT '1810', '6', '2', '692638', '-1' UNION ALL
SELECT '1811', '6', '3', '692638', '-1' UNION ALL
SELECT '1812', '6', '4', '692638', '-1' UNION ALL
SELECT '1813', '6', '5', '692638', '-1' UNION ALL
SELECT '1814', '6', '6', '692638', '-1' UNION ALL
SELECT '1815', '6', '7', '692638', '-1' UNION ALL
SELECT '1816', '6', '8', '692638', '-1' UNION ALL
SELECT '1738', '6', '1', '692639', '-1' UNION ALL
SELECT '1739', '6', '2', '692639', '-1' UNION ALL
SELECT '1740', '6', '3', '692639', '-1' UNION ALL
SELECT '1741', '6', '4', '692639', '-1' UNION ALL
SELECT '1742', '6', '5', '692639', '-1' UNION ALL
SELECT '1743', '6', '6', '692639', '-1' UNION ALL
SELECT '1744', '6', '7', '692639', '-1' UNION ALL
SELECT '1745', '6', '8', '692639', '-1' UNION ALL
SELECT '1746', '6', '9', '692639', '-1' UNION ALL
SELECT '1747', '6', '10', '692639', '-1' UNION ALL
SELECT '1748', '6', '11', '692639', '-1' UNION ALL
SELECT '2018', '6', '12', '692639', '-1' UNION ALL
SELECT '2019', '6', '13', '692639', '-1' UNION ALL
SELECT '1387', '6', '1', '692640', '-1' UNION ALL
SELECT '1388', '6', '2', '692640', '-1' UNION ALL
SELECT '1389', '6', '3', '692640', '-1' UNION ALL
SELECT '1390', '6', '4', '692640', '-1' UNION ALL
SELECT '1391', '6', '5', '692640', '-1' UNION ALL
SELECT '1392', '6', '6', '692640', '-1' UNION ALL
SELECT '1393', '6', '7', '692640', '-1' UNION ALL
SELECT '1394', '6', '8', '692640', '-1' UNION ALL
SELECT '1395', '6', '9', '692640', '-1' UNION ALL
SELECT '1396', '6', '10', '692640', '-1' UNION ALL
SELECT '1397', '6', '11', '692640', '-1' UNION ALL
SELECT '1398', '6', '12', '692640', '-1' UNION ALL
SELECT '1399', '6', '13', '692640', '-1' UNION ALL
SELECT '1400', '6', '14', '692640', '-1' UNION ALL
SELECT '1401', '6', '15', '692640', '-1' UNION ALL
SELECT '1402', '6', '16', '692640', '-1' UNION ALL
SELECT '1403', '6', '17', '692640', '-1' UNION ALL
SELECT '1404', '6', '18', '692640', '-1' UNION ALL
SELECT '1405', '6', '19', '692640', '-1' UNION ALL
SELECT '1406', '6', '20', '692640', '-1' UNION ALL
SELECT '1407', '6', '21', '692640', '-1' UNION ALL
SELECT '1408', '6', '22', '692640', '-1' UNION ALL
SELECT '1409', '6', '23', '692640', '-1' UNION ALL
SELECT '1592', '6', '24', '692640', '-1' UNION ALL
SELECT '1593', '6', '25', '692640', '-1' UNION ALL
SELECT '1595', '6', '26', '692640', '-1' UNION ALL
SELECT '1596', '6', '27', '692640', '-1' UNION ALL
SELECT '1598', '6', '28', '692640', '-1' UNION ALL
SELECT '1607', '6', '29', '692640', '-1' UNION ALL
SELECT '1755', '6', '30', '692640', '-1' UNION ALL
SELECT '1756', '6', '31', '692640', '-1' UNION ALL
SELECT '1757', '6', '32', '692640', '-1' UNION ALL
SELECT '1790', '6', '33', '692640', '-1' UNION ALL
SELECT '1791', '6', '34', '692640', '-1' UNION ALL
SELECT '1792', '6', '35', '692640', '-1' UNION ALL
SELECT '1861', '6', '36', '692640', '-1' UNION ALL
SELECT '1444', '6', '1', '692641', '-1' UNION ALL
SELECT '1445', '6', '2', '692641', '-1' UNION ALL
SELECT '1446', '6', '3', '692641', '-1' UNION ALL
SELECT '1448', '6', '4', '692641', '-1' UNION ALL
SELECT '1449', '6', '5', '692641', '-1' UNION ALL
SELECT '1371', '6', '1', '692642', '-1' UNION ALL
SELECT '1372', '6', '2', '692642', '-1' UNION ALL
SELECT '1373', '6', '3', '692642', '-1' UNION ALL
SELECT '1374', '6', '4', '692642', '-1' UNION ALL
SELECT '1375', '6', '5', '692642', '-1' UNION ALL
SELECT '1376', '6', '6', '692642', '-1' UNION ALL
SELECT '1377', '6', '7', '692642', '-1' UNION ALL
SELECT '1378', '6', '8', '692642', '-1' UNION ALL
SELECT '1379', '6', '9', '692642', '-1' UNION ALL
SELECT '1380', '6', '10', '692642', '-1' UNION ALL
SELECT '1381', '6', '11', '692642', '-1' UNION ALL
SELECT '1382', '6', '12', '692642', '-1' UNION ALL
SELECT '1383', '6', '13', '692642', '-1' UNION ALL
SELECT '1384', '6', '14', '692642', '-1' UNION ALL
SELECT '1385', '6', '15', '692642', '-1' UNION ALL
SELECT '1629', '6', '16', '692642', '-1' UNION ALL
SELECT '1651', '6', '17', '692642', '-1' UNION ALL
SELECT '1751', '6', '18', '692642', '-1' UNION ALL
SELECT '1752', '6', '19', '692642', '-1' UNION ALL
SELECT '1854', '6', '20', '692642', '-1' UNION ALL
SELECT '1857', '6', '21', '692642', '-1' UNION ALL
SELECT '1965', '6', '22', '692642', '-1' UNION ALL
SELECT '1970', '6', '23', '692642', '-1' UNION ALL
SELECT '1415', '6', '1', '692643', '-1' UNION ALL
SELECT '1416', '6', '2', '692643', '-1' UNION ALL
SELECT '1417', '6', '3', '692643', '-1' UNION ALL
SELECT '1451', '6', '1', '692644', '-1' UNION ALL
SELECT '1452', '6', '2', '692644', '-1' UNION ALL
SELECT '1453', '6', '3', '692644', '-1' UNION ALL
SELECT '1454', '6', '4', '692644', '-1' UNION ALL
SELECT '1455', '6', '5', '692644', '-1' UNION ALL
SELECT '1456', '6', '6', '692644', '-1' UNION ALL
SELECT '1457', '6', '7', '692644', '-1' UNION ALL
SELECT '1458', '6', '8', '692644', '-1' UNION ALL
SELECT '1715', '6', '9', '692644', '-1' UNION ALL
SELECT '1758', '6', '10', '692644', '-1' UNION ALL
SELECT '1759', '6', '11', '692644', '-1' UNION ALL
SELECT '1760', '6', '12', '692644', '-1' UNION ALL
SELECT '1761', '6', '13', '692644', '-1' UNION ALL
SELECT '1762', '6', '14', '692644', '-1' UNION ALL
SELECT '1763', '6', '15', '692644', '-1' UNION ALL
SELECT '1764', '6', '16', '692644', '-1' UNION ALL
SELECT '1765', '6', '17', '692644', '-1' UNION ALL
SELECT '1766', '6', '18', '692644', '-1' UNION ALL
SELECT '1768', '6', '19', '692644', '-1' UNION ALL
SELECT '1769', '6', '20', '692644', '-1' UNION ALL
SELECT '1770', '6', '21', '692644', '-1' UNION ALL
SELECT '1771', '6', '22', '692644', '-1' UNION ALL
SELECT '1772', '6', '23', '692644', '-1' UNION ALL
SELECT '1773', '6', '24', '692644', '-1' UNION ALL
SELECT '1793', '6', '25', '692644', '-1' UNION ALL
SELECT '1794', '6', '26', '692644', '-1' UNION ALL
SELECT '1358', '5', '1', '692646', '-1' UNION ALL
SELECT '1359', '5', '2', '692646', '-1' UNION ALL
SELECT '1360', '5', '3', '692646', '-1' UNION ALL
SELECT '1361', '5', '4', '692646', '-1' UNION ALL
SELECT '1363', '5', '5', '692646', '-1' UNION ALL
SELECT '1364', '5', '6', '692646', '-1' UNION ALL
SELECT '1365', '5', '7', '692646', '-1' UNION ALL
SELECT '1367', '5', '8', '692646', '-1' UNION ALL
SELECT '1368', '5', '9', '692646', '-1' UNION ALL
SELECT '1538', '5', '10', '692646', '-1' UNION ALL
SELECT '2017', '5', '11', '692646', '-1' UNION ALL
SELECT '2133', '5', '1', '692647', '-1' UNION ALL
SELECT '2134', '5', '2', '692647', '-1' UNION ALL
SELECT '2135', '5', '3', '692647', '-1' UNION ALL
SELECT '2142', '5', '4', '692647', '-1' UNION ALL
SELECT '2143', '5', '5', '692647', '-1' UNION ALL
SELECT '2144', '5', '6', '692647', '-1' UNION ALL
SELECT '2147', '5', '7', '692647', '-1' UNION ALL
SELECT '2148', '5', '8', '692647', '-1' UNION ALL
SELECT '2236', '5', '9', '692647', '-1' UNION ALL
SELECT '2240', '5', '10', '692647', '-1' UNION ALL
SELECT '1441', '5', '1', '692648', '-1' UNION ALL
SELECT '1938', '5', '2', '692648', '-1' UNION ALL
SELECT '1939', '5', '3', '692648', '-1' UNION ALL
SELECT '1940', '5', '4', '692648', '-1' UNION ALL
SELECT '1941', '5', '5', '692648', '-1' UNION ALL
SELECT '1942', '5', '6', '692648', '-1' UNION ALL
SELECT '1943', '5', '7', '692648', '-1' UNION ALL
SELECT '1420', '5', '1', '692649', '-1' UNION ALL
SELECT '1421', '5', '2', '692649', '-1' UNION ALL
SELECT '1422', '5', '3', '692649', '-1' UNION ALL
SELECT '1423', '5', '4', '692649', '-1' UNION ALL
SELECT '1424', '5', '5', '692649', '-1' UNION ALL
SELECT '1426', '5', '6', '692649', '-1' UNION ALL
SELECT '1428', '5', '7', '692649', '-1' UNION ALL
SELECT '1429', '5', '8', '692649', '-1' UNION ALL
SELECT '1432', '5', '9', '692649', '-1' UNION ALL
SELECT '1433', '5', '10', '692649', '-1' UNION ALL
SELECT '1435', '5', '11', '692649', '-1' UNION ALL
SELECT '1442', '5', '12', '692649', '-1' UNION ALL
SELECT '1529', '5', '13', '692649', '-1' UNION ALL
SELECT '1530', '5', '14', '692649', '-1' UNION ALL
SELECT '1531', '5', '15', '692649', '-1' UNION ALL
SELECT '1604', '5', '16', '692649', '-1' UNION ALL
SELECT '1610', '5', '17', '692649', '-1' UNION ALL
SELECT '1612', '5', '18', '692649', '-1' UNION ALL
SELECT '1679', '5', '19', '692649', '-1' UNION ALL
SELECT '1838', '5', '20', '692649', '-1' UNION ALL
SELECT '1964', '5', '21', '692649', '-1' UNION ALL
SELECT '1968', '5', '22', '692649', '-1' UNION ALL
SELECT '2080', '5', '23', '692649', '-1' UNION ALL
SELECT '1956', '5', '1', '692650', '-1' UNION ALL
SELECT '1139', '5', '16', '692653', '-1' UNION ALL
SELECT '2193', '5', '92', '692653', '-1' UNION ALL
SELECT '2241', '5', '346', '692654', '-1' UNION ALL
SELECT '2233', '5', '1', '692657', '-1' UNION ALL
SELECT '1872', '5', '1', '692658', '-1' UNION ALL
SELECT '2154', '5', '1', '692659', '-1' UNION ALL
SELECT '2145', '5', '1', '692667', '-1' UNION ALL
SELECT '2184', '5', '1', '692668', '-1' UNION ALL
SELECT '1151', '5', '1', '692677', '-1' UNION ALL
SELECT '1152', '5', '2', '692677', '-1' UNION ALL
SELECT '1153', '5', '3', '692677', '-1' UNION ALL
SELECT '1154', '5', '4', '692677', '-1' UNION ALL
SELECT '1156', '5', '5', '692677', '-1' UNION ALL
SELECT '1714', '5', '6', '692677', '-1' UNION ALL
SELECT '1930', '5', '7', '692677', '-1' UNION ALL
SELECT '2009', '5', '8', '692677', '-1' UNION ALL
SELECT '2083', '5', '9', '692677', '-1' UNION ALL
SELECT '2084', '5', '10', '692677', '-1' UNION ALL
SELECT '2085', '5', '11', '692677', '-1' UNION ALL
SELECT '2086', '5', '12', '692677', '-1' UNION ALL
SELECT '2087', '5', '13', '692677', '-1' UNION ALL
SELECT '2088', '5', '14', '692677', '-1' UNION ALL
SELECT '2167', '5', '15', '692677', '-1' UNION ALL
SELECT '2168', '5', '16', '692677', '-1' UNION ALL
SELECT '2169', '5', '17', '692677', '-1' UNION ALL
SELECT '2227', '5', '18', '692677', '-1' UNION ALL
SELECT '1653', '5', '1', '692678', '-1' UNION ALL
SELECT '2023', '5', '1', '692679', '-1' UNION ALL
SELECT '1496', '5', '1', '692680', '-1' UNION ALL
SELECT '1503', '5', '2', '692680', '-1' UNION ALL
SELECT '1510', '5', '3', '692680', '-1' UNION ALL
SELECT '1438', '5', '1', '692681', '-1' UNION ALL
SELECT '1439', '5', '2', '692681', '-1' UNION ALL
SELECT '1440', '5', '3', '692681', '-1' UNION ALL
SELECT '1473', '5', '4', '692681', '-1' UNION ALL
SELECT '1475', '5', '5', '692681', '-1' UNION ALL
SELECT '2007', '5', '6', '692681', '-1' UNION ALL
SELECT '2172', '5', '7', '692681', '-1' UNION ALL
SELECT '2192', '5', '8', '692681', '-1' UNION ALL
SELECT '2020', '5', '1', '692682', '-1' UNION ALL
SELECT '2022', '5', '2', '692682', '-1' UNION ALL
SELECT '1059', '5', '1', '692685', '-1' UNION ALL
SELECT '1060', '5', '2', '692685', '-1' UNION ALL
SELECT '1061', '5', '3', '692685', '-1' UNION ALL
SELECT '1062', '5', '4', '692685', '-1' UNION ALL
SELECT '1063', '5', '5', '692685', '-1' UNION ALL
SELECT '1064', '5', '6', '692685', '-1' UNION ALL
SELECT '1065', '5', '7', '692685', '-1' UNION ALL
SELECT '1066', '5', '8', '692685', '-1' UNION ALL
SELECT '1067', '5', '9', '692685', '-1' UNION ALL
SELECT '1694', '5', '10', '692685', '-1' UNION ALL
SELECT '1069', '5', '1', '692686', '-1' UNION ALL
SELECT '1070', '5', '2', '692686', '-1' UNION ALL
SELECT '1827', '5', '3', '692686', '-1' UNION ALL
SELECT '1829', '5', '4', '692686', '-1' UNION ALL
SELECT '1999', '5', '5', '692686', '-1' UNION ALL
SELECT '2027', '5', '6', '692686', '-1' UNION ALL
SELECT '2054', '5', '7', '692686', '-1' UNION ALL
SELECT '2156', '5', '8', '692686', '-1' UNION ALL
SELECT '2070', '5', '1', '692687', '-1' UNION ALL
SELECT '2180', '5', '1', '692688', '-1' UNION ALL
SELECT '2186', '5', '2', '692688', '-1' UNION ALL
SELECT '2189', '5', '3', '692688', '-1' UNION ALL
SELECT '1055', '5', '1', '692689', '-1' UNION ALL
SELECT '2185', '5', '2', '692689', '-1' UNION ALL
SELECT '1721', '5', '1', '692692', '-1' UNION ALL
SELECT '2050', '3', '422', '692697', '-1' UNION ALL
SELECT '2063', '3', '426', '692697', '-1' UNION ALL
SELECT '2238', '3', '515', '692697', '-1'
You can see that the maximum level is 6, and this can be static ... I can just tell them they can only have 6 levels which is fine. So of course the level 6 can easily be converted into my dimensions through a query similar to this:
/* Level 6 leaf accounts roll straight up the six levels */
SELECT
t1.AcctID, t1.iOrder,
t2.AcctID, t2.iOrder,
t3.AcctID, t3.iOrder,
t4.AcctID, t4.iOrder,
t5.AcctID, t5.iOrder,
t6.AcctID, t6.iOrder,
t7.AcctID, t7.iOrder
FROM
tmpAcctTree t1
INNER JOIN tmpAcctTree t2 ON t1.ParentID = t2.AcctID
INNER JOIN tmpAcctTree t3 ON t2.ParentID = t3.AcctID
INNER JOIN tmpAcctTree t4 ON t3.ParentID = t4.AcctID
INNER JOIN tmpAcctTree t5 ON t4.ParentID = t5.AcctID
INNER JOIN tmpAcctTree t6 ON t5.ParentID = t6.AcctID
INNER JOIN tmpAcctTree t7 ON t6.ParentID = t7.AcctID
WHERE
t1.IsLeaf = -1
AND t1.iLevel = 6
The problem arises with leaves at less than the maximum level. They need to be expanded somewhere between the second and the fifth level with a group that rolls into itself ... but of course that group can not exist elsewhere in the dimension at a different level ... or can it?
September 17, 2013 at 1:25 pm
busraker (9/17/2013)
...but of course according to multiple sources I should not use parent/child hierarchies,...
Heh... you need to ignore those "multiple sources" because they're condeming one of the easier methods to maintain hierarchical data there is. Please see the following articles for why I say that. The articles also explain how to "flatten" or "traverse" such hierarchies using very high performance conversions to "Nested Sets" and other hierarchical structures while maintaining the ease of maintanence offered by the typical parent/child (Adjacency List) hierarchical structure.
http://www.sqlservercentral.com/articles/Hierarchy/94040/
http://www.sqlservercentral.com/articles/T-SQL/94570/
--Jeff Moden
Change is inevitable... Change for the better is not.
September 17, 2013 at 1:58 pm
Jeff Moden (9/17/2013)[/b
Heh... you need to ignore those "multiple sources" because they're condeming one of the easier methods to maintain hierarchical data there is.
Well, one of those sources (and the most important one) is the client so I should at least have it in myself to present both possible solutions in the planning phase. They rightfully would like to be able (or at least test the ability) to drill down to a certain level up from the detail or down from the summary without having to expand/collapse each individual node.
September 17, 2013 at 2:13 pm
This ugly bit of code gets me frighteningly close ...
SELECT
t1.AcctID, t1.iOrder, t1.ilevel,
/* The second level is always the parent folder of the detail */
t2.AcctID Level2ID, t2.iOrder Level2Order,
CASE WHEN t1.iLevel < 6 THEN t2.AcctID ELSE t3.AcctID END Level3ID,
CASE WHEN t1.iLevel < 6 THEN t2.iOrder ELSE t3.iOrder END Level3Order,
CASE t1.iLevel WHEN 6 THEN t4.AcctID WHEN 5 THEN t3.AcctID ELSE t2.AcctID END Level4ID,
CASE t1.iLevel WHEN 6 THEN t4.iOrder WHEN 5 THEN t3.iOrder ELSE t2.iOrder END Level4Order,
CASE t1.iLevel WHEN 6 THEN t5.AcctID WHEN 5 THEN t4.AcctID WHEN 4 THEN t3.AcctID ELSE t2.AcctID END Level5ID,
CASE t1.iLevel WHEN 6 THEN t5.iOrder WHEN 5 THEN t4.iOrder WHEN 4 THEN t3.iOrder ELSE t2.iOrder END Level5Order,
CASE t1.iLevel WHEN 6 THEN t6.AcctID WHEN 5 THEN t5.AcctID WHEN 4 THEN t4.AcctID WHEN 3 THEN t3.AcctID ELSE t2.AcctID END Level6ID,
CASE t1.iLevel WHEN 6 THEN t6.iOrder WHEN 5 THEN t5.iOrder WHEN 4 THEN t4.iOrder WHEN 3 THEN t3.iOrder ELSE t2.iOrder END Level6Order,
COALESCE(t7.AcctID, t6.AcctID, t5.AcctID, t4.AcctID, t3.AcctID, t2.AcctID) Level7ID,
COALESCE(t7.iOrder, t6.iOrder, t5.iOrder, t4.iOrder, t3.iOrder, t2.iOrder) Level7Order
FROM
tmpAcctTree t1
INNER JOIN tmpAcctTree t2 ON t1.ParentID = t2.AcctID
LEFT JOIN tmpAcctTree t3 ON t2.ParentID = t3.AcctID
LEFT JOIN tmpAcctTree t4 ON t3.ParentID = t4.AcctID
LEFT JOIN tmpAcctTree t5 ON t4.ParentID = t5.AcctID
LEFT JOIN tmpAcctTree t6 ON t5.ParentID = t6.AcctID
LEFT JOIN tmpAcctTree t7 ON t6.ParentID = t7.AcctID
WHERE
t1.IsLeaf = -1
September 17, 2013 at 3:55 pm
busraker (9/17/2013)
Jeff Moden (9/17/2013)[/b
Heh... you need to ignore those "multiple sources" because they're condeming one of the easier methods to maintain hierarchical data there is.
Well, one of those sources (and the most important one) is the client so I should at least have it in myself to present both possible solutions in the planning phase. They rightfully would like to be able (or at least test the ability) to drill down to a certain level up from the detail or down from the summary without having to expand/collapse each individual node.
That's precisely what the "Nested Sets" hierarchy in the first article and the "alternate" in the second article would do for you. If you take the time to read the articles, you'll also find that each node is actually "flattened" in the Hierarchical path column.
You can help the client out by educating them. Remember, the client isn't always right. We sometimes have to help protect them from themselves. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply