TM1 Forum

Is it possible to define a rule that will calculate a percentage of column total that will be valid in any cube view regardless of what dimension is on the row edge?

For example say I have a cube with dimensions Product, Sales Rep and Day, and measures Quantity, Net Amount and Percent Total, where Percent Total is defined by this rule that calculates the percentage that the Net Amount contributes to the column total. If Sales Rep is on the row edge of the cube view then it would look something like:
['Percent Total'] = ['Net Amount'] \ ['Total Sales Rep', 'Net Amount'];

But how do I make this generic so it will apply to any cube view regardless of what's on the row edge?

Thanks.

I think you are thinking in database terms there my friend. Rules don't apply to columns. (or rows to that matter) As long as the the consolidations exist in the dimensions you quoted then the rule you stated is valid.

I'm not thinking in database terms. A cube view is a crosstab with a row edge and a column edge, or two axes in MDX terms. I know the rule I specified is valid but I need it to be generic so that it will always apply to the consolidation of whatever dimension elements are on the row edge/axis. Make sense?

Thanks.

A cube view is slice of an nth dimension object. It is not just a cross tab, hence the reason for saying you were thinking in database terms. Once you understand what I mean you have in effect answered your own question.

Right, I get how it's a slice of an n-dimension cube. That makes sense. At least two of those dimensions are represented on the crosstab. Let's say for arguments sake I have two cube views. The first shows Sales Reps on the row edge of the cross tab and the second shows Products on the row edge of the cross tab. Both show Months along the column edge. Can I define a single rule for calculating my Percent Total measure that will work on both of these cube views to show me the percent that the Net Amount is contributing to the consolidation of the elements on the row edge? I'm not seeing how I've answered my own question here. I'm new to TM1 so I appreciate your patience.

Thanks.

nerph wrote:Can I define a single rule for calculating my Percent Total measure that will work on both of these cube views to show me the percent that the Net Amount is contributing to the consolidation of the elements on the row edge?

No, a rule is not going to change its definition because of the way a user has a cube-view configured. The rules are defined server-side. Off the top of my head (with all the usual disclaimers) I'd try creating two measures, e.g. % of Total Product and % of Total Reps and write separate rules. The one you wrote already seems fine, as Jim points out. You just need to write another.

You can achieve the 'generic' effect you want by using the Excel client (Perspectives/ Xclerator) and doing the calculations in the spreadsheet as an a/b type calculation. However, although this is generic, it has the problem that the spreadsheet calculation is not as strictly version controlled as a server-side rule.