Skip to main content

Business Intelligence Tutorial: Creating and Packaging Pivot Tables and Dashboards

After creating one or more Business Intelligence cubes, you typically create and package a set of initial pivot tables and dashboards, and users typically create new pivot tables and dashboards as needed.

This page briefly leads you through the process of creating pivot tables and dashboards.

For much more information on creating pivot tables and dashboards, see Using the Analyzer and Creating Dashboards.

Creating Pivot Tables

Earlier in this tutorial, we created a pivot table that uses the Patients cube. Now let us create pivot tables that use your new cube.

  1. Access the Analyzer.

  2. Navigate to the Tutorial cube.

  3. Expand the AgeD dimension in the Model Contents pane.

  4. Drag and drop Age Group to Rows.

  5. Drag Age Bucket to Rows and drop it onto the Breakout Here button .

  6. Drag and drop Count to Columns.

  7. Drag and drop All Patients to Rows, at the bottom.

    The Rows box now looks like this:

    generated description: pivot table sample2 setup

    The pivot table is as follows:

    generated description: pivot table sample2

  8. Click Save.

    The system displays a dialog box where you specify the pivot table name.

    Save the pivot table and give it a name. Here, we are saving the underlying query that retrieves the data, along with the information needed to display it the way you chose. We are not saving the data.

  9. For Folder, type Tutorial

  10. For Pivot Name, type Patients by Age Group

  11. Click OK.

  12. Create another pivot table as follows:

    • For Rows, use Diagnoses

    • For Columns, use Count and Avg Age

    • For Folder, select or type Tutorial

    • For Pivot Name, type Patients by Diagnosis

Creating a Dashboard

In this section we create a simple dashboard.

  1. Click the Analytics link at the top of the page.

  2. Click Home > Analytics > User Portal.

    The system then displays the User Portal, which lists the existing public dashboards and pivot tables.

  3. Click Menu > New Dashboard.

    The system displays a dialog box that prompts you for basic information about the new dashboard.

  4. For Folder, type or select Tutorial

  5. For Dashboard Name, type Sample Dashboard

  6. Click OK.

    The system creates, saves, and displays the dashboard, which is initially empty.

    generated description: dashboard step1

    Notice the > button on the left side of the dashboard.

  7. Click the > button.

    This step expands the Dashboard Editor, as follows:

    generated description: dashboard editor init view

  8. Click Widgets.

    This step displays a list of any widgets that this dashboard currently contains (none in this case).

  9. Click the + button.

    This step displays a dialog box where you can choose some initial options.

  10. In the dialog box, click Pivots and Charts, if this is not already expanded. The list expands to show a list of choices.

  11. In this list, click Table.

  12. Click the Search button next to Data source.

  13. Click Tutorial and then click Patients by Age Group

  14. Click OK to add this widget.

    The system displays the dashboard like this:

    generated description: dashboard step2

  15. Repeat the preceding steps to add the other pivot table that you created in the previous section.

    The newly added widget is placed in the same default position (in the upper left) and therefore covers the previously added widget.

  16. Put the cursor into the title bar of the newly added widget and then drag this widget and drop it below the other widget.

    You might want to leave space below the upper widget so that you can resize it.

  17. Use the resize control in the lower right corner of each widget to resize it so that all rows are visible without scrolling.

    generated description: dashboard step4

  18. Click Menu > Save.

    The system saves the dashboard immediately.

  19. Do the following to add some filter controls to this dashboard:

    1. Open the Dashboard Editor.

    2. Click Widgets.

    3. Click the Patients by Age Group widget.

    4. Click Controls.

      The Dashboard Editor displays a list (currently empty) of any controls defined on the selected widget.

    5. Click the plus sign button above the list.

      The system displays a dialog box where you specify the control.

    6. For Location, select Dashboard.

    7. For Target, type *

    8. For Action, select Apply Filter.

    9. For Filter, select ZIP Code

    10. For Control Label or Icon, type ZIP Code

    11. Click OK to add the control.

    Now the upper left corner of the dashboard is as follows:

    generated description: dashboard step5

  20. Repeat the preceding steps to add the Allergies and Favorite Color filters.

    Now the upper left corner of the dashboard is as follows:

    generated description: dashboard step6

  21. Reconfigure these filter controls so that each one has a default value. To reconfigure the ZIP Code filter:

    1. Open the Dashboard Editor.

    2. Access the definition of the Patients by Age Group widget.

    3. Click Controls.

    4. Click the ZIP Code control.

    5. For Default Value, type 32007 and click the check mark.

    6. Click Done.

    For Allergies, use the default value soy

    For Favorite Color, use the default value blue

    As you make these changes, the system automatically saves them.

  22. Test the dashboard and verify the following:

    • If you use the browser’s refresh button, each filter should show the correct default value.

    • Each filter should affect both widgets.

