In the previous article, we saw how to import an Excel spreadsheet into SmartReports so that it can be automatically populated with data from a PCensus study area.  This works well if the study area is configured exactly to match the original report (i.e. it has concentric 1, 3 and 5 mile radius circles), but it would be nice if the template would also work for other types of study area, for example one with an arbitrary number of circles or drive time areas.  This can be accomplished easily with the SmartReports “Repeat Section” feature.

• To begin, create a blank template and import the spreadsheet used in the previous article.
• Save the template as “Generalized Race Distribution.xltx“.

The generalized template will only require one row of numeric data to define the contents of the report.  This row will automatically be duplicated and populated for each “part” (ring, drive time isochron etc.) in the study area, so we will delete the rows for 3 mile and 5 mile rings.

• Position the cursor in cell A5 (the leftmost cell of the row to be repeated) and select Repeat Section from the SmartReport Designer ribbon.

SmartReports inserts markers for the first and last rows to be repeated.

• Change the End Row entry in the Document Actions pane to 7 so that the data row is entirely included.
• Change the Repeat Type entry to ForEachStudyPart by selecting from the pull-down list.

Now we must insert the data links to PCensus.

• Select the part description (“1 mile ring”, cell B6) and select CurrentStudyAreaPartDescription from the Quick Tags list.  You may wish to increase the width of the column or select a smaller font so that the ge.

• Now select Profile Values from the SmartReport Designer ribbon to populate the numeric fields ( see the previous article).

Note that this time we will leave the Study Part Index set to -1 so that the correct area will be set for each entry.

Unfortunately, we cannot use the graph in this example, because we do not know in advance how many series will be represented. See the post Multiple Trade Areas to see how we might get around this problem. In the meantime, delete the graph from the template.

Now we can create a multi-part study area in PCensus and Run the SmartReport.

This report has a row for each of the five drive-time isochrones in the study area.

You can download a copy of the template Generalized Race Distribution.

Users of PCensus often have a requirement to present information for a trade-area in an Excel spreadsheet using a standard layout. Consider the simple example of a tabulation with a linked graph.  The user needs to use this layout repetitively for many trade areas, and each time he must copy the cell contents manually from PCensus:

We can create a SmartReports template that exactly duplicates this layout, allowing PCensus to automatically populate the cells for any study area.

Copy the Sheet  into a Template

The first step is to create a new, blank SmartReport template, as described in the post Creating a SmartReport Template.  Save the file to RaceDistributions.xltx. Now open an example of the original manually-created spreadsheet (Original Report.xls) in the same Excel instance as the template. You can use “Arrange all” to view the two documents.

Right-click on the worksheet tab that contains the report, and select “Move or Copy…”

Make a copy of the sheet into RaceDistributions.xltx.

The layout of the report, including the graph linkages, is now part of the template.  However, the numeric values are all static, as copied from the original report, so we must replace them with dynamic fields that will be populated by SmartReports.

Inserting Dynamic Fields

The second row of the template contains the name of the PCensus Study Area used to create the report.  We can automate this name with the Quick Tag feature:

• Highlight the field in the spreadsheet.
• Select “Study Area Name” from the Quick Tag pull-down list.

The field is replaced by the tag <StudyArea.Name>, which will be replaced in the final report.

Now we can begin to replace the numeric values with dynamic entries.

• Highlight the Total Population in the 1 mile ring row of the report.
• Click the “Profile Values” button.

This dialog box contains a view of the current PCensus template.  Note that all of the numeric values shown are All-US “placeholders”.

• Select the Category that contains the required values (Demographic Quick Facts), and highlight the values to be inserted as shown.
• We need the values to be inserted in a row across the page, instead of in the column format in PCensus, so we must check the Switch columns/rows box.
• Click Paste Selected Items to insert the entries into the SmartReports template.
• Repeat for the 2 mile ring and 3 mile ring rows.

Assign the Radius Ranges

This simple example assumes that any study area used to populate the SmartReport will have three concentric circles of one, three and five miles radius. In the next post, we will see how this can be generalized to work for any study area.

