How to Use TableWare

To open the TableWare dialogue in Excel 2007, you find the "Add-Ins" ribbon and click on the TableWare button, and the main dialogue should pop up over your spreadsheet.
This is the main TableWare dialogue:

To create a Pivot Table, or just do a query:

  1. Have a worksheet open
  2. Click on the cell that will become the upper-left cell of the QueryTable or PivotTable
  3. Be sure that you have selected the correct database from the radio buttons on the upper-left of the form. If you don't want one of those three databases, the LOGIN button beside them will allow an Oracle login to any City Oracle instance, with UserID, Password, and Instance name.
  4. Type your query into the black text box. Error checking is limited! It'll mostly just die on you, so you may want to check the query with another tool first.
  5. Hit PIVOT TABLE or SIMPLE QUERY, as your preference.

To Refresh your Table

Any pivot table or query table stores the SQL that created it. Right-clicking anywhere inside a table will drop down a menu that includes "Refresh", which will re-run the query and change all numbers if the source table has changed.

To Edit your Query

To change the SQL query itself: If it is still in the black text box, simply edit it and double-click on the text box space. A message box will ask if you confirm changing your query; a YES will change it and refresh the table.

If the query is NOT still in the text box, (say, you reopen the worksheet later and bring up TableWare again), you can fill the text box with the query stored inside the table definition by selecting any cell inside the table and double-clicking on the black text-box field when it is blank. That will fill the text-box with the query, which you can then edit and double-click again to replace the existing one.

For Queries You Can See In the Spreadsheet

1 - Query all in one cell

If the text box is blank when you hit the PIVOT TABLE button, TableWare will assume your SQL query is on the spreadsheet, on the cell you clicked on before hitting the button. In this case, that query is sent, and the Pivot Table appears two rows below it, instead. NB: To make sure the black text box is completely empty (not even stray blanks), hit CTRL-A and DEL to delete every character in it.

2 - Longer Query in a range of cells

If the text in the cell you clicked on does not start with the six letters "SELECT", TableWare will assume it is a range name. Then it will look for a range of cells by that name and use all of the text in those cells strung together:

However, you cannot just change the SQL in the one cell, or in a range of cells, and refresh your Pivot Table. Once the Pivot Table (or Query Table) is created, the SQL that created it becomes part of the definition and does not change automatically. These are only ways to DISPLAY the original SQL that created the Pivot Table in your spreadsheet.

To Refresh your Table After Changing Your Query in Spreadsheet Cells

If you create a table with a query in a spreadsheet cell (or named range of cells), the query in the table stays the same if you change the cells. To send any changes into the table, first edit the cell(s), then click inside the table and hit the green "SQL!" button. This will cause TableWare to look in the original cell full of SQL (or range-name of SQL cells) and use whatever is there now to refresh the table.

This technique is especially useful when combined with Excel text forumlas. The cell(s) containing the text of an SQL query can also be a formula like:

="SELECT * FROM WORK WHERE WORK_CATEGORY='D-REPMAIN' AND extract(year from work_finished)=" & H2

Creating a spreadsheet where you need only change the highlighted cell to change the query:

Of course, every time you change the year in cell H2, you must hit the green "SQL!" button on TableWare to update the pivot table with the changed SQL. If you need to give the spreadsheet to somebody who isn't likely to use TableWare, read the next section:

Creating a Query Spreadsheet That Does Not Need TableWare

The green button called "! Button" puts a blank green button on the cell right above the table, and thus just below the cell with the SQL (or SQL range-name) in it. This blank green button does what the "SQL!" button on TableWare does, only embedded in the spreadsheet. Every time it is clicked the query is refreshed from whatever the SQL currently is.

TableWare Buttons to Ease Managing your Pivot Table

These buttons only duplicate functions you can manage with the menus, but make it much easier than a lot of right-clicking or hunting through the ribbons.

The Clone PivotTable will create a new sheet, with a new PivotTable in the same position on that sheet as the selected PivotTable is on the original sheet. This second PivotTable shares the same PivotCache, containing all the data from the last query, and reports can be done with it on that data, while leaving your existing PivotTable untouched...for when you need multiple reports from one query. This one does not apply to simple queries.

The Copy Data button just copies the DATA in your existing PivotTable to another sheet - it lets you keep a snapshot of a database as it was when you made the query, useful when the database is changing with time. Note that a copied PivotTable sharing the cache will CHANGE if you re-query, so you can lose a report that was itself a "snapshot" of the data at a given time.

