Another powerful method for summarizing and analyzing data is data outlining. This is an interactive version of traditional reporting tools that calculate subtotals and other summary calculations. Instead of creating a static report for printing, Panorama allows the creation of an interactive outline structure that can be collapsed to show high level summaries or selectively expanded to reveal specific details. To demonstrate data outlines I’ll use the lineitems table in my sample hobbyshop application. This table is a companion to the orders table used in the crosstab example. This table has multiple records for each invoice — one record for each line item. (The orders and lineitems tables are linked by a has many/belongs to association, see Creating Associations and Associations for more information.)
Before I start creating the data outline I’m going to start Panorama’s built in recorder. This will record the steps I take so that they can be re-used later (more on this in a moment).
I’m going to create a three level data outline organized by Month, by Railroad and by Item. I start by clicking on the Date field and choosing Group Up from the Sort menu. Panorama responds by asking me what calendar period I want to use for summarizing by date — I’ll pick by Month.
group by month
When I press the Group button the local database is sorted in ascending date order. In addition nine special new records are created — one for each month and one for the grand total. These special records are called summary records, and they have some special properties as you’ll see in a moment. To make these records stand out visually they are displayed in varying shades of light blue, with bold text. The screenshot below shows the summary record for August 2008, and the grand total summary record.
Now I click on the Railroad field and choose Group Up from the Sort menu again. This sorts the local records in order by railroad within each month, and adds 120 more summary records (one for each railroad within each month). By the way, summary records only appear in the local RAM based copy of the data — they are never uploaded to Rails.
Now I’ll click on the Item field and choose Group Up again, sorting the items within each railroad and adding 758 more summary records (you can see two of these new summary records below — one for WP 50' Hi Roof Box and one for WP Covered Gondola).
My data outline is almost complete. To complete it, I click on the Total column and choose Total from the Math menu. Panorama scans the database and calculates the proper subtotals for all of the 878 summary records I have created:
Any reporting program can create subtotals. Panorama goes a step further and makes the subtotals interactive as an outline. For example, looking at the screenshot above it is hard to see the forest for the trees. For now I'd like to see just the monthly totals and ignore all of the lower level detail. To do that I choose Outline Level from the Sort menu.
This dialog allows me to collapse (or expand) the outline structure I've just created to show more or less detail. To see just the monthly totals I press the 3 button, for Date (by month). All of the underlying detail is collapsed, leaving only the monthly subtotals (and grand total) visible.
I’m going to show you some more interactive tricks with the data outline in a moment, but before I do I want to finish up the recording I started earlier. To do that I press the Record Procedure tool again.
Panorama responds by prompting me for a name for the new recording (as you can see, recordings are actually called procedures).
Once I’ve saved a procedure I can replay the steps in that procedure at any time by choosing it from the Action menu. Creating this data outline manually took 5 menu commands, but now that I have recorded the steps I can recreate this outline at any time with a single mouse click.
When Panorama saves a procedure, it is actually saved as a series of text commands in Panorama’s internal scripting language. You can use the View menu to see and edit procedures you have recorded. You can also create new procedures from scratch, without recording. See Programming for more information.
Expanding and Collapsing the Outline
Ok, enough about procedures, let’s get back to our regularly scheduled programming. When we left off we had a data outline that was collapsed to show only the monthly summaries. Now I’d like to see some detail. I notice that there was a big drop in sales in July, I wonder why? To find out more I click anywhere in the July summary line and click on the Expand tool.
While the other months remain collapsed, July is now expanded to show the next level of detail — in this case the subtotals for each railroad with the month.
By clicking on Union Pacific and using the Expand tool again, I can reveal the individual Union Pacific related items sold in July.
I can continue this expansion process until I reach the actual raw data. Panorama gives you amazing power to see overall trends, then to zoom in to examine the detail behind any trends or unusual patterns you see.
I can use the Collapse tool to collapse individual summary records, or the Outline Level dialog (in the Sort menu) to choose an overall level of visible data.
In this case I’ve picked level 2, so all of the monthly subtotals and the railroad subtotals are visible. All lower levels of detail are hidden.
Ranking using Summary Data
When the outline is collapsed I can use the Sort or Sort Down commands to re-arrange the data by summary values. This makes it easy to answer questions like "Who were our top salespeople each month?", "What products sold best last quarter (and previous quarters)?", or "Which offices handled the most support calls each year?" I’d like to find out what the most popular railroads are at my hobby shop. To find out, I click anywhere in the Total field and choose Sort Down from the Sort menu. Within each month the summary records are now ranked in order from most to least popular for each month. As you can see the Union Pacific is usually the most popular, followed by the Southern Pacific, Santa Fe and Pennsylvania.
By the way, when the summaries were sorted the underlying detail went along for the ride, so you can still use the Expand tool to see the underlying detail associated with any summary.
Removing Summaries when Done
When I’m done with the summaries I simply choose Remove Summaries from the Sort menu. This allows me to specify which summaries to remove — usually you’ll just want to remove them all, leaving only the original data records.
Another way to get rid of summaries is to re-download from the Rails table. This wipes out any summary records without having to explicitly delete them. Either way, you’re ready to go forward, either editing the data or creating a new outline with different organization.
Continue the Tour —> Graphic Layout