Use Cell Reference in Active Form Column Filter

[slu]

All,

I created a MDX expression using TM1 planning sample database, plan_BudgetPlan cube as an example. I basically wanted to pull all 0 level departments with FY2004 Budget, Wages expense (acct 61060) Jan-2004, local currency, budget source greater than 300000 (MDX dynamic subset filter by a value). Below expession works on TM1RPTROW function. Lo is a dynamic named subset I created using another MDX expression.

The Expression is :{FILTER ({ [plan_department].[L0]}, [plan_BudgetPlan]. ( [plan_version].[FY 2004 Budget],[plan_business_unit].[10000],[plan_chart_of_accounts].[61060],[plan_exchange_rates].[local],[plan_source].[budget],[plan_time].[Jan-2004])>300000 )}

If I like to replace ">300000" using a cell paramater, so user can input >300000 in C19 in excel, below MDX does not work
{FILTER ({ [plan_department].[L0]}, [plan_BudgetPlan]. ( [plan_version].[FY 2004 Budget],[plan_business_unit].[10000],[plan_chart_of_accounts].[61060],[plan_exchange_rates].[local],[plan_source].[budget],[plan_time].[Jan-2004])"&$C$19&")}

Could you help out with the correct syntax how to write "&$C$19&" this portion. I did try single, double, &, or 3 double quotation """ around the cell reference, it is not working.

Thank you very much

[gtonkin]

It does not look like you have entered the MDX as a formula in Excel try:

Code: Select all

="{FILTER ({ [plan_department].[L0]}, [plan_BudgetPlan]. ( [plan_version].[FY 2004 Budget],[plan_business_unit].[10000],[plan_chart_of_accounts].[61060],[plan_exchange_rates].[local],[plan_source].[budget],[plan_time].[Jan-2004])"&$C$19&")}"

[slu]

Works, thank you for your time.

[gtonkin]

Pleasure-good luck with the rest!

[slu]

I saw your posting about how to use cell parameters in the title dimension. Now I can dynamically change both the title and column inputs. Next step I will try to UNION two or three column filters and test whether the expression works. I personally like TM1 worksheet with drop down list vs. SUBNM, but a client asked whether I can do advanced active form for them. I know this method can not stack dimensions on the row. It would be great if someone can provide some hints using other methods which can apply on stack dimension case. I personally did one active form by stacking GL account dim and Department dim, I created alternative hierarchy (changed metadata structure) in one of the two dimensions, created several active form sections to make the whole active form meeting requirements.