Pivot Query Function

« Sheet Processing Guidelines | Pull Method Spreadsheet Integration (Excel-Link) »

This manual is in pilot operation.

The Pivot Query Function allows you to specify conditions to import ledger data from fusion_place into an Excel sheet and display it as a pivot table. The search conditions are specified as shown below:

Below is a description of each setting:

(1) Common Items

Item	Mandatory	Content
Display as Label	✓	Switch between label and name display for this screen, and similarly switch for search results.
Create a Data Sheet	✓	If checked, the raw data for the pivot table is imported into a separate sheet named DATA. However, if the number of data items is too high, due to the row limit of Excel sheets, data cannot be imported and the pivot table will not be displayed. In that case, uncheck and execute. Although no data sheet will be created, the pivot table will be displayed. ⚠ In Excel 2010 64-bit, executing without checking will result in an error. In Excel 2010, the limit on the number of sheet rows has been significantly relaxed, so there is almost no need to uncheck. If this causes inconvenience, you can download the 64-bit version of "Microsoft Access Database Engine 2010 Redistributable" from Microsoft’s webpage and install it on each client PC to resolve the error. The URL for download is as follows: http://www.microsoft.com/download/en/details.aspx?id=13255
Application	✓	The application to be accessed.
Ledger	✓	The ledger within the selected application to be accessed.
Participant	✓	Used to determine access rights during the search Participant. ⚠ Displays a list of participants that have permissions, which are included in the selected application and specified at the time of fusion_place login.
Ledger Edition	✓	The ledger edition to be accessed. The ledger edition is one of the following: ・Published ・Workspace
Sign Handling	✓	Choose from the following three methods for displaying signs when retrieving amounts/numbers into the sheet: ・Display according to the account attribute ・Always display as Debit+ ・Always display as Credit+

Item

Mandatory

Content

Display as Label

✓

Switch between label and name display for this screen, and similarly switch for search results.

Create a Data Sheet

✓

If checked, the raw data for the pivot table is imported into a separate sheet named DATA. However, if the number of data items is too high, due to the row limit of Excel sheets, data cannot be imported and the pivot table will not be displayed. In that case, uncheck and execute. Although no data sheet will be created, the pivot table will be displayed.

⚠ In Excel 2010 64-bit, executing without checking will result in an error. In Excel 2010, the limit on the number of sheet rows has been significantly relaxed, so there is almost no need to uncheck. If this causes inconvenience, you can download the 64-bit version of "Microsoft Access Database Engine 2010 Redistributable" from Microsoft’s webpage and install it on each client PC to resolve the error. The URL for download is as follows:
http://www.microsoft.com/download/en/details.aspx?id=13255

Application

✓

The application to be accessed.

Ledger

✓

The ledger within the selected application to be accessed.

Participant

✓

Used to determine access rights during the search Participant.

⚠ Displays a list of participants that have permissions, which are included in the selected application and specified at the time of fusion_place login.

Ledger Edition

✓

The ledger edition to be accessed. The ledger edition is one of the following: ・Published
・Workspace

Sign Handling

✓

Choose from the following three methods for displaying signs when retrieving amounts/numbers into the sheet:
・Display according to the account attribute
・Always display as Debit+
・Always display as Credit+

(2) Filter Conditions per Dimension

In the "Filter" table in the center of the screen above, conditions (filters) for narrowing down the search target members are displayed for each dimension. Double-click a row of a dimension or click to highlight it, then click the Edit Filter button to display the screen for changing the filter conditions for each dimension.

Item	Mandatory	Content
Peg Member	✓	Specify the member you want to use as the starting point (apex) for the search range. In addition to specifying individual members, you can also specify "all root members" as starting points.
Expansion Method	✓	Specify the range of members to include in the search range based on the peg member. The following specifications are possible: Self only Include only the peg member in the search range Include child members Include the peg member and its immediate child members in the search range Include descendant members Include the peg member and its descendant members (direct and indirect children) in the search range Only lowest members Include only the lowest members under the peg member in the search range

Item

Mandatory

Content

Peg Member

✓

Specify the member you want to use as the starting point (apex) for the search range. In addition to specifying individual members, you can also specify "all root members" as starting points.

Expansion Method

✓

Specify the range of members to include in the search range based on the peg member. The following specifications are possible:

Self only: Include only the peg member in the search range
Include child members: Include the peg member and its immediate child members in the search range
Include descendant members: Include the peg member and its descendant members (direct and indirect children) in the search range
Only lowest members: Include only the lowest members under the peg member in the search range

Saving and Loading Search Conditions

You can save the configured search conditions to a file and reload them for reuse as many times as needed. Saving and loading are done via the Save Search Conditions and Load Search Conditions buttons on the search condition specification screen above.