TM1 perspectives TM1RPTROW problem

[JLeten]

Hi,

I'm trying to do the following: I have a TM1RPTVIEW of a cube with two TM1RPTROW (i.e. product and plant). Both TM1RPTROWS are filtered based on a dynamic expression. Yet the second TM1RPTROW being plant has to be filtered based on the result of the TM1RPTROW, yet when I try to make my second TM1RPTROW dependant on the first I no longer get any rows?

Any suggestions on how to solve this?

In the two code samples below I'd like to add an extra dimension to the filter of the second TM1RPTROW being: [Product Structure Dim5].[here comes the cell reference of the values of the first TM1RPTROW). Yet this doesn't seem to work.

First TM1RPTROW:

=TM1RPTROW($B$9;Parameters!$B$5 & ":Product Structure Dim5";"";"";"";0;"{ORDER({FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product Structure Dim5] )}, 0,1,2)}, [Com Budget Details].( [Budget Periods].[" & $D$16 & "], [Area Sales Managers].[" & $D$21 & "], [Budget Customer SubGroups].[" & $D$18 & "], [Com Budget Details Measures].[Summary] ) > 0 )}, [Com Budget Details].( [Budget Periods].[" & $D$16 & "], [Area Sales Managers].[" & $D$21 & "], [Budget Customer SubGroups].[" & $D$18 & "], [Com Budget Details Measures].[Sort] ), ASC )}";0;0)

Second TM1RPTROW:

=TM1RPTROW($B$9;Parameters!$B$5 & ":Company Structure";"";"";"";0;"{ORDER({FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Company Structure] )}, 0)}, [Com Budget Details].( [Budget Periods].["&$D$16&"], [Area Sales Managers].["&$D$21&"], [Budget Customer SubGroups].["&$D$23&"], [Com Budget Details Measures].[Summary] ) > 0 )}, [Com Budget Details].( [Budget Periods].["&$D$16&"], [Area Sales Managers].["&$D$21&"], [Budget Customer SubGroups].["&$D$23&"], [Com Budget Details Measures].[Sort] ), ASC )}";0;0)

[tomok]

That's not going to work. The active form model is pretty good but it can't handle that, meaning cell references in one TM1RPTROW formula cannot point to a cell in another TM1RPTROW column. If you are going to point to a cell reference it has to be outside the active form area.

[JLeten]

Any chance I can solve my problem in a differen way?

[tomok]

Well, you didn't really explain what you were trying to do except to say you were trying to base one TM1RPTROW formula off the results of another, which you cannot do. If that is what your problem is then the answer is no. Since you didn't any explanation is to why you want to do that then I can't provide any further input.

[JLeten]

AH my mistake, let me explain what I'm trying to accomplish.

I'n my first TM1RPTROW function I'm retrieving Products that a certain Sales Manager is selling, say for example bikes, accessories and clothing.

In the second TM1RPTROW functions I'm trying to retrieve the specific Plants these Products are sold in. Plants are for example Shop 1, Shop 2 and Shop 3. Based on previous actuals of the Products of the first TM1RPTROW function I need to retrieve the corresponding Plants.

For Example:

Bikes	Shop 1	1000
Accessories	Shop 1	100
	Shop 2	2000
Clothing	Shop 3	50

Does this make any sense to you?

[tomok]

Is there some underlying reason why zero-suppressing the report won't work? If there are no sales for a Product/Plant combination the row will be suppressed.

[lotsaram]

... let me explain what I'm trying to accomplish.

I'n my first TM1RPTROW function I'm retrieving Products that a certain Sales Manager is selling, say for example bikes, accessories and clothing.

In the second TM1RPTROW functions I'm trying to retrieve the specific Plants these Products are sold in. Plants are for example Shop 1, Shop 2 and Shop 3. Based on previous actuals of the Products of the first TM1RPTROW function I need to retrieve the corresponding Plants.

