Reports from Scan/US

There are several ways to get a useful report from Scan/US. We will go through each one of these, and show you how to do it. Here is what we will be covering:

• Quicklook report: Click on an object, and get the report out through Quicklook, using the buttons at the bottom of Quicklook.
• Profile report: Select an object (or, in Object Manager, more than one), and choose "Profile Report" from the Reports menu.
• Site Express: From the Reports menu, choose "Site Express..", and enter your address, selected object (or objects), specify the number and size of rings or drivetimes, and choose the pre-formatted reports.
• Export data: From the Data menu, choose "Export Data..", and choose the precise set of demographic variables to export to an Excel workbook or other format data file. Save a named set of export variables to re-use later. "Export Data" files can, when exported to an Excel worksheet, pick up format from an Excel template. This is an option on export. Thus, people may think of these exported files as reports, since anything can be put into the template: special formatting, charts, graphs which depend on a subset of exported data.

The first export gives you an option to generate an Excel Worksheet template, as a "starter" sheet for this export-via-template operation. * Thematic maps: Create thematic maps from the Data Menu., by choosing "Classify by Value..." Print maps from the Map menu, by choosing "Print..." or "Export image...". You need to know about printing or exporting images, because at the moment, there are no combined map/demographic pre-formatted reports.

These are actually not the only kinds of reports you can do in Scan/US -- these are just the ones which focus on getting demographic data out of a single object, or comparing to a single benchmark. Comparison reports, benchmark reports, distance reports, and value-based thematic maps are covered in another overview document.

Let's start with the first on the list above: Quicklook report.

---

Quicklook report

When you have selected an object, select "Quicklook.." from the Data menu, and export to Excel, or print to PDF using the buttons at the bottom of the dialog. Change the databases you want to export from, using the "active datalist" dropdown. Expand Quicklook to include benchmark figures in your export.

Quicklook operates on the currently-selected object. Click in the map to select the object. This is whatever object for which you want data, such as a ZIP code or 10-minute drivetime contour you have created. Then, from the Scan/US Data menu, select "Quicklook..", shown below at the bottom of the Data menu. (Quicklook actually has already been activated in the example below.)

Once you are looking at the demographic variables which interest you, you can print or export them, using two controls located on Quicklook's bottom panel.

Click the "Export Quicklook" button (shown above) to export the Quicklook values to an Excel workbook. After you press the button, a little dialog will come up with a blue-underlined "link" to where the workbook has been saved. Clicking on the link will let you look at the Excel workbook.

If you press the little 'printer' button, the same sheet you exported will, instead, be printed to the active printer. The little "triangle-menu" in between the printer button and the export button (red circle above) can be used to change the active printer. Above, it is shown exporting to "PrimoPDF", a free PDF-output printer that can be downloaded from the internet. But, you can also print to a real, physical printer and get your output on a sheet of paper.

Quicklook export/print only works on one datalist at a time. To change to a different datalist, select a different one from the Active datalist dropdown, shown above underneath the Scan/US menu bar.

If you don't want to export the whole list, check off just some variables, and collapse Quicklook by pressing the button in the lower left of the bottom panel.

It's worth mentioning that when Quicklook is "collapsed" to show fewer than 10 variables, you can export those variables, along with the map-outline of the current object, directly over to Google Earth. You do this by right-clicking on the object in the map itself, and choosing "Locate in Google Earth..".

Quicklook has the capability to show the values of the currently selected area compared with the values for the United States (or any other benchmark you save -- the US Benchmarks are "pre-saved" for you.) Click the "Show benchmarks" arrow-button (above left) to expand quicklook. Then, when you export the data, it will show Benchmark values next to current values, as below:

Notice the "index" column, above right.