Exporting and Packaging the Pivot Tables and Dashboards

  1. Click Menu > Management Portal.

    If you do not see this option, you are currently logged in as a user without direct access to the InterSystems IRIS Business Intelligence development tools. In this case, log in again to Business Intelligence.

  2. Click Analytics > Admin > Folder Manager.

    The system then displays the Folder Manager.

  3. Scroll to the Tutorial folder, which should look something like this:

    generated description: package step1

    The column on the right indicates the InterSystems IRIS® data platform user who created these items; you may see different values there.

  4. Click the check box to the left of Sample Dashboard. The left area of the page now lists the pivot tables used by this dashboard:

    generated description: package step2

    Both of these pivot tables are in the same folder as the dashboard itself, which is not required.

  5. Click the check box to the left of the two newer pivot tables as well.

  6. For Directory, type the name of an existing directory to which you have write permission.

  7. Click Export.

    The system then writes three files to that directory.

    You could use these files as a package for the items you have defined, but in the next set of steps, we will use a more convenient approach.

  8. In an IDE, create a new class called Tutorial.DashboardsAndPivots. The class should extend %DeepSee.UserLibrary.ContainerOpens in a new tab.

  9. In the new class, add an XData block named Contents, as follows:

    XData Contents [ XMLNamespace = "http://www.intersystems.com/deepsee/library" ] 
    { 
    <items>
    </items>
    }
  10. Save the new class.

  11. Copy the dashboard and pivot table definitions from your exported files into this XData block:

    1. Use a text editor to open the dashboard file (Tutorial-Sample_Dashboard.xml), which looks as follows:

      <?xml version="1.0" encoding="UTF-8"?>
      <dashboard xmlns="http://www.intersystems.com/deepsee/library" folderName="Tutorial" name="Sample Dashboard" ...
      ...
      </dashboard>
      
    2. Copy the contents of this file, starting with the second line and ending at the end of the file. That is, do not copy the first line, which is the XML declaration.

    3. Paste the copied text into the XData block between <items> and </items>. Now you should see this:

      XData Contents [ XMLNamespace = "http://www.intersystems.com/deepsee/library" ] 
      { 
      <items>
         <dashboard xmlns="http://www.intersystems.com/deepsee/library" folderName="Tutorial" name="Sample Dashboard" ...
         ...
         </dashboard>
      </items>
      } 
      
    4. Use a text editor to open one of the pivot table files, which has a similar structure (with <pivot> instead of <dashboard>).

    5. Copy the contents of this file, , starting with the second line and ending at the end of the file.

    6. Paste the copied text into the XData block between </dashboard> and </items>. Now you should see something like this:

      XData Contents [ XMLNamespace = "http://www.intersystems.com/deepsee/library" ] 
      { 
      <items>
         <dashboard xmlns="http://www.intersystems.com/deepsee/library" folderName="Tutorial" name="Sample Dashboard" ...
         ...
         </dashboard>
         <pivot xmlns="http://www.intersystems.com/deepsee/library" folderName="Tutorial" name="Patients by Age Group" ...
         ...
         </pivot>
      </items>
      } 
      
      Note:

      You could instead insert the copied text between <items> and <dashboard>. The order of the items has no effect on anything.

    7. Repeat the preceding steps with the other pivot table file.

    8. Save the class definition.

  12. Return to the Folder Manager and refresh the page.

    This clears any selections you may have made.

  13. Click the check box next to each of the three items in the Tutorial folder.

  14. Click Delete and then click OK to confirm.

  15. In an IDE, compile the class you just created.

    When you do this, the system imports the dashboard and pivot table definitions contained in that class.

  16. Return to the Folder Manager and refresh the page. Notice that the tutorial dashboard and pivot tables are available again.

You can include as many dashboards and pivot tables as needed in a container class like this, and you can have multiple container classes. There are no requirements to organize the dashboards and pivot tables in any particular way in these container classes. For example, you do not need to place the pivot tables in the same container class as the dashboards that use them. There is also no requirement to create these container classes at all.

FeedbackOpens in a new tab