Proposal summary
JCurve Solutions would like to create new a report to retrieve data for data from Account balance based on chart of account that set to show in report. And the Report will show intercompany amount effect to each account in separate column between intercompany, CDG group – Related. And print reports in Excel format.
Requirement
JCurve Solutions would like to create new a report to retrieve data from Account balance based on chart of account that set to show in report. And the Report will show intercompany amount effect to each account in separate column between intercompany, CDG group – Related.
Assumptions:
- This report will retrieve data from Account balance based on chart of account that set to show in report but will need to check balance end of month and year of reporting Criteria
- Report will show intercompany amount effect to each account but separate column between intercompany, CDG group – Relate
- It possible to have column expand to 40 columns
- It possible to have line level up to 200 lines
- Print report as excel format
Sample Report:
Report Details – Criteria:
| Criteria | |||
| # | Field | Description | Details |
| 1 | Subsidiary | Select Subsidiary for report | name of Subsidiary |
| 2 | As of Year | Select as of Year to filter transaction | Input as at Year to recall report to filter transaction within year |
| 3 | As of Month | Select as of Month to filter transaction | Input as at Month to recall report to filter transaction within month |
| Report Details | |||
| # | Field | Description | Details |
| 1 | Company Name | Show Subsidiary name that select for report | name of Subsidiary |
| 2 | Related Party Transaction – ByCompany | Report Name | Fixed |
| 3 | As at Month… Year… | Show Month and Year recall the report | Based on month and year select in the criteria |
| Report Column | |||
| # | Field | Description | Details |
| 1 | Number | Account Code | Number of accounts which will seperate line for account account 1107-0101 สินค้าที มีเพื อขาย (Debit one line and credit one line) |
| 2 | Account Name | Account Name | Name of accounts |
| 3 | Intercompany Name | Summary all data relate to this company | Company Name if the Customer Type = G-Able Group and Name is notsame as company name in the selection criteria |
| 4 | Total Subsidiaries | Summary all the data per account for allIntercompany Company | Summary by account for all intercompany |
| 5 | CDG group – Relate | Summary all data relate to this company | Company Name if the Customer Type = CDG Group |
| 6 | Total Other Related Comp. | Summary all the data per account for all OtherRelated Comp | Summary by account for all Related Comp. |
| 7 | Grand Total | Grand Total (Amt. total Subsidiaries + Amt. total other related comp.) | |
| Report Row | |||
| # | Field | Description | Details |
| 1 | Amount for each column | Summary amount based on each column | Summary Debit – Credit based on account per line and group bycolumn |
Our Solution
This requirement can be achieved by creating a new report using a transaction saved search. And this search will retrieve data from all transactions based on chart of account that set to show in report. The data shown in the report is related to customer/vendor type and com conso short name of all transaction’s customer and vendor records.
The navigation to the custom report can be added from the classic center. The user can navigate to Reports -> Custom Reports -> Related Party Transaction – By Company. Then upon clicking the “Related Party Transaction – By Company”, the user will be redirected to a report page.
The report will first display a detailed report based on the default filters, such as the subsidiary as G-ABLE CO., LTD, As of Month as Current Month and the As of Year as Current Year, and then show a summary of the all amount column in the report’s last row.
The fields given below will be indicated on the header part of the report.
| Criteria/ Available Filter: | ||
| # | Field | Field Name from NS records |
| 1 | Subsidiary | Subsidiary(subsidiary) list |
| 2 | As of Month | Month consider from Date(trandate) on transactions. |
| 3 | As of Year | Year consider from Date(trandate) on transactions. |
| Backend Criteria for Report | ||
| 1 | Posting | True(Will display all of the transactions that have been posted). |
| Report Details: | ||
| # | Field | Field Name from NS records |
| 1 | Company Name | Subsidiary(subsidiary) name selected in filter |
| 2 | Related Party Transaction – ByCompany | Fixed |
| 3 | AS at Month … Year … | Based on the date(trandate) select in the criteria |
Please see the table below for the report column details for the detailed ‘Related Party Transaction – By Company’ report.
| Report Column | ||
| # | Field | Field Name from NS records |
| 1 | Number | Number(acctnumber) from char of account record. |
| 2 | Account Name | Name(acctname) from char of account record |
| 3 | Intercompany Name | When the subsidiary on the transaction is the same as the subsidiary specified in the filter, the amount from all transactions will be displayed in appropriate columns based on the following criteria:If the Customer(custentity_customer_type)/Vendor Type(custentity_vendor_type_new) = G-Able Group of the filtered transactions.If the Com Conso Short Name(cseg_comcoso_shortn) from a customer/vendor of transaction record is same as the Intercompany column names. |
| 4 | Total Subsidiaries | The total amount of the Intercompany Name columns |
| 5 | CDG group – Relate | When the subsidiary on the transaction is the same as the subsidiary specified in the filter, the amount from all transactions will be displayed in appropriate columns based on the following criteria:If the Customer(custentity_customer_type)/Vendor Type(custentity_vendor_type_new) = CDG Group of the filtered transactions.If the Com Conso Short Name (cseg_comcoso_shortn) from a customer/vendor of transaction record is same as the CDG group column names. |
| 6 | Total Other Related Comp. | The total amount of the CDG group related Name columns. |
| 7 | Grand Total | The sum of Total Subsidiaries and Total Other Related Comp. |
Finally, on the report page, an Export button will be added to download the detailed report in Excel format.
When you click the export button, the report will be downloaded in Excel format based on the report’s size in the following cases:
- If the file size is less than 10Mb (based on predefined line count), the excel file will be downloaded directly from Netsuite. And in real-time, the excel file will be exported by split files based on the line counts that can be included in the single file.
- Otherwise, the Report Download will take place through a scheduled process where there is a delay in the process based on the availability of the Netsuite Queue. For that will make use if a custom record to track the requests
- If the file size is greater than 10Mb, then we will split the file into multiple files with predefined line counts in a single file due to the size of each individual attachment cannot exceed 10Mb. And the generated multiple files will be emailed to the initiator’s email if the file count is smaller than or equal to the specific count that we can attach in the email.
- If the size of files is greater than the specific count, we will generate multiple files with predefined line counts in a single file. And all generated files will be saved in NetSuite’s file cabinet and attached to the corresponding custom record entry which tracks that Report generation process.
Once all export functions have been completed in NetSuite, a single email with the custom record link will be sent to the download initiator. The initiator can export files by login to the NetSuite and by navigating to the custom record entry with the link from the email.
The summary line on the Excel file will be calculated based on the result lines included in the corresponding file in the case of split file download
Note: Exported multiple files will be attached to the email is a very exceptional case. i.e. Most of the export function occurs in real-time or the files attached to the custom record.
Assumptions
- Each report page can include 1000 lines, with the 1001st line displaying a summary of the Total Amount of all results in the report.
- The report can be created based the reference search JJ Summary PRT Report JS-456 that is confirmed by Jcurve
Risks
- The line counts in the single file to progress with the scheduled process can be confirmed during the development phase.
- If the file is more than 19Mb, the user should go to NetSuite and manually download the file from the account.
- The file cabinet will be stored with many files in the future. This will have an impact on the NetSuite account’s file cabinet. So, users have to remove these files from a file cabinet in the future to free up the storage.
- The report on the UI will be displayed as normal NetSuite Table layout without any styles. It will follow the standard behavior