The Index column shows the "index", or percentage scaled to 1.0, that the value represents, as a proportion of the benchmark. For example, the value of 2011 Average employees per establishment in the table above, 10.26 for California, is about nine-tenths of the national average of 11.34 employees per establishment. The value in the index column, 0.905, represents this. The index is most valuable for statistical variables like average, median, or per capita, or for count variables where the benchmark areas are expected to be about the same size, or if you are interested in a proportionand don't want to do the division yourself.

---

Profile report

You can do a profile report for the selected object. This is an extremely clear, professional looking report, suitable for presentation purposes. You can do it for the selected object (as we will do here), but you will see a variety of other options too.

First, select the object for the report. If an object is still selected from the Quicklook report example, you can use that one.

From the Scan/US Reports menu, chose "Print profile reports..", and the "Specify objects" page will come up. Can I put in a good word here for the on-screen instructions? They are short, and always clear. Take a moment to read the one-line intro, and you can save yourself a lot of time.

Look at "Total objects selected," above, to make sure it matches how many objects you want reports for. (Here, it is one (1) object).

Anothing point to be aware of: it is here that you will choose between enumerating profile reports, one for every object you have selected, or summarizing all the objects into just ONE profile report. To summarize, click next to "Groups summarized:" it will say "Total groups selected" instead of "Total objects selected."

Then click "Next," and the page on which you select the reports will appear:

Be aware of the number of Objects, Reports, and Pages shown above. It should match what you think you are getting. If not, you may have specified "objects in checked groups" instead of "groups summarized" on the preceding page. You can go back and change it before outputting a hundred reports when you wanted just one.

A very cool little button is circled (in red), at lower right above. When you have a report highlighted, click this button -- it is a magnifying glass showing a 'plus' -- to show a preview of a sample report. If you have not highlighted a report, it will say "sample report not available." Otherwise, it will show a picture of the report.

You will be able to edit the report title, too, by editing the text to the right of "Title," above. Here the subtitle "The great state of California" will show up later on in the report.

Two checkboxes give you control of output options. You can print the report, or save it to a file, or both. Here we are ONLY saving to an Excel file.

When you press the "Print" button, and are saving to a file, the following "Reports Output" box will come up:

Clicking on the blue text above will open the folder where the report was saved.

If you need to return to where the report was saved, another choice on the Reports menu, "Report outputs folder ..." will take you back there. When you open the report, and print it, it will look like this:

You can bring the saved report up in Excel, re-edit the subtitle if desired, and then print directly from Excel.

---

Site Express

To choose a three-ring report (or multiple-ring or multiple drivetime report) based on an address, a selected point (or layer) or a latitude and longitude, start Site Express.

We are only going to show Site Express with an Address in this short orientation to different kinds of reports. Site Express can also be set to Launch on startup, which means it will always start when you start Scan/US.

You do not need to have selected an object before starting Site Express. But ... if you DO want to do a report on the selected object, you can choose the "Selection" tab (shown below between "Address" and "Lat/Long") after you have launched Site Express.

The panel above shows Site Express after an address has been entered and the "Find" button was pressed. The little blue pin in the middle of the target shows how good a job it thinks it did in finding the location. If the pin is not in the middle of the target, then the location may not be quite correct on the ground.

At this point, it's worth noting that you can enter MORE addresses, and the "Total sites" readout in the upper right of the panel will show the number. You can look at the list and change the naming, or delete them, by pressing the "View Sites..." button. We are not going to show a picture of that here -- it is pretty obvious how it works, when you need it. The main point here is that you are not limited to doing reports one address at a time.

Another thing to be aware of before pressing "Next", is the "Current Settings" box at the bottom of the panel. It shows the settings from the last time Site Express was run, and you can use them, immediately, on this run of Site Express by pressing "Finish." Here it shows a ten-minute drivetime, the layer where the new areas will be saved, and the number of reports -- here it is two reports, one of which is named: "2011 Demographics in Brief."

Since we are going to do rings, not drivetimes, we will press "Next."