The Flip Chart button is only used when you have added a PivotChart to your pivot table. (When any cell in the pivot table is selected, hit the PivotTable Tools button above the ribbon to get the ribbon with "PivotChart" on it.) Which table field is the rows and which is the columns often produces a chart with exactly the wrong axes; this button just toggles "X" for "Y" in the axes and gets the chart right, and can toggle it back again when you want to look at the table.

The Sum/Ct button flips summarization between the sum of numbers and the count of different values. When you drag in a field, Excel has a maddening way of assuming you want a COUNT of those fields when you want a SUM (say, of money or length), and vice-versa. This toggles any field you click on between "COUNT" and "SUM".

The DEL button just above "Sum/Ct". It just deletes any PivotTable you click on first when you hit it. Saves the effort of selecting the area around the PivotTable (including the Page field if used) and going through Edit/Clear/Clear-All in the menus.

The Data button just above DEL selects the whole Pivot Data range - all the "inner" cells. This makes it quick & easy to then hit formatting keys like "$" and "%" to change the number format. If the that area is ALREADY selected, it sets the whole area to the "GENERAL" (Excel Default) number format that shows the whole number. (There is, oddly, no button for this in Excel itself). If the format is already "General", then it changes it to the "Number" format with one decimal place, usually all that is useful. Further clicks just switch between general "123.0734", and one-decimal "123.1" formats.

The Rounding Buttons are those just to the right of the database login (and just left of the big Pivot Table button). They say "Off","10","100","1K". These are used only when the data area is filled with a SUM rather than a count, say, the SUM of the LENGTH field in a pipe table to give the total length of pipe in each cell of the table. Frequently you get numbers like these:

To round the values off, first click on any of the data cells, then click on 10, 100 or 1K to round it to the nearest 10, 100 or 1000 metres. Click on OFF to turn off rounding.
Same table with a data cell selected and 10 clicked

The GIS button just does something handy for ESRI user in particular. Often, a pivot table report may pull out a group of assets worth looking at on a map. ESRI layers have a tab called "Definition Query" where you might put in a string like:
MSLINK IN (37, 99, 159, 222)

...only the list might be very long.

Once a pivot table shows a group of query rows of some interest - say that there are 55 mains that are CON material and have choked 1 time:

...then a double-click on Cell C8 with the "55" in it will jump to a new worksheet with all 55 records in it:

What the "GIS" button does, is if you click anywhere in column A of that sheet, and hit the GIS button, then TableWare will look at the column name at top, and the whole column and construct a string like this:

MSLINK in ( 17126, 4688, 7919, 4542, 7904, 17349, 23585, 17334, 17331, 7714, 7722, 7635, 17447, 7744, 7668, 4544, 7899, 7686, 4687, 17342, 7720, 17089, 7903, 17123, 7682, 7773, 7767, 4543, 17330, 23607, 7725, 7815, 7791, 61090, 7824, 17351, 7675, 7662, 7900, 17360, 17449, 7670, 7852, 17306, 7633, 17194, 19299, 7792, 7661, 7866, 7748, 7689, 7902, 23620, 7836)

...and it puts this string into the Windows "Paste Buffer". Just move your mouse over to the GIS program, the "Definition Query" tab, and Paste it. Your next refresh of the layer will show only the 55 mains that were of interest in your pivot table. NB:These "enumerated lists" in an SQL statement cannot exceed 1000 items. If you have more than 1000 things to highlight in the GIS, it would be better to put a specific query in the Definition Query tab.

Lastly, there are three buttons with no text at the bottom and right of the SQL query text box; they start off all Orange. Once you have a PivotTable and click within it anywhere, these buttons cycle the report through three report styles:

The button at the right of the SQL box does the same - except it changes the report to "% of Row" and "Running Total along Row" and then back.

The tiny button at lower right just switches back & forth between two reports: normal data and "% of whole table".

For each of these three buttons, they also change colour to notify you of the current state, with these mnemonics:

When columns or rows are being changed, the other button turns black; when it's the whole table percentage, both column and row buttons turn black. (If you change the data calcuation with Excel itself, the buttons do NOT sense this and change colour; but they will get back in synch as soon as they are used again.)

Common errors are (1) wrong database and (2) SQL code errors - check them in Benthic Golden32 or other SQL tool. And remember that the format "07-13-1958" for dates may work in one tool but not in another - that one won't work here! To be sure, always use "to_date('07-13-1958','DD-MM-YYYY') or equivalent. CURRENTLY, the Oracle servers are set to the format "19580713" ('YYYYMMDD'), but some IT guy can change this at any time!

This tool does not have the 26 error checks that a commercial product would have. If your SQL is wrong or similar problem, it will simply die with an error message. Just close the dialogue and use the SQL button to pop it up again.

The HELP button at upper right brings up the help web page. You are reading it now. Thank you!