Skip to content

Results dashboard

The Autory results template is an Excel-based dashboard on which to build automated reports on your results.

Queries setup sheet

All external data queries are set up in this sheet. Each query is a row in the queries Excel table. Power Query is used to retrieve the data and convenience functions have been written to import CSV, Autory JSON results, Workbook tables and to combine different query tables. For customised requirements, a raw Power Query string can also be used to import any data source.

Table columns

QueryName
The name of the query
Update
Whether to update the query when pressing the Update/ add queries button
Load to workbook
Whether to load the query to an Excel sheet as a table
Use in pivot table
Whether to add the query to the workbook connections, which makes it available for use in pivot table reports
Source
Choose the source for the data to this query
Source Args

 

  • ImportCsv: Enter the Path of the CSV file, e.g. .\Data\I17_Summary.h3.csv
  • ImportJson: Enter the path of the Autory JSON file, e.g. .\Data\I17_Summary.h3.json
  • WorkbookTable: Enter the name of the workbook table or named range, e.g. MyManualData
  • Combine: Comma separate the queries to combine, e.g. I17_Summary_h3_1,I17_Summary_h3_2
  • Raw: Enter a raw Power Query string (copy and paste from advanced editor) e.g. let ... in ...
  • Tip: Use the Power Query editor to build your required step, and copy the syntax from the formula-bar to use in the config sheet.
Additional Steps

 

  • Not applicable for Raw query
  • Additional query steps to append to the Source Args. Syntax:
    • Table.{func}(__Prev_Step__,{func_args})
    • __Prev_Step__ is a key-word to use to which will automatically default to the previous step in the Power Query.
  • Tip: Use the Power Query editor to build your required step, and copy the syntax from the formula-bar to use in the config sheet.

Pivot tables configuration

Query name

  • Name of the query to link to the pivot table
  • In some Excel versions, the table needs to be linked to the data model in order to work with a pivot table

Config pivot columns

  • The columns in the config table that indicates which lines to include in a pivot table
    • For example, if All,VFA,GM is selected, the config table must have columns called All, VFA and GM respectively with indicators (TRUE/FALSE, 0/1) to indicate whether the row is applicable for that column
  • If blank, all the rows will be used to construct a pivot table

Global slicers

Fields should be comma-separated

  • Slicers that are defined globally to all pivot tables applied in the particular configuration sheet
  • For example, if more than one pivot table is created from this config these slicers will apply to all those pivot tables
  • The name will include (global) to indicate that the slicer applies to more than one pivot table

Value formatting

  • Formatting to apply to the values section of the pivot table

SheetPreFix

  • The prefix given to a sheet name to the Config pivot columns

Graph

  • Graph to apply to the pivot table output
  • Works best if _Value_ field is defined in the column fields

LocalSlicers

Fields should be comma-separated

  • Slicers that are applied locally to the pivot created (doesn't affect other pivots)

Filters

Fields should be comma-separated

  • Filter fields to include in the pivot table

ColumnFields

  • Column fields to include in the pivot tables
  • Use _Values_ to include the Values field in the columns (recommended for when graphs are included)

RowFields

  • Row fields to include in the pivot tables
  • Use _Values_ to include the Values field in the rows

Name

Name of the pivot table field. If _Values_ is a RowField:

  • the formatting of the Name column will be mimicked in the output:
    • Font size
    • Bold status
    • Indent level (maximum 7 indentations)
      • Automatic grouping applied according to indentations
    • Fill color

Field

Variable name to map to the pivot table field

  • Calculated field can be created by specifying a formula. For example:
    • NewField=Var1+Var2-Var3/2
    • Only +-/* supported