Skip to main content

Working with Pivot Tables

This page discusses how to work with Business Intelligence pivot tables.

Also see Accessing the BI Samples.

Opening a Saved Pivot Table

To open a saved pivot table:

  1. Click Open. Or click Menu > Open.

    The system displays a dialog box, which shows names of folders and of pivot tables that are not in any folders.

  2. Optionally click the name of a folder.

    The dialog box then shows the contents of that folder.

    Repeat as necessary if your folders are nested.

  3. Click the name of the pivot table you want to open.

  4. Click Go.

The system closes the dialog box and then displays the selected pivot table in the Analyzer.

In most cases, the underlying query is executed automatically. If it is not, click the Refresh button . See the next topic.

Disabling and Enabling Auto-Execution

By default, when you make any change to the pivot table, the Analyzer re-executes the query and redisplays the results. You can disable this auto-execution. To do so, clear the Auto-execute option on the toolbar. Later, to enable auto-execution, select the Auto-execute option.

Note:

If you disable auto-execution and save the pivot table, that change is saved as part of the pivot table definition and affects the behavior of any dashboards that use the pivot table. Therefore, you should be careful to make this change and save it only if you do not want the pivot table to run automatically in dashboards, though actions may still be invoked from the Analyzer with the current query described by the pivotTable object even if said pivotTable has not been executed. Be sure to include a refresh control in any widget that displays this pivot table. For more information, refer to this section on manual query editing.

Canceling a Running Query

While the Analyzer is executing a query, you can cancel it. To do so, click the Cancel Query button .

Exporting Data to Microsoft Excel or CSV file

From the Analyzer, you can export the data in a pivot table to Microsoft Excel or to a CSV file by selecting the Export current results button Rectangle with arrow pointing outward representing data export in the Pivot Builder area. On a dashboard, you can also export data to Microsoft Excel from a pivot table widget, but not from other types of widgets. To do so, select the Export to Excel button Rectangle with arrow pointing outward representing data export in the widget.

In the Analyzer, when you export data to CSV by selecting the CSV option from the drop-down menu, the system generates a file that includes the contents of the pivot table, including the column and row labels. The cell values for a given row are separated by comma characters (,), and rows are separated by line breaks. When you select the CSV with Heading option from the drop-down menu, the file includes the same contents as a file exported using the CSV option, with an additional line at the beginning. This line is a heading that identifies the data:

  • If the data is derived from a saved pivot table, the heading contains the relative path to the pivot table and the pivot table’s name (for example: "Patient Pivots/Avg Score by Age")

  • If the data is not derived from a saved pivot table, the heading provides the name of the source cube or subject area

When you export data to Microsoft Excel, the system generates an Excel file that contains all the data currently displayed in the widget. The file has the name %DeepSee.UI.MDXExcel.zen.xls, %DeepSee.UI.MDXExcel.zen-1.xls, or similar. Your browser then does one of the following, depending on your operating system, the configured file types on your machine, your browser, and your browser settings:

  • Opens the file with Excel (or other selected program).

    You specify the program to use by setting a browser option. For example, on Firefox, you use Tools > Options > Applications. This option also enables you to specify whether the browser should open the file with this program or prompt you to save it to the hard drive.

    Note:

    The exported file, while created with an .xls extension, is formatted as an HTML spreadsheet. Excel will prompt you with a warning about the format and extension not matching, but this does not indicate an unusable or unsafe file. When prompted to open the file anyway, click Yes.

  • Prompts you to open the file with Excel (or other selected program, as described in the previous bullet).

  • Opens this file within the current browser window.

    This is the default behavior for Windows operating systems earlier than Windows Vista. You can change this behavior on the Edit File Type dialog box (which you access via My Computer > Tools > Folder Options or My Computer > View > Options, depending on the operating system version).

    This option is not available on Windows Vista and later Windows operating systems.

  • Prompts you to save the file to the hard drive.

  • Automatically saves the file to the hard drive.

Exporting to Excel, does not support exporting more than 250000 rows. Also, if the cube is based on a data connector that uses an external table, there is a limit of 1000 rows in the listing.

Note:

If no numeric formatting is specified, any numbers are formatted as integers by default. (The actual values are available in Excel, and you can change the formatting as needed to display them appropriately). If numeric formatting is specified in the model or in the pivot table definition, that formatting is used in Excel.

Printing a Pivot Table

To print a pivot table from the Analyzer, click the Print button . The system generates a .pdf file and opens it in your default browser. You can then use options in the browser to print the file or to download it, for printing later.

Note that if you download the file, the browser saves the file to the default download directory for that browser; there is no mechanism to specify a different location.

Also see Specifying the Print Settings.

Requirements for Printing

When a user invokes the Print option, InterSystems IRIS® data platform uses Java to call out to a third-party PDF rendering tool. The call out operation takes place using the /api/deepsee REST interface. This means that Java (or specifically JRE, Java Runtime Environment) is required on the user's machine.

This also means that you must configure security settings for your application appropriately. A user must hold the %System_CallOut:Use privilege in order to invoke the Print option. Furthermore, invoking the Print option may prompt the user to authenticate again if you have not configured the /api/deepsee web application to share a common authentication mechanism with the /csp/ web application for your namespace, which serves the Analyzer. (Similar problems may occur navigating between any two Management Portal pages which are served by different web applications.)

For information on the requirements, see Configuring InterSystems IRIS for PDF Output.

Sending Email

You can send email that contains a link to the pivot table, if your system is configured for this. To do so:

  1. Open a pivot table that contains the data about which you want to send a message.

  2. Click Menu > Send Email.

    If this option is not present, then your system is not configured to support email.

  3. The system then does one of the following:

    • Displays the default email system on your machine, with a message that contains a link to the dashboard.

      Edit the message, specify one or recipients, and send it as usual.

    • Displays a dialog box.

      Choose a recipient, type a brief message to include along with the generated link, and click OK.

Displaying the Pivot Table as a Chart

By default, the Analyzer displays the results in table format. You can use the buttons in the upper left to switch to other formats.

generated description: pivot as table and chart

To display the results as a table, click the Display As Table button generated description: button display as table.

If you click the Chart Options button , the Analyzer displays the following page:

Here you can do the following:

  • Specify a new title. To do so, type a value into Title.

  • Specify a chart type. To do so, click the button in the Chart Type section.

  • Control the display of grid lines. To do so, click a button in the Grid Lines section.

  • Control the text size. To do so, click a button in the Text Size section.

  • Specify the minimum value to display on the vertical axis. To do so, type a value into Minimum Axis Value.

  • Specify the maximum value to display on the vertical axis. To do so, type a value into Maximum Axis Value.

Notes that these changes do not affect the definition of the pivot table. When you add a pivot table to a dashboard, you can choose a chart type. Furthermore, a chart displays only the first 100 items.

For information on the chart types, see Available Chart Types.

Deleting a Pivot Table

To delete a pivot table in the Analyzer, click Delete.

Note:

When you delete a pivot table, the system moves it into a folder named $TRASH. Or, if the pivot table is already in a folder, then $TRASH/ is prepended to the folder name. In either case, the pivot table is no longer visible in the web-based interfaces.

FeedbackOpens in a new tab