If one needs to add columns or modify the data in a dataset built from a source report, one can use the "Calculated Columns" tab for that dataset. Scoop brings the full power of the spreadsheet to the realm of preparing your data.

Looking below you can see the screen that does this. While it might look a bit complicated at first, it is actually quite straightforward. At the top, there is a sheetlet (a mini spreadsheet view that allows you to edit spreadsheet data directly within Scoop. That sheetlet window contains a template that allows you to specify new calculations for new columns based on values of columns that are in your source report. Below that there is a panel to preview your data to see if your new columns are calculating as you expect.

How Scoop Uses a Spreadsheet Template to Add New Calculated Columns

The process Scoop uses is actually quite simple, but powerful in that it allows you to leverage everything you know about spreadsheets. Scoop essentially loads your source report data row by row into the Values row of the spreadsheet template. It then calculates the values in the New Calculated Columns row and adds those new values to your dataset. It does this row by row until the entire dataset has been processed, and then that modified data table is loaded into your dataset. Let's explain each row in this template in detail.

• Source Data Columns section
  • Column Names: As you can imagine, this row lists all the column names from the columns in your source report. You should not change these values.
  • Include Column: This is a true/false field which when deselected or set to false excludes this column from your dataset. You can change these values depending on your needs
  • Values: This is the row that Scoop uses to step through the data in your source report. In the template, a set of sample data (generally the first row of your report) is loaded so that you can see what it looks like. These values you can play with if you like to test different values in your source data and how they might affect your new calculated columns.
• New Calculated Columns section
  • Column Names: This is the row where you set the names of your new columns. You can type in any name you like into the blank cells in this row. Scoop will stop examining this row at the first blank column header cell. So if you want to add two new columns, just fill the first two cells out with the names you want to use.
  • Values: This is the row where you can enter a formula to calculate the value of that new column. That formula can refer to anything in your spreadsheet, including cells in the Values row of the Source Data Columns section. In addition, you can also refer to cells anywhere else. So, in the example above, you can see that the formula takes a value in the source columns and does a VLOOKUP into a table on another sheet to determine the appropriate value. How does this work? Well, behind the sheetlet embedded into the Scoop user interface is a full fledged spreadsheet. You can open that spreadsheet up by clicking on the spreadsheet icon in the upper left. It will actually open up Google Sheets to the sheet in question and you are fully free to add sheets there and use the full set of functions there. When you are done editing in Google Sheets, refresh your Scoop screen and you should see those changes reflected in the Scoop UI.

To test your calculations you can click on the Preview Data drop down. Scoop will run through the most recent source report and show you the results. When you are satisfied you can click "Save Changes" and then reprocess your dataset to have it run those calculations across all the reports that have been loaded into this Scoop dataset. See Reprocessing Data for more detail.