Hi,

Is there a feature to add an overall total column?

Assume the below example. I am showing all the leaf level elements of 2 dimensions (Closing version and Journal number).
I want to add a "calculated" total column that sums both dimensions.
I can add a subset calculation, but this only applies to one dimension.

Adding one subset calculation:

Adding two subset calculations, the desired total is highlighted:

The only workaround I know is creating a Total element in each of the desired column dimensions and adding rules to feed these elements.
This is rather complex and not very flexible.

To me this would be a verry useful feature. Is this on the roadmap?

(Paw version: 2.0.81)

Thanks,

Felix Stuyck

Hi,

Due to the nature of cube being multidimensional; you do need to separately add the totals to each of the nested hierarchies (as you have done with your two subset calculations.

You can then hide the combinations of the columns that you don't want by using the "target_selection" > that will allow you to click on individual columns and select hide.

You can also do this directly in the MDX for the view (which is what is done automatically for you when you do the above steps in the UI) but for something like this the UI option is probably good enough.

Note that depending on your version of workspace "Target Selection" is accessed differently; in the older versions it was a toggle box appearing on the cube viewer itself (as per your screenshot its that little toggle where the columns and rows meet (top left))... in the latest couple of versions you access it through the ribbon instead.

Thanks,
Declan

Hi,

Is there a feature to add an overall total column?

Assume the below example. I am showing all the leaf level elements of 2 dimensions (Closing version and Journal number).
I want to add a "calculated" total column that sums both dimensions.
I can add a subset calculation, but this only applies to one dimension.

Adding one subset calculation:

Adding two subset calculations, the desired total is highlighted:

The only workaround I know is creating a Total element in each of the desired column dimensions and adding rules to feed these elements.
This is rather complex and not very flexible.

To me this would be a verry useful feature. Is this on the roadmap?

(Paw version: 2.0.81)

Thanks,

Felix Stuyck

Hi,

Thanks for the response, target selection works.
The drawback is that if there are new column combinations, a new target selection has to be made.

Thanks,
Felix Stuyck

Hi,

Due to the nature of cube being multidimensional; you do need to separately add the totals to each of the nested hierarchies (as you have done with your two subset calculations.

You can then hide the combinations of the columns that you don't want by using the "target_selection" > that will allow you to click on individual columns and select hide.

You can also do this directly in the MDX for the view (which is what is done automatically for you when you do the above steps in the UI) but for something like this the UI option is probably good enough.

Note that depending on your version of workspace "Target Selection" is accessed differently; in the older versions it was a toggle box appearing on the cube viewer itself (as per your screenshot its that little toggle where the columns and rows meet (top left))... in the latest couple of versions you access it through the ribbon instead.

Thanks,
Declan

Hi,

Is there a feature to add an overall total column?

Assume the below example. I am showing all the leaf level elements of 2 dimensions (Closing version and Journal number).
I want to add a "calculated" total column that sums both dimensions.
I can add a subset calculation, but this only applies to one dimension.

Adding one subset calculation:

Adding two subset calculations, the desired total is highlighted:

The only workaround I know is creating a Total element in each of the desired column dimensions and adding rules to feed these elements.
This is rather complex and not very flexible.

To me this would be a verry useful feature. Is this on the roadmap?

(Paw version: 2.0.81)

Thanks,

Felix Stuyck

Hi,

This is where using the MDX view instead of the GUI will be beneficial as you can make it completely dynamic if the subsets are going to change.

The MDX would look roughly like below based on the screenshots that you have provided. The Member calculations at the top are pretty simple sums of the subsets you have.

The section between "SELECT" and "ON 0" indicates the column headers. So we do a pretty simple join of the 2 subsets first; and then a comma is used to indicate we are providing a list to be returned and the second part of that list is simply the 2 "Members" we calculate joined with each other.

Any time that new elements are added to either of the subsets (or elements are removed) the calculated member results would get updated automatically. And the elements that are returned in the first part of the "ON 0" will also get updated automatically.

WITH MEMBER 	[Closing Version].[Closing Version].[Total] AS 	SUM({DISTINCT({TM1SubsetToSet([Closing Version].[Closing Version],"Base","public")})})	, SOLVE_ORDER = 1	, FORMAT_STRING = '#,##0;(#,##0)' MEMBER 	[Journal Number].[Journal Number].[Total] AS 	SUM({DISTINCT({TM1SubsetToSet([Journal Number].[Journal Number],"Base","public")})})	, SOLVE_ORDER = 2	, FORMAT_STRING = '#,##0;(#,##0)' SELECT {	{TM1SubsetToSet([Closing Version].[Closing Version],"Base", "public")}	*	{TM1SubsetToSet([Journal Number].[Journal Number],"Base", "public")},	{[Closing Version].[Closing Version].[Total]}	*	{[Journal Number].[Journal Number].[Total]}} ON 0,{TM1SubsetToSet([Account].[Account],"PL Short","public")} ON 1 FROM 	[<YourCubeName>] WHERE 	(		[<TitleDim1>].[<Hierarchy1>].[<TitleElement1>], 		...		[<TitleDimX>].[<HierarchyX>].[<TitleElementX>]	)

Thanks,
Declan 

Hi,

Thanks for the response, target selection works.
The drawback is that if there are new column combinations, a new target selection has to be made.

Thanks,
Felix Stuyck

Hi,

Due to the nature of cube being multidimensional; you do need to separately add the totals to each of the nested hierarchies (as you have done with your two subset calculations.

You can then hide the combinations of the columns that you don't want by using the "target_selection" > that will allow you to click on individual columns and select hide.

You can also do this directly in the MDX for the view (which is what is done automatically for you when you do the above steps in the UI) but for something like this the UI option is probably good enough.

Note that depending on your version of workspace "Target Selection" is accessed differently; in the older versions it was a toggle box appearing on the cube viewer itself (as per your screenshot its that little toggle where the columns and rows meet (top left))... in the latest couple of versions you access it through the ribbon instead.

Thanks,
Declan

Hi,

Is there a feature to add an overall total column?

Assume the below example. I am showing all the leaf level elements of 2 dimensions (Closing version and Journal number).
I want to add a "calculated" total column that sums both dimensions.
I can add a subset calculation, but this only applies to one dimension.

Adding one subset calculation:

Adding two subset calculations, the desired total is highlighted:

The only workaround I know is creating a Total element in each of the desired column dimensions and adding rules to feed these elements.
This is rather complex and not very flexible.

To me this would be a verry useful feature. Is this on the roadmap?

(Paw version: 2.0.81)

Thanks,

Felix Stuyck