For the purposes of SmartReports, each concentric circle is called a “study area part”, and is refered to by a zero-based index number.  The smallest (one mile) circle has index 0, the next (three mile) has index 1, and the largest (5 mile) has index 2. For each numeric cell in the template, we must specify the appropriate index by a setting in the Document Actions pane.

• Highlight the Total Population cell for 1 mile ring, and notice that the Study Part Index for this cell is initially set to -1.

An index of -1 specifies a default value that will be used according to context.

• Change the Study Part Index to 0 to specify the first (one mile) circle.  Note that this does not change the displayed “placeholder” value of 310,650,750.
• Now repeat for the other numeric cells in the report, using an index of 0 for all entries in the 1 mile ring row, 1 for the 3 mile ring and 2 for the 5 mile ring.

Generate a Report

The template is now ready for use.

• Save the file and run PCensus.
• Create a Study Area using 1, 3 and 5 mile circles.

• Click the SmartReports icon and create a report using our Race Distributions template.

All of the numeric fileds are populated with the appropriate values from the study area, and the template can now be used for any 1, 3, 5 mile study area.

You can download a copy of the Template described in this article.

What Next?

It may be that you would like to create this same report for other types of study areas, for example concentric drive time isochrones or for predefined areas like counties or ZIP codes.  See the next post for ways to generalize this template.

A SmartReport Template is is a special Excel template (.xltx) file that contains software extensions which provide linkages to PCensus and PCensus online.  A new SmartReport template must be created by opening an existing template,   which you can “Save As…” to create a new .xltx file containing your customizations as well as the PCensus linkages.

An empty template called ExcelDesigner.xltx is installed for this purpose as part of the PCensus version 10 software package.  Of course, if you already have customized templates (including stock templates provided by Tetrad) you can use these as a basis for your new SmartReport instead of ExcelDesigner.xltx.

Create an Empty Template

The easiest way to create a template is from the SmartReports dialog box in PCensus, which is displayed by clicking the icon in the PCensus toolbar.

Right-click on the Blank Report icon, and select New Report Template… to start Excel with a new copy of the empty ExcelDesigner.xltx SmartReport Template. It may take a few moments for Excel to fully open the template.

When a SmartReport Template is opened in Excel, you will see special controls dedicated to editing this type of template:

• The SmartReport Designer ribbon tab contains icons for configuring and editing the template.
• The Document Actions pane allows you to configure the contents of PCensus-linked cells and to edit the overall Report Properties.

Configuring the PCensus Linkage

The SmartReport template refers directly to information stored in the PCensus database, using the same symbolic names (like CY_POP for current year population) that are used in the PCensus template editor.  The various database products available (e.g. Nielsen, PopStats, AGS, Statistics Canada etc.) use unique variable-naming conventions, so it is essential that the SmartReport template is linked to the correct database.

The SmartReport Ribbon contains buttons for the selection of PCensus database and template.

Click these buttons to select from lists of currently-installed data products. The initial selections will default to those in the current PCensus project.

Note that selecting a database does not necessarily limit the use of the SmartReport template to that particular product.  For example, if you select a Nielsen 2011 vintage database, you will still be able to use the template when the 2012 data become available, provided that the variable names used in both versions are compatible.

The selected database is used as the source of all-USA “placeholder” values in the SmartReport template which will be replaced by study area values when a report is generated.

The selected PCensus template is used as a reference source of formulas used to calculate values displayed by PCensus (for example CY_AHI_HH/CY_HH for average household income).

PCensus Templates

Formulas pasted from the PCensus template become part of the SmartReport template, so the source template does not need to be attached when the SmartReport is generated.  You can even switch between PCensus templates while you are working on your SmartReport project, to get formulae from more than one template.

SmartReports does not enforce the matching of data types between the selected PCensus database and PCensus template.  This allows you to select a customized template containing your own formulae etc. even if it is normally linked to a different database type.  In this case, it is up to you to ensure that all variable names used are valid for the selected database.