The panel above shows Site Express after 1-3-5 has been entered into the ring-area tab. You can see that the "Current Settings" at the bottom has changed to show a 1-3-5 mile ring area.

Finally, you can choose your reports and click "Finish".

Here again (before you click 'Finish') you have the option to either print, or save the file, or both. The other checkbox has to do with the creation of study areas. If you are doing less than 6 sites, you can have Scan/US create a new study area for each site. If you are doing, for instance, 100 sites, it will not create 100 new study areas, because that would be cluttering up your system with study areas. The number of pages will be shown, and if you are printing a huge number of pages, it will split your saved output file up into files, so that it is a little more manageable.

Finally, if you have asked for study areas to be created, where are they? They are in new windows, and can be found at the bottom of the Scan/US Window menu, so you can select them and go look at the study area, and print the map or export the image. In fact, this study area is used in the last example, on map output: print map or export image.

But first, let's take a look at another kind of a "report", Export data.

---

Export data

"Export Data" reports may be unformatted, or may pick up format from a previously prepared Excel Template worksheet (XLTX). Because of the ability to choose a set of pre-selected data variables, we discuss this form of export with other types of report. In fact, they are a list of data variables, by object, exported to an Excel workbook or other type of file.

To get started, choose "Export data.." from the Scan/US data menu. The first page in the "Export data" series of panels will appear. For this example, we have used "Copy objects" from the Objects menu to make a layer of 2-mile rings around the 16 locations of Apple stores in Texas. The ring layer is named "Texas Apples", but as you can see below, you can also choose to export for "Object selected in the active map."

'Roster' is the term Scan/US uses for "list of all objects on a layer."

When selecting ring or drivetime objects for data export, you can filter your output using checkboxes in the lower-left panel (shown above).

Ring area site objects (area totals) are checked, here, as the object to export. Ring areas can be more complicated than the simple two-mile rings we have chosen for this example. You can also have concentric rings, and ring sectors (subareas), for which the checkboxes are greyed out in the example above. This is because there are no sectors in this example. The options for drivetime are also grey.

If you had concentric rings, the checkbox for "Ring areas (cumulative)," which is unchecked above, would show the progressively-larger totals for each ring radius: the one-mile, the two-mile, and the three-mile.

When you click 'Next', you progress to the next screen (shown below) where you specify the data variables: notice that there are 20 data items for each of 17 records selected for export, since during the course of this example, Apple has built another store in Texas.

When you choose the 'name and save current selection' button (red rectangle below), you can name the set of variables to use again with a different set of objects.

On the panel shown above, by scrolling up and down you can select any of the data items installed on your Scan/US system. The small "plus-sign" buttons in the list expand arrays of variables. Households by Income, shown in the example above, is such a list, with 16 items showing the number of households in each income range.

The small triangle buttons next to the "pound-sign" ('#') indicators expand to let you export percentage values ('%') in addition to the count value.

Choosing the 'name and save current selection' button brings up the following screen:

Notice the checked box for "Create a starter template file", above.

What is this option for? This creates an Excel template sample file.

"Export Data" files can "open with" a formatted a Excel template, and this checkbox creates an Excel template in the "my documents/ScanUS/DEX Library" folder. This template file will have the same name as the saved selection, with an "xltx" filename extension. The template can contain anything can be put into a template: special formatting, or even additional worksheets containing charts, graphs which depend on the exported data. The data will show up on an "Inputs" worksheet, and the rest of the template can make use of formulas which you will write, to display it however you choose.

The dropdown at the bottom governs whether data in the sheet will be exported with objects in rows (suitable when there are many objects), or in columns (more suitable when only a few objects are exported)

Finally, click "Next" to get to the final page of Export Data:

On this page, click "Export" to export the list. The blue-underlined link in the placemark above will appear after you have done the export. It is a link you can click, to bring up your exported file directly. In order to merge your export with a previously-saved Excel template, check the checkbox next to "Export data under template control" (small green rectangle shown above).