Based on your explanation it doesn't sound like you need to do anything. Except of course to turn null-suppression on. Hey presto null-suppression will automatically filter everything down to valid combinations.

If you are trying to make a planning input template and you don't have any data for the time series on the columns yet as it will be input by the user and you want to "pre-filter" the row combinations based on the prior year actual then you really haven't explained your use case very well. If this is actually what you are doing then the answer is still null-suppression. Just base the active form off a single column actual | total year for the previous year. Then hide that column and have another VIEW formula (not TM1RPTVIEW) pointing to Forecast | current year and base the cells that you are displaying on the 2nd VIEW formula.

[JLeten]

Putting zero suppression on does solve this problem I know, but it creates new ones. Users of the TM1 application are able to create new rows based on Products they select in a particular screen. As these manual selected products won't have actuals, they wouldn't appear because of the zero suppression.

[lotsaram]

Putting zero suppression on does solve this problem I know, but it creates new ones. Users of the TM1 application are able to create new rows based on Products they select in a particular screen. As these manual selected products won't have actuals, they wouldn't appear because of the zero suppression.

The answer is still zero supression. Just think a little outside the box.

A simple solution:
- A consolidation in your time dimension that groups last year and planning year
- a numeric 1/0 flag measure populated for the "manually selected" products
- a consolidation to group actuals and the manually selected flag
- base the hidden column active form zero suppression on the combination of grouped PYACT/plan year and actual/manual flag products

Take it from there and make it as refined or more elegant but that should be enough to get you started. For someone who knows what they are doing this can be seamless to the end user, be real time and perform well.

[paulsimon]

Hi

We have a similar situation.

We defined a single measure to drive the zero suppression. The sheet collects budgets, and we want it to show combinations of Account, Cost Centre and Project whenever there is a value in the Budget or Forecast for any of the Current 5 years, or Previous Year Actual. I defined a calculated measure called Single Point that just has a rule setting it to 1. The value doesn't matter, it is the feeding that is important. It is fed from the appropriate Version and Time consolidations for each combination of Account, Cost Centre and Project. The feeding goes to just this measure, for one version, and one time period, and one of all other dimensions apart from the Account, Cost Centre, and Project that must be able to vary. This explains why it is referred to as a Single Point.

The Single Point Measure sits below a Single Point consolidation. There is also a Single Point Input measure consolidating in to this.

If the user is budgeting for a combination that is completely new, then they have a line at the top of the sheet where they can select the Account, Cost Centre and Project combination that they want to add, and they then run a process that puts a 1 into the SIngle Point Input measure via an Action Button. I did try a DBS but this did not work nicely.

By driving the zero suppression off the SIngle Point consolidation, this means that a new combination is not zero suppressed, because of the 1 that the process stored.

The Active Form is only used to retrieve the Single Point Consolidation. We are not interested in the value of the Single Point Consolidation so that column is hidden. It is only there to drive the zero suppression to give the appropriate combinations of Account, Cost Centre and Project. All other columns ie 12 months of Budget, totals and variances, come from Views. We found that this approach took around half the time to refresh compared to our previous approach that have 3-4 columns based on the Active Form and the rest from Views.

As a further refinement, when the process is run to add a new combination, it checks against a cube of valid combinations to ensure that eg the Account is valid for the overall Entity that they select at the top.

Regards

Paul Simon

[whitej_d]

In this situation I prefer to use a separate control cube to define the valid combinations of product/shop and any other dimension which is relevant. I think this is a good approach for 2 reasons. First, a valid combination of product/shop is possibly relevant to more than one cube, and I don't want to have to repeat too much logic in every cube I need to filter, and second, trying to include the flag in a cube with more dimensions than the valid combination creates feeding issues and can end up being quite messy (Do you feed all year/ months /versions for flagged combos, if so overfeeding can be hard to avoid.).

I then build my active form using zero suppression directly on the control cube with one column pointing to the flagged value, and use normal dbrw references for the rest of the active form which points to the real input cube.