Данный пример отражает структуру вызова MDX к Essbase из T-SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
DECLARE @Periods TABLE (MonthName nvarchar(80) ); INSERT INTO @Periods SELECT 'P07' as MonthName; INSERT INTO @Periods SELECT 'P08' as MonthName; INSERT INTO @Periods SELECT 'P09' as MonthName; INSERT INTO @Periods SELECT 'P10' as MonthName; INSERT INTO @Periods SELECT 'P11' as MonthName; INSERT INTO @Periods SELECT 'P12' as MonthName; DECLARE @Drivers TABLE (DriverName nvarchar(80) ); INSERT INTO @Drivers SELECT 'acc_drivers0007' as DriverName; INSERT INTO @Drivers SELECT 'acc_drivers0008' as DriverName; INSERT INTO @Drivers SELECT 'acc_drivers0009' as DriverName; select MonthName, DriverName, Dim29 as BU, Dim30 as Driver, DblValue as DriverValue from @Periods cross apply @Drivers cross apply [dbo].[BuildXmlaDataTable] ( 'mdxLev0' , ' SELECT NON EMPTY ({[' + MonthName + ']}) ON COLUMNS, NON EMPTY NONEMPTYBLOCK CrossJoin({ Descendants([iBU_TBU90200009],1)} ,{ [' + DriverName + '] } ) ON ROWS FROM D08FUNC.D08FUNC where ( [FY16], [BU_NA], [C00_LvlBefore], [C03_NA], [C04_NA], [C05_NA], [C06_NA], [C07_NA], [C09_NA], [C11_NA], [C12_NA], [C01_NA], [C02_NA], [SCN_BUD], [VER_WRKYR], [C10_ALT90400072] ) ' ) Where DblValue>0; |
Результат:
