IBM PA MDX & Reportworq
Quote from Karen Hewitt on May 13, 2026, 11:36 amHello!
Have you ever built an IBM Planning Analytics report using an MDX query, refreshed it successfully in Excel with the IBM PA add-in, and then had it fail when running the same report through a job in Reportworq?
That situation can be really confusing. The first thought is often that something is wrong with Reportworq and that a support ticket needs to be opened.
I’ve run into this many times over the years, so I wanted to share a few things I’ve learned when using MDX statements for IBM Planning Analytics report generation.
If you use the IBM PA add-in to generate MDX automatically, keep in mind that it records your filtering steps, but it does not always produce the most efficient or cleanest MDX. Because of that, it is always worth reviewing the generated MDX carefully.
One key point is that Reportworq is often more strict about MDX syntax than the IBM PA add-in. So if a report refreshes correctly in Excel but fails in Reportworq, there is a good chance the issue is with the MDX query itself.
A common sign is that the cell containing
TM1RPTViewdisplays#NAME?after the Reportworq job runs. In many cases, the cause is something small, such as a missing). The IBM PA add-in may still tolerate that, but Reportworq will not. I’ll admit this was frustrating for me at first, but it also pushed me to become much better at writing and reviewing MDX.If you need to feed your MDX query with a parameter from Reportworq bursting, and that value must be surrounded by quotes such as
"Long", getting the Excel formula syntax right can be tricky the first time. Once you learn it, though, it becomes much easier.For example, suppose your MDX query looks like this:
{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Products] )} , 0)}, [Products].[Shape] = "Long")}
Now suppose you want to dynamically supply the value for the
Shapeattribute from a worksheet cell or named range. You could modify the formula in Excel like this:="{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Products] )} , 0)}, [Products].[Shape] = "&""""&$A$1&""""&")}"
or:
="{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Products] )} , 0)}, [Products].[Shape] = """&$A$1&""")}"
The difference between these two options is mostly personal preference in how you organize the quotes inside the formula. Either approach works. After entering the formula, you can confirm that the output includes quotation marks at the beginning and end of the attribute value. Both formulas produce the MDX you want, but now the filter value can be supplied dynamically from a cell, which makes it much easier to use in Reportworq job iterations.
If you have a very long MDX statement with multiple filters or
ORDERclauses and it exceeds Excel’s formula length limit, you can split the logic across several cells and then combine them in a master cell. That master cell can then be used in theTM1RPTROWformula.For example, your master formula might look like:
=$A$1&$A$2&$A$3
Just try to break the MDX into segments that are easy to follow. The longer the expression gets, and the more pieces you split it into, the easier it is to accidentally miss a closing
)or}.You can also use conditional Excel formulas to switch between different MDX statements. For example, if one segment requires a slightly different MDX query than the others, you can use an
IFstatement in the cell referenced byTM1RPTROW, such as:=IF(Dept="Shoes", $A$1, $B$1)
In this example,
Deptis a named range.I hope these tips are helpful. I’m also including a few useful MDX resources below, along with a simple tool that can help spot missing brackets or parentheses in your query.
Helpful MDX resources:
https://www.bihints.com/book/export/html/68
https://www.bihints.com/index.php/search/node?keys=mdx+filter
To help spot missing
)or}in an MDX query:Let me know if you have any questions. If it would be helpful, I can also share an Excel workbook with examples.
Hello!
Have you ever built an IBM Planning Analytics report using an MDX query, refreshed it successfully in Excel with the IBM PA add-in, and then had it fail when running the same report through a job in Reportworq?
That situation can be really confusing. The first thought is often that something is wrong with Reportworq and that a support ticket needs to be opened.
I’ve run into this many times over the years, so I wanted to share a few things I’ve learned when using MDX statements for IBM Planning Analytics report generation.
If you use the IBM PA add-in to generate MDX automatically, keep in mind that it records your filtering steps, but it does not always produce the most efficient or cleanest MDX. Because of that, it is always worth reviewing the generated MDX carefully.
One key point is that Reportworq is often more strict about MDX syntax than the IBM PA add-in. So if a report refreshes correctly in Excel but fails in Reportworq, there is a good chance the issue is with the MDX query itself.
A common sign is that the cell containing TM1RPTView displays #NAME? after the Reportworq job runs. In many cases, the cause is something small, such as a missing ) . The IBM PA add-in may still tolerate that, but Reportworq will not. I’ll admit this was frustrating for me at first, but it also pushed me to become much better at writing and reviewing MDX.
If you need to feed your MDX query with a parameter from Reportworq bursting, and that value must be surrounded by quotes such as "Long", getting the Excel formula syntax right can be tricky the first time. Once you learn it, though, it becomes much easier.
For example, suppose your MDX query looks like this:
{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Products] )} , 0)}, [Products].[Shape] = "Long")}
Now suppose you want to dynamically supply the value for the Shape attribute from a worksheet cell or named range. You could modify the formula in Excel like this:
="{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Products] )} , 0)}, [Products].[Shape] = "&""""&$A$1&""""&")}"
or:
="{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Products] )} , 0)}, [Products].[Shape] = """&$A$1&""")}"
The difference between these two options is mostly personal preference in how you organize the quotes inside the formula. Either approach works. After entering the formula, you can confirm that the output includes quotation marks at the beginning and end of the attribute value. Both formulas produce the MDX you want, but now the filter value can be supplied dynamically from a cell, which makes it much easier to use in Reportworq job iterations.
If you have a very long MDX statement with multiple filters or ORDER clauses and it exceeds Excel’s formula length limit, you can split the logic across several cells and then combine them in a master cell. That master cell can then be used in the TM1RPTROW formula.
For example, your master formula might look like:
=$A$1&$A$2&$A$3
Just try to break the MDX into segments that are easy to follow. The longer the expression gets, and the more pieces you split it into, the easier it is to accidentally miss a closing ) or }.
You can also use conditional Excel formulas to switch between different MDX statements. For example, if one segment requires a slightly different MDX query than the others, you can use an IF statement in the cell referenced by TM1RPTROW, such as:
=IF(Dept="Shoes", $A$1, $B$1)
In this example, Dept is a named range.
I hope these tips are helpful. I’m also including a few useful MDX resources below, along with a simple tool that can help spot missing brackets or parentheses in your query.
Helpful MDX resources:
https://www.bihints.com/book/export/html/68
https://www.bihints.com/index.php/search/node?keys=mdx+filter
To help spot missing ) or } in an MDX query:
Let me know if you have any questions. If it would be helpful, I can also share an Excel workbook with examples.