70
©2002-2016, Ido Millet, ido@MilletSoftware.com
Page 190
Generating Excel Pivot Tables
Visual CUT can automatically generate an Excel Pivot Table based on an existing or newly
exported (Data Only) excel worksheet. This can be very valuable in cases where you want to
give users an easy and familiar way to Slice & Dice the data.
The command line argument structure is as follows:
…
"
XLS_Pivot_Table:Source_WorkBook>New_WorkBook>Tab>…
"
The parameters (after the ":") are separated by a ">" and are as follows:
1. Source_WorkBook: path & name of the source excel file (e.g. c:\temp\Sales.xls)
2. New_WorkBook: path & name of file to create (e.g. c:\temp\Sales_and_Pivot.xlsx)
if left blank, the Pivot table gets added as 1
st
tab in the existing WorkBook
if the style argument is not blank, the destination file should have .xlsx extension
and the machine should have Excel 2007 or later.
3. Tab: the sheet name in the source workbook where the data block is. Typically:
Sheet1
4. Range Name: [Optional]. If blank, data with column headers is assumed to start at A1.
5. Pivot/Tab Name: Pivot Table and New Worksheet Tab. To Target an existing Tab,
specify: PivotName||TabName||Cell_Address (for example, Pivot1||Sales||D20)
6. Row Elements: field names separated by "||". For example, "Country||City" would use
Country as level 1 row group and City as level 2 row group
7. Row Element Sort: a sort code for each row element specified above, separate by "||"
A for Ascending, D for Descending, N for No Sort
T# for TopN (for example T7 for Top 7), B# for BottonN (B12 for Bottom 12)
Last element (after "||") must specify the CAPTION name of the Column Controlling the
Sort (e.g., "Revenue"). For example: "T5||D||Revenue" would show Top 5 countries and
within that cities in Descending order based on "Revenue"
8. Column Elements: field names separated by "||". ("Employee||Product Name")
9. Column Element Sort: same logic as for Row Element Sort
10. Page Filter Elements: field names separated by "||". ("Product Class||Product Type")
11. Data Elements: separated by "||" and providing 4 sub-elements separated by "::"
1. Field name
–
the column header from the raw data block (e.g. "Value")
2. Caption - a user-friendly name. For example, "Revenue"
3. Summary Type
–
current options include: Sum, Count, Average, Max, Min, or Var
4. Format String
–
for example: $#,##0,K or 0.0%
For example: "Value::Revenue::Sum::$#,##0,K||Late::% Late::Average::0.0% "
specifies 2 pivot table metrics. The ‘Revenue’ metric is a sum
of the Value field, and is
formatted as thousands of dollars. The ‘% Late’ metric is an average of the
Late field
and is formatted as percent.
12. Show Grand Totals: "Both", "None", "Rows", or "Columns"
13. Show Subtotals: "None", "Bottom ", or "Top"
14. Style: same style names as in Excel, but without spaces (e.g. "PivotStyleMedium23"
15. Show Bands: "Both", "None", "Rows", or "Columns"
16. Show Blank Rows: "Yes" to insert a blank line after each group level 1 row.
17. Hide Data Tab: "No", "Yes" (allow unhide), "YES" (no GUI option to unhide).
18. Hide Field List: Yes hides the field list on the right side of the screen.
19. Data Orientation:
(optional)
"Rows" (Default) or "Columns" (to show metrics side-by-side)
20. Options
(optional):
various options separated by ‘||’ (e.g. Report_Lay
out=Tabular)