MakeQuery3
Universal Report static layout has a formula called MakeQuery3.
MakeQuery3 builds the MDX query from the report’s content. This formula takes in a cube name as its first argument and the row, column, slicers, and calculation named ranges.
Syntax
In the following syntax, 0 represents the report id on the sheet:
=@MakeQuery3("plan_BudgetPlan",tm2\\_0_rh,tm2\\_0_rm,tm2\\_0_ch,tm2\\_0_cm,tm2\\_0_sh,tm2\\_0_slicers,tm2\\_0_calcs)The MakeQuery3 output is an MDX query representing the report that starts with /*
STATICLAYOUT */.
Note: If you wanted to manually add an MDX query to the report instead of using MakeQuery3,
/* STATICLAYOUT */. must be at the front of the query in order for IBM® Planning
Analytics for Microsoft Excel and IBM Planning
Analytics TM1® Web to recognize the query as a part of a
Universal Report static layout.| Argument | Description |
|---|---|
| Cube | TM1 cube name. |
| tm2\\_0_rh | Cell range of the row axis hierarchies. The order is important and should match the hierarchy order of the row axis hierarchies desired (ex: {“[plan_chart_of_accounts].[ plan_chart_of_accounts]”, “[plan_business_unit].[plan_business_unit”}). |
| tm2\\_0_rm | Cell range of the row axis members (ex: {“Revenue”, “UK”; “Revenue”, “Canada”; …}. |
| tm2\\_0_ch | Cell range of the column axis hierarchies. The order is important and should match the hierarchy order of the row axis hierarchies desired. |
| tm2\\_0_cm | Cell range of column axis members (ex: {“Jan-2004”, “Feb-2004”, “Mar-2004”; “FY 2004 Budget”, “FY 2004 Budget”, “FY 2004 Budget”}). |
| tm2\\_0_sh | Cell range of the slicer hierarchies. The order is important and should match the hierarchy order of the row axis hierarchies desired (“ex: {“[plan_source].[plan_source]”, “[plan_department].[plan_department]”…}). |
| tm2\\_0_slicers | Cell range of the slicers members (“ex: {“Goal”, “Total Organization”, …}). |
| tm2\\_0_calcs | Cell range of the calculated members. |