Before you press the "Export" button, give the output file a good name (Red rectangle shown above). By default, the filename is "DExport.xlsx", and it is up to you to give it a better name.

The exported file will always have the key for each object, and any other data items you picked. If you need to include the name of each object in your exported data, you may find it in the "roster" datalist, which is always at the top of the list of datalists, or in the "Export Object Labels" datalist, which may show the exported object name in various other useful formats.

When there are group assignments on a layer, another datalist will appear: the list of Group Assignments on the layer. You should be aware of this as an opportunity for export. If you have created a grouping using "Classify by Value.." on the Data menu (or a grouping created by any other means), you will be able to export the data category assignment (i.e. the group) created by that grouping, for each object for which you exporting data. Thus, the category assignment for each object, along with any other demographic data you may select, can be exported.

Export under Template Control

Let's take a more detailed look at Export under template control. There are three general steps:

• First, make a starter template workbook. We will look at the starter template produced by clicking the checkbox shown earlier "Create a starter template file".
  
• Next, fill in the template's blank sheet. We will show you how to fill in and refer to the variables specified in that file, in your "Sheet 1" of the export template.
  
• In "Export data..", when you check the templated export checkbox, these references on Sheet1 will be filled in by the data.

Starter Template

The standard template has four sheets: "Sheet1", "Dataitems", "InputR", and "Schema". Sheet1 is empty until you fill it out.

The first sheet shown below is the "Dataitems" worksheet. Notice the cell references stored underneath "DataRange", in Cells A2 through A21, the first of these being "InputR!A2:A2"

This reference specifies the sheet (InputR) and Cell Range (A2) of the first dataitem.

Since this specification is already present in your "Dataitems" sheet, you have the option of referring to it with the Excel =INDIRECT() statement, rather than doing the cell-by-cell work of putting in formulas to refer to each cell in the "InputR" sheet. We will show a formula to do that, when we get to "how do you fill out sheet1".

The Input items sheet, known as "InputR" because it is organized Row-wise, is shown below. The column headers in the InputR sheet, below, match the "ColLabel" entries from the Dataitemsheet, above.

The next row of "InputR" has the corresponding data items. Notice that the 2017 population figure in R2C2 is 328,556,267. This number is a "sample number" to help you format your sheet: it is the US population number for 2017.

Each number in the sample template is the entire-US value, so that you will be able to lay out your Sheet 1 spreadsheet, and refer to real numbers which are the maximum numbers you are likely to get for any smaller geography.

Finally we have the "Schema" Sheet, shown below. The schema sheet has the number of data items being exported, and the number of objects, in case you want to write a VB Macro to deal programatically with exported data. We will not be using the Schema sheet in this example, but that is what it is there for.

Now, let us proceed to fill in Sheet 1.

Filling out Sheet 1

First, find the template workbook in your "Documents/ScanUS/ExportTemplates" folder, and open it in Excel. It will have the same name as the selection of variables you made.

When you save this workbook, 1) First, specify it as an Excel template (bottom of dialog below) and then 2) save it into the folder it came from: "Documents/ScanUS/Export Templates". Excel likes to save this type of file someplace else, so if you perform the specification-and-selection in this order, you can save yourself some annoyance.

Once you have opened the template, fill it in with whatever data references, labels, data series ranges, and charts, that you have in mind. The Sheet1 you are looking for is completely blank when you begin. The example below has already been filled in with an example idea: no doubt your spreadsheet will look much better than this!

After you have decided what to put in your template, how do you establish a connection to the exported data? Notice the Population figure in A7, above, and the formula shown in the formula bar: "=INDIRECT(Dataitems!R6C1)" This refers to the cell in the "Dataitems" sheet, which holds the cell reference to the data, for that data item, for the exported object.

