Selecting Multiple Members in Smart View Ad Hoc

You can select multiple members from dimensions in rows and columns

You may select multiple members in dimensions that are in the rows or columns of a Smart View ad hoc report. Dimensions that are in the Point of View (POV) in row 1 may have only one member selected. If you need multiple members in a dimension, move the dimension to the rows or columns first, and then select the members.

Select one member for dimensions in POV and multiple members for dimensions in rows and columns

How to enter multiple members

There are a few different ways to enter multiple members:

  • Type the members into Excel

  • Member Selection dialog box

  • Zoom In to next level

  • Zoom in with summary on top

  • Zoom in to all levels or bottom level only

And for your consideration, we offer some strategies for working with large dimensions and suppressing the display of rows with no data:

  • Strategies for selecting members from large dimensions

  • Suppress rows with no data 

Type the members into Excel

If you know how the members are spelled, typing them into Excel is a quick way to enter them. For example, you could type Total Revenue and Total Expenses into two rows for the Account dimension. 

However, if you want Total Revenue & Operating Transfers and are not sure exactly how that member is spelled, you should open the Member Selection dialog box and select it from the list to avoid an incorrectly spelled member.

At best, an incorrectly spelled member returns no results. If the misspelled member is the only member in the dimension, Smart View will not be able to identify the dimension, so the dimension will be added to the POV. This situation is difficult to remedy.

Use the Member Selection dialog box

The order in which you check the boxes for members in the left panel determines the order of appearance in the right panel. In this example, the user first checked Total Revenue & Transfers and Total Expenses, and then checked the next level accounts: Total Revenue, Operating Transfers, Total Compensation, and Total Non Compensation.

With a member selected, you can use the ^ button to move the member up in the list, or the downward facing caret to move the member down. The drop-downs for these buttons have options to move directly to the top or bottom of the list.

 Member Selection dialog box with Move Up button highlighted

Zoom In

When you have a member in the Excel sheet, you can double-click the member to zoom in to the next level. Or you can use the Zoom In button on the Essbase menu to zoom in.

Zoom In button example

With Total Expenses selected in Excel, clicking the Zoom In button expands Total Expenses to the next level. The three rows that roll up to Total Expenses are shown above Total Expenses.

Members with summary at bottom and no indention

Zoom In – Summary on Top

If you prefer to have Total Expenses, which is the summary level member, at the top of the list as presented in the SRECNA reports, you can change the settings in the Options dialog box. 

Members with summary at top and subitems indented

From the Member Options section, set Ancestor Position to Top. If you would like the lower level members indented as shown above, select Subitems in the Indentation drop-down.

Options dialog box with Member Options highlighted

If you want these settings as the default for ad hoc reports you create in the future, open the drop-down list on the OK button and select Save as Default Options. All new ad hoc reports you create from now on will use these options.

Options Save button with Default highlighted

After you save the options, refresh the data to see the effect of your newly selected options.

Zoom in to all levels or to bottom level only

So far, we have discussed zooming in to the next level. It is possible to drill down one level at a time to see more detail, but that can be tedious with dimensions such as Entity, Account, and Fund that are hierarchies with many levels. 

In this example, we zoomed in on Operating Transfers to the next level.

Example of zoom in to next level

All Levels

If we wanted to see all of the levels that roll up to Campus Support, we could open the Zoom In drop-down and select All Levels.

Zoom in drop-down menu with All Levels highlighted

The Account dimension now shows all of the members in the dimension that roll up to Campus Support. Notice that the levels of the hierarchy are indented.

Zoom In example with all levels

Bottom Level

The bottom level is the detail level where transactions are recorded. In this level, each member has a code that uses five digits and a description. All other levels are summaries that are calculated from the detail levels; summary level members often have just a description, they may or may not have a code. If you want to see only the bottom level, select Bottom Level from the Zoom In drop-down menu. This example shows the results when Campus Support is selected and the user zooms in to the bottom level.

Zoom In example with bottom level

Selecting by Level from within the Member Selection dialog box

The above examples of drilling down to all levels or the bottom level are executed from the Zoom In button on the Essbase ribbon.

It is also possible to select by level from within the Member Selection dialog box. This method checks the boxes of members based on your selection. The terminology is different from that on the Zoom In button, but the concepts are the same.

Check Children

Children refers to the next level of detail.

Check Descendants

Descendants refers to all members at more detailed levels.

Check Base Members

Base Members are the detailed members. No summary levels are included with this selection.

Options button in Member Selection dialog box with menu selections showing

Strategies for selecting members from large dimensions

Some dimensions such as Entity, Account, and Fund are organized into hierarchies with many levels. In addition to multiple levels, these dimensions have a significant number of members. Having a good understanding of the size of a dimension and its hierarchy can help you work efficiently to explore data in large dimensions. 

This chart shows the number of hierarchy levels and members in the Entity, Account, and Fund dimensions and includes a recommendation of the hierarchy level from which you can zoom in to all levels or the bottom level. Following these guidelines allows you to glean the information you need while using a reasonable amount of the shared server resources.

Dimension Levels Members (approximate) Recommended hierarchy level from which to zoom in to all levels or bottom level
Entity 7 7,000 L3 (Division) or L4 (Department) or below
Account 8 1,400 Account Category, e.g. 51XXX - Staff Wages, 550XX - General Supplies, etc.
Fund 7 45,000

Do not select All Levels or Bottom Level from this dimension Use Next Level to drill down one level at a time where there is data.

Data may exist in any of the 45,000 distinct funds for the Actual scenario.

Plan data for the Forecast and Operating Budget scenarios is included in the five planning accounts whose description ends with "- Plan." 

Suppress rows

When you have many members in the rows of your query, some may have a value of zero or have no data. In this case, you may wish to suppress the rows. In this example, Campus Support has been expanded to all levels. The first image shows all accounts. The second image is with row suppression applied; note that accounts with no data / missing or those that have a value of zero are not included.

Accounts before suppressing rows

Account list before applying row suppression

Accounts after suppressing rows

Account list after applying row suppression

Apply Row Suppression

1. From the Smart View ribbon, open the Options dialog box.

2. Select Data Options in the left panel.

3. Check the boxes for No Data / Missing and Zero.

Options dialog box with Suppress Rows options checked

4. Click the OK button to save changes for this sheet. (We recommend that you do not select Save as Default Options because it may prevent Essbase from returning any results on new queries.)

5. Refresh the sheet to fetch data without the suppressed rows.