Automating Reporting

From Q
Jump to: navigation, search
Related Online Training modules
Exporting variables to Excel
Exporting Charts and Tables to Office
Default Export Charts Office 2007/2010
Creating PDFs
Updating Embedded PowerPoint Charts in a Tracking Study
Generally it is best to access online training from within Q by selecting Help > Online Training


Q has a variety of tools designed to assist in the automation of reporting.

Updating reports by changing data files

Once a Q project has been set up you can replace the underlying data and all tables and data will update (see Updating Projects with New or Revised Data). Where this involves tracking studies, you can set up the project so that the latest results are appended to any tables and charts (see Tracking Studies (Longitudinal Data Analysis).

Automatic Reporting in PowerPoint, Word, Excel, PDF, Online and by Printing

A Report created in Q can be automatically exported to Microsoft Office programs, as a PDF, online and for printing. Q can be setup to control aspects of the appearance in each of these formats (e.g., chart types, footers, headers, etc.).

Updating reports in PowerPoint, Excel and online

Reports that are exported to PowerPoint and Excel, or, as online dashboards, can be automatically updated when new data is imported (see Automatic Updating of PowerPoint and Excel Documents and Dashboards (Online Reporting)).

Showing filters and dates in headings

Filters and dates can be automatically shown in headings that are exported to other programs. See Automatically Including Weights, Filters and Dates in Headings.

Using Q to Update Excel Scorecards

An Excel Scorecard is an Excel Workbook that is used for reporting (i.e., given to a client). The following sub-sections contain tips relating to the optimal way of working with Q to update Excel Scorecards.

Generic filters

Consider the situation where you need to construct two scorecards, one containing data on men and another for data on women. The obvious way to do this in Q is to create two filters and change these filters to update the data. However, when constructing a scorecard it is useful to have a single filter and update the data by modifying this filter. For example, a filter could be created called Gender and, when the expression used to define this filter is changed, all of the tables will automatically update.

Single sheet export with linked cells

By default, Q exports to Excel with a separate sheet for every table. However, if when exporting you click the Advanced button (which is shown a few dialog boxes through the exporting process) and select Export all table data to one sheet, which will cause all the tables to be exported to a single sheet. You then use Excel formulas to link all of your scorecard data back to this sheet, which means that all you need to do to update your scorecard is to re-export the data from Q and the copy and paste it into this single sheet (or, alternatively, use Q's features for automatically updating an Excel worksheet).

Where changes to a study change the number of rows or columns in tables some care needs to be taken with this approach.

A disadvantage of this approach is that you will lose any formatting from Q (e.g., color-coding of significance levels). This can be remedied using Visual Basic programming within Excel.

Edit things in Q, not Excel

Often there are small mistakes in Q labels and it is tempting to edit these directly in Excel. For example, the label for Coke may be Cooke. However, this often causes problems later, as the data often gets re-exported at a later stage, causing either the correct label to be over-written by the incorrect label again, or, much worse, the correct label to end up next to the wrong data entirely.

See also

How to Add a Return Character to a Heading