Currently we have a set of reports which we want to generate using 3 dimensions within TM1/Planning Analytics. These dimensions are namely “entity”, “period” and “currency”. With these dimensions we want a report to be generated for each unique combination of these dimensions. Currently we are able to produce all these reports successfully buy looping through the n level subsets.
The problem comes in that because we store a large number of currencies within the TM1 model, we have a huge number of reports being generated with empty values as most of the currencies are not applicable to the entity being run. Is there anyway of somehow skipping the blank reports? Currently within the model we have a lookup cube which is use by TI’s to determine which currencies are applicable to which entities. With a Boolean flag next to the intersections of entity and currency that they are applicable to.
Unfortunately, not all entities have the same number of currencies. While some might have only one, some have two or more. We also have a large number of entities within the model making it not feasible to have a subset for each.
Any guidance on ways this can be filtered out would be most appreciated.
Matthew
Thank you for being the first post in our new forum!
If Currency was a function of entity, meaning there was a one to one then I would simply remove the currency parameter and replace it with a DBR inside the Excel file. However that’s not the case here, since you may load multiple sheets for multiple currencies based on the entity.
The solution mat vary a bit based on if you prefer the output reports being generated to all be individual files (easier) or if some of the reports should have multiple sheets (e.g. a report per entity and each report contains a sheet per currency). I’ll assume individual reports for now.
Approach 1
There a technique called Parameter Sets in ReportWORQ which allows you to provide combinations of elements to be spread across a combination of cells. This us useful if you have something like a Business Unit + Department pair where only certain combinations make sense and the cross join would result in too much unwanted combinations.
You can test this manually by creating a single ReportWORQ Parameter named: EntityPeriodCurrency
Set the Parameter Location to a pipe delimited string of the 3 cell locations to spread the values: A1|B1|C1
Now in the Parameters screen choose a Text List type parameter and set the parameter to One report per item. Each row in the text list will represent a report output and will contain a pipe delimited list of 3 values to spread to the cell locations:
Text List: NewJersey|October|USD Manchester|November|GBP Manchester|November|EUR
It may be possible to use a subset to load this list of strings as well.
Approach 2
Instead of managing this in ReportWORQ as parameters, you can manage the entire listing in a cube (or alternatively and for non-TM1 customers use a hidden Excel sheet for lookups). Replace the Entity, Period & Currency cells in the Excel report to load these 3 values from a lookup cube or worksheet using a key . Then provide ReportWORQ with a list of keys (either manually as a text list, or dynamically as a subset). Then ReportWORQ will loop through the list of keys creating worksheets or reports for each key. That key will be used to load up the 3 member selections as defined in the cube. This approach is more complicated, but allows you to maintain the listing in a cube or worksheet and then generate reports and sheets based on combinations of keys.
Building on this approach if you want to create a report per entity, but worksheets within a report for combinations of period and currency, this can now be achieved using ReportWORQ’s MDX Repeater feature. One MDX statement will query the list of entities for reports and the repeater statement will for each entity query the list of period/currency sets for worksheets. Let me know if you want more information on that.
