We have some consolidations that only exist in an alternate hierarchy. As there is apparently no way on God's green Earth I have found to assign a subset created with HierarchySubsetCreate to a view , I tried using ViewCreateByMDX including those elements and read it in the DATA procedure. The MDX works in PAX just fine, but the TI returns a message it is unable to create the view. The alternate hierarchy is on the [Organization] dimension.
Code: Select all
SELECT
non empty {
[stage.Employee Actuals Measure].[stage.Employee Actuals Measure].[Actual Hours],
[stage.Employee Actuals Measure].[stage.Employee Actuals Measure].[Total Labor Cost],
[stage.Employee Actuals Measure].[stage.Employee Actuals Measure].[Allowable Labor Cost]
}
ON COLUMNS,
non empty
TM1SubsetToSet([Organization].[ORGPG.001], "Planning Components") *
{[Fiscal Period].[Fiscal Period].[2019].children} *
{[HR Level].[HR Level].[All Staff HR Levels].children}
ON ROWS
FROM [stage.Employee Actuals]
WHERE ([sys.Cube.Adjustments].[sys.Cube.Adjustments].[Effective],
[Accounts Master].[Accounts Master].[All Accounts],
[Employee].[Employee].[All Employees],
[GLC].[GLC].[All GLCs],
[Sub Period].[Sub Period].[All Sub Periods])
Code: Select all
SELECT non empty {[bpc.Plan Summary Measure].[bpc.Plan Summary Measure].[Effective]} on 0 ,
non empty {[Organization].[ORGPG.001].[Core CON]} *
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Accounts Master] )}, 0)} *
{{[Fiscal Period].[2020].Children} + {[Fiscal Period].[Plan Out Year]}} on 1
FROM [bpc.Plan Summary] WHERE ([Version].[Active_01])