With data consolidation feature you can summarize data collected by another SpreadsheetWEB application into a single report.
Let’s take an excel workbook created by a company to collect sales forecast figures from all sales people annually. This workbook has fields to enter customer name, project name, estimated amount, probability and close date. Workbook calculates weighted amounts. However, assuming there are many sales people, handling this process using simple Excel spreadsheets is a very tedious process.
With SpreadsheetWEB this workbook is turned into a web based applications collecting user data in a backend database.
As each sales person logs into their SpreadsheetWEB account and enter their sales forecasts, corresponding data is stored in SpreadsheetWEB database. Each sales person can update their data any time by logging into their account. Data submitted by all sales people can be viewed by a manager as shown below while each sales person can only view own data.
Using Data Consolidation feature in SpreadsheetWEB Excel Addin, you can connect to SpreadsheetWEB server and bring saved data for each sales person into a workbook
Data Consolidation feature gives options to select individual records in the database or select a group of records and consolidate results. You can either use built-in query builder or if you are familiar with database programming insert your own custom queries.
For this sample case, we have to enter each cell to be consolidated for each individual record.
Select Add Data Consolidation from the SpreadsheetWEB addin, and the following window will open.
Enter a Name for the Consolidation and enter the Application ID. Application ID can be found in the address bar of the application and it is the part after "ApplicationId= "
After filling the required fields, select Connect, to connect to database of that Application that you want to consolidate in the existing file.
The following data fields will be gathered from the database. The consolidaiton wizard requires entering the cells which the consolidation will be made. It also requires entering consolidation rules.
Since our database contains sales data for each month, cell locations where the consolidated sales date will be inserted are selected. Consolidation rules are entered in the last column as follows:
After clicking to first field, the wizard will ask which cell to bring the consolidated values:
Select the cell from the opening field and hit OK.
Automatically, the name of that cell would be entered into the field.
Now, the consolidation rule will be selected as follows. Clicking on the second field which will bring up the following window to select the data from the database.
In this window, SalesPerson option would be selected as = and then the appropriate field must be selected. By doing so, the system will bring the data to the previously selected cell corresponding to the Sales Person's name.
The field would require the cell to match which Sales Person to be queried from the database. In this case, "Jane Doe" cell would be selected. And then "Finish" will be selected. The following window will be on screen:
For the remaining months, they should be done in the same manner simply by enabling the checkbox for each month and select the cell to which data will be populated. For the second column (i.e. conditions), after checking all, clicking to the text box of the first filled. Then, click on the button "Update All Fields"which will copy this rule to all other months.
Following is how the consolidation window will look like:
After all, pressing "Save" button will save the consolidation data. For this simple case there are 6 more Sales People to have their data to be consolidated in. Same procedure would be repeated for all.
Results can be inserted into select cells in your workbook. When you press "Refresh Consolidation" button on SpreadsheetWEB addin menu, most recent data will be downloaded from the database and consolidated data will be placed in selected cells.
The file, then, can be converted to a web report using SpreadsheetWEB, and can be used for real-time reporting accessible by world wide web.
Watch a video on data consolidation feature