Parameter-Driven Parameters

[mcowie]

The Power of ReportWORQ Parameters

Parameters are a big part of the magic of ReportWORQ. Parameter values are used to make data retrieval in reports much more dynamic, and they can also be used to shape the output created by ReportWORQ.

By linking a parameter to a specific cell in an Excel report, you can not only generate multiple worksheets, PowerPoint slides, or pages in a PDF-- you can also drive recalculation behavior and filtering. Parameters can also be used to drive output file names, paths, content in emails, and so much more!

Individual Parameters can be based on static values and lists, of course, and Parameters can even serve as placeholders for more complex integrations, such as through the use of Burst Sets or overrides through the ReportWORQ REST API.

Where Parameters really shine is in their ability to be dynamically populated by linking them to other data sources that ReportWORQ supports, such as SQL queries, IBM Planning Analytics, Workday Adaptive, and more.

MDX Repeater Parameters: Parameter-Driven Parameters

One thing ReportWORQ Parameters can’t generally do is reference other ReportWORQ Parameters. For example, an IBM Planning Analytics (PA) / TM1 Subset parameter can’t derive its selected subset name from another ReportWORQ Parameter. There is one exception to this: our support for IBM PA/TM1 MDX Repeater Parameters. This post will go through some examples around MDX Repeaters for those who have IBM PA/TM1 and would like to learn a bit more about this capability.

Prerequisites

Using MDX Repeater Parameters requires that you have:

1. A valid IBM PA / TM1 data source connection.
2. A Parameter consisting of one or more values that will be referenced by your MDX Repeater parameter. This can be any type of ReportWORQ parameter.
3. A little knowledge of IBM PA/TM1’s support for MDX queries. There are a number of resources online, like this article, to help you get started.

When you create an MDX Repeater Parameter you will need to first reference the dependent Parameter that you created in ReportWORQ. In this example we have already created a BU (Business Unit) parameter which will be used at Job runtime to drive the Parameter values returned by our MDX Repeater Parameter:

You then will construct your MDX query in your MDX Repeater definition, referencing and substituting the value in your dependent parameter in the MDX expression using %ELEMENT%. For each value in your dependent Parameter (BU in this example) ReportWORQ will substitute that value in the %ELEMENT% placeholder and “calculate” the MDX query, returning a list of element (or alias names, if Alias is specified) for this MDX Repeater Parameter. Some examples of common MDX Repeater use cases follow.

Return a Specific Named Subset

Let’s say are generating a report packet for each BU, driven by a BU Parameter list, and you want to generate a worksheet for each Department. However, you want to only output Departments that are relevant for each BU. If you’ve created named, public subsets in your Department dimension that have the name of the BU you can reference your BU Parameter in the following way using an MDX Repeater Parameter for Departments:

TM1SUBSETTOSET([plan_department], '%ELEMENT%', 'public')

Return a List of Employees based on Manager Attribute

Let’s say you are generating a report packet for each manager, driven by a Manager Parameter list, and you want to generate a worksheet for each of the Manager’s employees. If you have an attribute defined for each manager, you can reference your Manager Parameter in the following way using an MDX Repeater Parameter for Employees:

FILTER( TM1SUBSETALL( [Employee] )}, [Employee].[Manager] = "%ELEMENT%")

Return all Leaf Elements Below a Rollup

Another common requirement is to dynamically generate report output that is based on the detail underlying a consolidation. For example, if you have a Parameter for Product Categories and want to create a Report that includes worksheets for each Product Category’s Products you could reference your Product Categories Parameter in the following way using an MDX Repeater Parameter for Products:

TM1SORT( TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER( {[Product].[%ELEMENT%]}, ALL, RECURSIVE ), 0), ASC)

Return all Products with non-Zero Sales

A twist on the previous example is to incorporate a cube query to further filter Products that have non-zero sales in a Product Sales cube for a specific Year:

FILTER( TM1SORT( TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER( {[Product].[%ELEMENT%]}, ALL, RECURSIVE ), 0), ASC) , [Product Sales].([Product].CurrentMember, [Periods].[2024], [Version].[Actual], [Measures].[Sales]) > 0)

ReportWORQ Parameters are already powerful, and hopefully these examples helped illustrate a little more of the advanced power behind MDX Repeater Parameters for customers who use IBM PA/TM1!

Got any favorite MDX Repeater use cases? Feel free to share in comments!

[LlewellynE]

This was incredibly helpful — thank you for putting this together. I was able to leverage this approach for my use case of passing multiple elements through a single ReportWORQ parameter using the Planning Analytics MDX Repeater type.

However, I did run into a limitation that I think is worth flagging. When using the MDX Repeater parameter type, the only available Parameter Option is “Group & collate pages that use this parameter.” In contrast, the Text List parameter type offers additional options — most notably “One report per item” which is critical for certain workflows.

My specific use case involves running a single Job across multiple Organizations, where the list of Organizations is dynamically maintained by users through a cube. The MDX Repeater approach works well for some of my users, but the absence of the “One report per item” option means we still can’t generate separate reports per element. As a result, we’re forced to fall back on static Text List parameters and build out individual jobs manually — which defeats much of the automation benefit.

It would be a great enhancement if the MDX Repeater parameter type could support the same output options available under Text List, particularly “One report per item.” That addition would make this feature significantly more powerful for enterprise-scale deployments.

Has anyone else encountered this limitation or found a workaround?

[aweiss]

@LlewellynE,

I have some good news for you. ReportWORQ supports referencing other parameters when using the SQL and MDX Parameter. For example, if you have a Parameter called State with the value “New Jersey”, you could write a SQL query like this:

SELECT Location FROM REGIONS WHERE State = %param:State%
With output: Middletown, Princeton, Hoboken

Then when you change the State parameter, the SQL query will adjust. This is particularly helpful when using Burst Sets. Rather than have a custom SQL query for each burst set, you can have a single SQL Parameter and a different State parameter for each Burst Set. Which is much easier to implement and maintain, and allows the state list to be sourced dynamically.

If the referenced parameter has multiple values then the SQL Query will execute multiple times and join the results. For example, if State was New Jersey and New York then the results would be: Middletown, Princeton, Hoboken, Manhattan, Brooklyn, Queens

Using this parameter type you can now set the SQL Parameter or MDX Parameter to One Report per Item and you should be able to achieve the results that you’re looking for.

Please wait until the next release on April 6th to test this functionality since we had to make a modification to allow MDX parameters to reference other MDX parameters.

Here’s an example of the configuration that I tested with.

Thank You,
-Andy

[LlewellynE]

I will start counting down the days until 4/6/2026. Thanks for looking into this and providing a workaround!