[EXCEL_LINK] Locale=… LicenseReminderDisabled=… URL=… CommandRecordingEnabled=… CalculationTimeoutBySecond=60
Overview of Excel-Link
This manual is in pilot operation.
|
Features
[Excel-Link] is a tool for accessing data from fusion_place through Microsoft Excel.
[Excel-Link] includes "Link Function", "Template Processing Function", and "Pivot Search Function". In any case when accessing ledger data, Ledger Access Control is applied.
Link Function
On a user-designed Excel sheet layout, you can set any number of "Linked Regions" (rectangular cell ranges on the sheet), and apply the following functionalities:
-
Retrieve ledger data from fusion_place and display it in each cell of the Linked Region.
-
Reflect data from each cell of the Linked Region back into the ledger of fusion_place.
-
Display names and property values of dimension members from fusion_place in the Linked Region.
For retrieving and reflecting ledger data (as mentioned above in points 1 and 2), you can specify processing conditions for each Linked Region (for more details, see the settings description for data regions).
-
Whether to perform both retrieval and reflection, or only one of the two.
-
The unit of display for amounts (e.g., data is held in units of yen in the ledger but displayed/input in thousands of yen on Excel).
-
Methods for rounding and the number of significant decimal places during unit conversion.
-
Methods for displaying signs of amounts (according to account properties, always positive for debit, or always positive for credit).
-
Whether to automatically switch the lock attributes of cells based on the input capabilities of the ledger cells.
-
Display an error message when the destination ledger cell is closed.
It is possible to set multiple "Linked Regions" on one sheet. For example, you could use ledger data retrieved in one Linked Region to calculate values using Excel formulas and write these values back to the ledger of fusion_place from another Linked Region.
Template Processing Function
The Template Processing Function is an extended version of the Link Function. While the Link Function requires specifying member labels used as keys for individual columns and rows, the Template Processing Function allows you to specify the order of member labels you want as row keys as a Member List and automatically generates rows (not supported for automatic column generation). You can also combine members from multiple dimensions as row keys.
With the Template Processing Function, you can easily create reports with variable numbers of rows. Moreover, like the Link Function, it not only displays data but also allows updates. For example, you can easily perform the following tasks:
-
Analyze the difference between budget and actual figures for customer value and number of customers per combination of store and product department (sales area), and reflect both difference data back into the ledger of fusion_place to make them visible in various reports.
-
Calculate budget personnel costs by applying planned personnel cost rates to the number of personnel in all departments and reflect these in the ledger of fusion_place.
-
Convert sales and expenses of overseas subsidiaries from foreign currency bases using exchange rates determined by each subsidiary’s currency.
The Template Processing Function defines a calculation model on the Excel sheet for one processing unit (e.g., one combination of store and product department) and applies this model to all processing units. Since the calculation model can be defined in Excel, the calculation process is also visualized. Moreover, as one model applies to all processing units, there is no need to write numerous formulas on the Excel sheet, resolving maintenance issues. Processing units are specified by members included in one dimension’s member list or combinations of members from several dimension lists.
In the worksheet’s "Sheet Processing Details", you can set "Template Processing Settings," which make the sheet subject to template processing. The Template Processing Settings also include a "Suppress Template Processing" checkbox, which, when ON, makes the sheet a normal "Link Function" target sheet.
Features
Link Function
Compared to many other spreadsheet integration tools, [Excel-Link]’s "Link Function" has the following features:
-
Bidirectional Data Integration: The Link Function works in both directions. That is, it can retrieve data from the fusion_place ledger onto an Excel sheet, and also reflect data from the sheet back into the fusion_place ledger.
-
Macro-Free Pull Method: In the Link Function, each cell on the Excel sheet can be directly linked to a cell in the fusion_place ledger (see Pull Method Spreadsheet Connectivity). This reduces the need for macros and enhances the maintainability of the sheet.
This functionality can be utilized in various forms of business operations.
-
As a Data Entry Sheet: You can create user-friendly data entry sheets in Excel and distribute them for field users to input data. Once the data is input, reflecting it into the fusion_place ledger allows sharing among stakeholders. Extracting data to another Excel sheet for analysis is also straightforward.
-
For Report Creation: For monthly reports and other documents created in Excel, you can simply add [Excel-Link] configuration information to the existing layout, and data from fusion_place can be flowed into it. This eliminates time-consuming data transcription tasks, avoids transcription errors, and reduces complex formulas (such as cross-sheet references), thus minimizing formula errors.
-
For Calculation Processes: You can extract data from fusion_place to Excel, perform calculations, and write the results back to fusion_place. This method allows you to leave complex calculations, such as allocations, to the familiar environment of Excel, and then share the results in the fusion_place database.
Pivot Search Function
[Excel-Link]’s "Pivot Search Function" has the following features:
-
Slice & Dice: The Pivot Search Function is the fusion_place version of the "Slice & Dice" feature provided by many BI tools. It allows you to easily display multidimensional data from various perspectives, making it suitable for ad-hoc analysis required in various business situations.
-
Sharing Analysis Results: With the Pivot Search Function, you can specify search conditions to import relevant ledger data into an Excel sheet in bulk and then perform analysis using Excel’s pivot table feature. Therefore, the created pivot table can be saved as a regular Excel file. Sending this file to other users allows easy sharing of analysis results.
-
Reuse of Search Conditions: If you frequently perform pivot searches with similar search conditions, you can save the search conditions to a file and reload them for reuse as needed.
Usage
[Excel-Link] is an add-in (XLA file) for Microsoft Excel. It becomes usable after Integrating the Add-In File into Microsoft Excel.
Differentiating from Forms
There is some overlap in the application areas of forms and [Excel-Link]. Please also refer to the document "[Excel-Link] and Forms, Which to Use" for guidance on how to differentiate their use.
About Timeouts
Server-Side Processing Timeouts
All server-side processes of [Excel-Link] will error out if the computation exceeds the execution timeout (default: 600 seconds).
Client-Side Processing Timeouts
In the client-side processing of [Excel-Link], Excel’s recalculation process is executed each time data is retrieved and pasted into a cell range. In this case, it is necessary to wait for the recalculation to finish before proceeding to the next process. However, in actual operation of Excel, there are times when, despite a long wait, the status does not change to recalculated. Therefore, [Excel-Link] waits by default for 20 seconds and if recalculation has not finished by then, displays the message, "Excel recalculations are significantly delayed and cannot be processed," and cancels the operation. This waiting time (20 seconds) can be changed by adding a description to the configuration file 'excel-link.ini' placed in the same folder as Client-Side Log Files.
< Example of excel-link.ini file content >
As shown in the example above, you can specify the waiting time in seconds by adding a line after [Excel-Link] in the format CalculationTimeoutBySecond=(seconds). Do not change other lines, as they contain values set from the interface.