Saving the Template

In this article, we have not discussed the process of adding meaningful content to the new SmartReport template; for now, we will just go ahead and save the empty template.

When we save the template for the first time using the standard Excel “Save” or “Save As…” functions, we see the “Save SmartReport Template” dialog box.

This dialog allows us to enter descriptive information about the template:

• In the Report Description box, type a short note describing the purpose of the template or similar information.  This will be displayed in a “tool-tip” in the PCensus SmartReports dialog.
• The DBType entry shows the internal type code of the linked PCensus database.  This is included for informational purposes only.
• The DBTYPEFilter entry determines which SmartReports will appear in the PCensus SmartReports dialog when a specified database is in use.  This will reduce clutter in the dialog if you have a large number of installed databases.  Enter an abbreviated version of the required dbtypes, separated by semicolons with no spaces.  For example, the string “CL11;CL10” will restrict use of the SmartReport to Nielsen databases for the years 2010 and 2011.
• If required, you can enter author and copyright information.

Click the Save button and enter a file name for the new template, for example “A New Template.xltx“. Do not use the name “ExcelDesigner.xltx“, as this would replace the default blank template.

Editing the Report Properties

If, after saving your new template, you wish to change some of the report properties such as the DBTypeFilter, you can open the template in Excel and select the Report Properties tab in the Document Actions pane.

The SmartReport Designer is an extension of Excel that allows your workbook to seamlessly import data elements from a PCensus project. The imported data populates designated cells in your spreadsheet, that can be used in any Excel operations, for example combined in formulae or displayed in graphs.

SmartReports are designed using an enhanced Excel template (xltx).  A number of “stock” templates are available for download at Tetrad’s web site, and Tetrad can help you develop new templates for your specific requirements.

If you have a working knowledge of Excel, you can use the SmartReports Designer (included with PCensus version 10) to design your own SmartReports.  The PCensus User Guide provides basic information on using SmartReports.

The purpose of this blog is to draw attention to techniques that can streamline the process of creating SmartReports, to share the accumulated experience of Tetrad’s designers and to provide “behind-the-scenes” knowledge of the SmartReports Designer.

PCensus user, Eswaran Selvarajah of Boston Studies Group has published a report titled, “CENSUS 2010:
How Changes in 12 Selected Cities and Towns in Metropolitan Boston Compare with State and National Trend”

The report makes heavy use of PCensus and US Census data to study changes in Boston and the surrounding municipalities.

You can view the full report here

Stay tuned for SF1 data release information for PCensus Desktop.

No correlation implied, but interesting nonetheless.

To kick off this announcement, we made a thematic map with counties in the continental states. Here is a map illustrating the percentage of single parent families.

% of Families with Single Parent (Mother or Father) and Children under 18

• South Dakota
• Oregon
• Nevada
• Indiana
• Illinois
• Washington

The states previously released were Alaska, Colorado, Connecticut, Nebraska, North Carolina, California, Delaware, Kansas, Pennsylvania, Wyoming, Alabama, Vermont, Oklahoma, New York, Montana, Minnesota, Arizona, Hawaii, Georgia, Kentucky, Maine, New Hampshire, Virgiina and Puerto Rico.

Access the data now from any iPhone, iPad, Android or Blackberry using Sitewise.

2010 Census Maps

The Census Bureau has released a dot density map using redistricting data to illustrate the locations of highest population density across all 50 states and Puerto Rico.  The New York Times has a number of thematic maps looking at everything from population change to vacant housing.  The Washington Post maps out trends with an interactive map of census variables that also includes 1990 and 2000 census numbers.  You can also create your own thematic maps with 2010 Census data using our PCensus Desktop software.  

»Watch a video on how to make your own dot density map.

In the News

• Mayors are lining up to protest Census counts that they say may be too low.
• States and Counties discover there has been significant growth in number of same-sex households. [ Virginia, Georgia , Maine , Tulare County , New York ]
• Minnesota, Ohio and Montana are going grey.