Proposal Summary
This proposal covers the scope KPI Dashboard customizations based on Excel refresh method.
Requirement
Hygiene Technologies aims to create various types of Key Performance Indicators (KPIs). This customization needs to allow users to access the KPI view without NetSuite access.The primary objective of customizing the KPI is to enable its usage outside of NetSuite by providing an external link.
The Search Details which is provied by the Hygiene Team is
| Saved Search Name | Saved Search Internal ID |
| a 1100 Sales by SO JS2 | 1290 |
| a 1510 Cost of Sales by Fulfilments JS2 | 1289 |
| Opp Points | 1398 |
| Sales Page Forecast – Dashboard | 1017 |
| Weighted Pipeline Value | 1387 |
| DIFOTIS Graph | 754 |
| KPI Build Actual less Target Minutes today | 1305 |
| KPI Avg days Open Invoices – 14 days grace | 934 |
- There is also a Report indicated with the saved search file data, then create a saved search from the report and the addition of this search ID to the custom record.
Deliverables
- A custom record is created to store all saved search internal IDs. Based on this custom record we will fetch the data from the search and write to Excel file. We also configure a security key in the custom record for data security.
- Suitelet scripts is used to write the search results to the Excel Desktop application.
- Validation checks will be implemented if we try to access data using distinct combinations of saved searches and security key stored in the custom record.
- We will store the following search IDs associated with a security key within the custom record.
- The search results will be displayed as each rows in the excel tabs.
Initial Setup
- Generate an Excel file in a designated OneDrive location and subsequently launch the file using the Desktop Application.
- Next, we can define the query to retrieve data from an External System (NetSuite).
- It’s essential to establish a refresh interval for the Excel File within the opened Desktop version and activate the Auto Save functionality.
- The Desktop Excel application should always remain active.
Excel in Web
- Consequently, any refresh made in the external system (NetSuite) Desktop version will be automatically updated and it will be reflected in the Online version of the Excel File.
Assumptions and Limitations
Microsoft Excel does not allow the external system(NetSuite) to write the data to the MS Excel online platforms. Hence we need to utilize both desktop-based and online Excel applications as the solution.
We assume that there will be a Licensed Excel desktop application with Auto Save and Auto Refresh functionality is enabled.
The Desktop Excel application should always remain active to automatically update the data to the Excel in the web.
We will display the data based on the saved search results. Ie the data is based on the filters specified in the search. And the available filters cannot be added to Excel.
The data will be displayed in the following format. And the saved search is displayed in the different sheets of workbook.