You can have Excel help you out: type the first part of this formula "=INDIRECT(" and then switch to the "Dataitems" sheet and select the cell with the data item range reference. In this example, it's the one that says "InputR!A2:A2". Excel will fill in a relative cell reference (It looks a little different from this one). Then type a right-parenthesis, and Excel will complete the formula and fill in the expected number.

You can copy that relative reference down to all the rest of the cells in the column, making your job quite easy.

The picture of the template shown above also has a data series beneath the 1-2-3 of the chart. It uses a formula in each of the three cells to summarize the red, yellow, and green ranges from the cells in column 1. The chart relies on this three-cell data series. we don't explain these formulas here, but you can look for the sample workbook in the examples folder, and examine them yourself.

Using the Template

An Export template matches a saved selection by name, so the first step (after specifying the object to export) is to choose a saved selection. For this example, the number of records should be one (1))

Press the "check items from saved selection" button (shown below with a green arrow next to it), and choose the saved list of variables which matches your template.

Then, press the "Next" button

When you arrive at the 'Create data file' step of "Export data", select Excel Workbook (.xlsx) as your output type, and check the checkbox "Export data under template control" which appears below the output file choice. When no checkbox appears, there is no template for that datalist.

Notice the descriptive filename "SantoTrinero" given to this export of data for the town of Santo Trinero. Now is the moment to give your exported file a name you can remember. Do not just go with the default filename of "Dexport.xlsx", because you will only have to rename it later.

When you press the "Export" button, your data will merge with the template formulas you have created, and form a new workbook with your data and your pre-cooked graph.

Looking at the median and average family income numbers for Santo Trinero, you can see that it has a lower average and median family income than the national average, but by comparing the two charts you can see that the proportion of low-income households is smaller than the national distribution, and that there are also fewer wealthy households, compared to the number of middle-income households.

You should always click the blue button and look at the worksheet. It seems that occasionally the calculation of the "Sheet1" formulas does not occur, and you will want to look at that population figure to make sure it is not 328 million, or whatever the US population value is for the variable you have picked.

You must make sure it recalculates the workbook, not just the worksheet. The keyboard command for doing this in Excel in CTRL-alt-shift-F9 for the current workbook, and CTRL-alt-F9 to do it for all open workbooks. Just pressing F9 or Shift-F9 will not cause the calculation of Formulas on Sheet1 to be redone, because they depend on two other sheets in the workbook.

This exercise has shown how to use template export to export one (1) object, but the same principle could be applied to a grouping.

The help topic covering multiple-object reports will show how to do a data export of more than one object via a template.

---

Map output: Print map or Export image

This is very straightforward.

From the Map menu, you export the current map by choosing "Export image.." from the Map menu. Conversely, to print your map, choose "Print..". The main difference is that the "Print" option offers page templates, with title and captioning opportunities, plus the ability to include more than one map, fill in a legend, etc., while the "Export image.." option gives you just the map pretty much as it exists.

In "Export image..", your image output options are to Windows clipboard (after which you may paste into another application), or "Save in a file" -- a box will appear to prompt you for a filename.

You can make the image larger or smaller by editing the Width field, and increase the resolution -- at a cost of increased filesize -- by choosing different dots per inch (dpi) output. If you want to fiddle with your map with the Export box up, press the "Refresh" button to bring the map image up to date before exporting it.

---

The first thing on the "Print Map" box is the "Choose layout" dropdown, which gives you various paper sizes and orientations. We are just going to show one simple 11x 8.5 map in this example. You can explore the others on your own.

The green-outlined areas of the output page (above) are editable by you. Click on the rectangle, and an edit box will pop up. Shown above, the "Description" field at bottom is being edited to give a more detailed description of the idea you want the map to convey.

Notice that you can change the output printer at lower left, and that, like some other reports, there is an option to print the map, or save it to a file. When saved, the saved map goes to the same folder as saved reports, and can be found in the folder found at the bottom of the Reports menu: "Report outputs folder..."

The map title, and company name, shown at top outlined, can both be edited in this template.