How to add two members together?
Let's say that we have a report that contains a list of countries. Is there a way to combine two or more countries into a new aggregate value. For example, Canada + USA = North America?
1 reply
-
There are two quick ways to do this: one via the Elements tab and one directly on the report itself. In the Elements tab, you can click on the label of the first country to select it and then hold down your Ctrl-key and click on the second country label. Once both are selected, right-click on either label and choose the menu Create Formula > Aggregate Functions > Aggregate. This will create a new calculated member that adds the 2 countries together. You can keep the default name that it creates or you can rename it by right-clicking on the new member and choose Properties for use in a single report or Make Shareable for reuse in multiple reports. Once renamed, just check the box to select the new member and click on the Pyramid logo in the upper left to Execute the query.
- Select Countries
- Right-click on selection and choose menu Create Formula > Aggregate Functions > Aggregate
- Rename Aggregate member or make it shareable.
4. Add new member to the query and execute.
The other way to do it is to click on the first country label in the table/grid and hold down your Ctrl-key to select the second country. Right-click on either selection and choose the menu Quick Calc > Grouping > Group & List All Others. This will create two custom objects: an aggregate member for your selection and a List for all the other countries. See the help file for more details on all three Grouping menu choices (HELP LINK).
- Select Countries