How To: Manage Excel Templates in Web Reports

Earlier when the user exported the report's data into an Excel sheet, each and every time he had to setup the whole template. Now, Ginesys has enabled an one-time setup. This article describes the process of setting up Excel Templates in Web Reports.  

Pre-requisites

1. MS Office (recommended) package must be installed in the system.
2. Ginesys Web Report must be configured properly. 

Step-by-step guide

The steps are as follows: 

1. Go to Ginesys Web > Report > Home.
2. Click on the Plus Icon () in the upper left corner of the window.
3. A drop down menu will open.
4. Click on the Advanced Report option.
5. Report creation wizard will open.
6. Fill the details to create a new report.
7. Click on the Finish button after putting all the details. 
8. The report will be shown in edit mode.
9. Set a particular template for the report.
10. Run the report.

11. Open an Excel file with two worksheets. 

12. Place the names of the column that will be seen by the end user in the top row of the first sheet.

13. Keep the remaining worksheet blank where Ginesys Web Report will populate the data.

14. Go to the second worksheet. 

15. Click on Name Manager in the Formula tab to create the Template - utilize Named Ranges.
    
    
    
    

16. Add a new Named Range that matches the name of the first worksheet. Select the upper left and upper right boundaries of the desired range in the Refers To property. For Example. if you want all the data from columns A to J, select cells of Sheet1!$A$1:$J$1’.
    
    
    
    

    Important information regarding the data population range

    Note: When the report is executed, Ginesys Web Report will modify this range to include all the rows in those columns that include data. If the report had 100 rows, the range would be updated to Sheet1!$A$1:$J$100 same as previous example.

    
    

17. Go to Ginesys Web Report.
18. Open the previously created report. 
19. Click on the Export to Excel option.
20. Map the columns of the report with the already created worksheet.
21. Now go back to the Excel sheets.
22. Set the Chart or Pivot Table to use the Named Range as its Data Source.
    
    
    
    
23. Instead of using Name Ranges, each Chart or Pivot Table can be set as reference of first two rows of the first worksheet. (Ex. For a template with 5 columns, the reference would be ‘=Sheet1!$A$1:$E$2’.) When the data is populated by Ginesys Web Report, the rows are inserted in such a fashion, that references will automatically expand to incorporate each row of data.
24. All the report data will be populated in the first worksheet.
25. If you run the Pivot Table or Chart in the second sheet, those will be updated automatically as per exported data.
26. This process will be replicated for any further exported report data.