I have 2 cubes, one that holds staff budgets and one that holds staff actuals (they are in different cubes because of differing dimensionality for one thing)
I have created an active form based on the actuals cube. Employees are in the rows. In the columns are the period actuals for pay and a column for their total pay budget.
I want to give the user the option on the web to return employees with just actuals or employees with a budget for a given cost centre. Obviously the just actuals view will exclude any employees who don’t have an actual! Hence giving users both options.
The view to return employees with just actuals is a simple mdx filter by level 0 (referenced in the TM1RPTROW) and suppress zero rows. This works fine.
To return those employees with a budget I have switched off zero suppression and used mdx in the TM1RPTROW as follows:
Code: Select all
{Filter({TM1FilterbyLevel( {TM1SubsetAll( [F_Employee] )}, 0 )},
[StaffingBudget].(
[F_Employee].CurrentMember, [Version], [Year], [Analysis], [CostCentre], [JobNumber], [TotalPay] ) > 0)}
The issue is that this mdx takes around 20 seconds to return data on the web.
I did add a view formula to the websheet, as follows:
Code: Select all
VIEW("TM1Model:StaffingBudget","!",$D$13,$D$17,$D$11,$D$16,$D$14,$D$18)
Can anyone think of a way to make this more efficient?
Regards, Mark