Learning
Community
Open Exchange
Global Masters
InterSystems IRIS Data Platform 2019.4 / Analytics / Defining Models for InterSystems IRIS Business Intelligence / Defining Listings
Previous section   Next section

Defining Listings

This chapter describes how to define listings. It discusses the following topics:
In Studio, you can define formatting for a listing. See “<listing>,” in the appendix “Reference Information for Cube Classes.”
You can also define individual listing fields with which users can create custom listings in the Analyzer. See the next chapter.
Tip:
You can also define listings outside of cube definitions (and without needing access to the Architect). See the chapter “Defining Listing Groups.”
Also see “Accessing the Samples Shown in This Book,” in the first chapter.

Adding a Listing

To add a listing:
  1. Click Add Element.
    The system displays a dialog box.
  2. For Enter New Item Name, type a listing name.
    See “Names for Model Elements,” earlier in this book.
  3. Click Listing.
  4. Click OK.
  5. Select the listing name in the Model Viewer, in the Listings section.
  6. Optionally specify the following details:
    • Display name — Localizable name of the listing. If you do not specify this, the user interface instead displays the logical name.
    • Description — Description of the listing.
    • Listing type — Type of the listing. Select table (the default) or map.
      If you choose map, see the section “Defining a Map Listing,” later in this chapter.
    • Resource — Specify the resource that secures the listing.
      For information on how this is used, see “Setting Up Security” in Implementing InterSystems Business Intelligence.
  7. Define the listing as described in the following sections.

Defining a Simple Listing

A simple listing uses fields in the source table that is used by the cube. To define a simple listing, add a listing as described earlier in this chapter and specify the following options:
  • Field list — Specifies a comma-separated list of fields in the source table to display. To specify this option, click the Search button images/d2model_button_search.png next to Field list to display a dialog box where you can select the fields. If you do so, the Architect displays the following:
    images/d2model_arch_listing_editor.png
    To use this dialog box:
    • If necessary, expand the items in the Source Class tree.
    • To add a property, double-click the property name in the Source Class tree. The property is then added to the end of the list shown in Field List. Properties with a default caption are automatically assigned an alias when added. For more information on the CAPTION property, see Core Property Parameters in Using InterSystems IRIS Objects. Note that the value shown is the SqlFieldName for the property, which might be different from the property name.
    • To move an item up or down, click it in Field List and then click the up or down arrows as needed.
    • To edit an item, click it in Field List, make changes in Edit Field: and then click Update. For example, you can add an SQL alias. See the subsection “Additional Options.”
    • To delete an item, click it in Field List and then click the X button.
    When you are done, click OK.
    Or type directly into Field list. For example:
    PatientID,Age,Gender,HomeCity->Name AS "Home City",TestScore AS "Test Score"
    
    Note that upon use of the Listing — Field List dialog box, any headers will be automatically wrapped with the $$$TEXT token. The cube’s domain will be added using $$$TEXT, such as in the following example:
    Product->Name AS "$$$TEXT["Product","HOLEFOODS"]"
    
    You can override the cube’s default domain by entering a different domain directly into Field list.
  • Order by — Specifies a comma-separated list of fields in the source table by which to sort the listing (these do not need to be included in Field list). The overall sort is controlled by the first field in the list, the secondary sort is controlled by the second field, and so on.
    To specify this option, click the Search button images/d2model_button_search.png next to Field list to display a dialog box where you can select the fields. This dialog box is a simpler version of the one for Field list.
    Or type directly into Order By. For example:
    Age,Gender
    
    After a field name, you can include the ASC or DESC keyword to sort in ascending or descending order, respectively.
  • Ignore the Data Connector field and the Custom SQL query field.

Additional Options

Note the following points:
  • You can use arrow syntax to refer to a property in another table. See “Implicit Joins (Arrow Syntax)” in Using InterSystems SQL.
    PatientID,HomeCity,PrimaryCarePhysician->DoctorGroup
    
  • You can include aliases.
    PatientID,Age,Gender,HomeCity->Name AS "Home City",TestScore AS "Test Score"
    
    Or:
    PatientID,Age,Gender,HomeCity->Name "Home City",TestScore "Test Score"
    
  • You can localize an alias by providing using the special token $$$TEXT[]. For example:
    %ID,DateOfSale As "$$$TEXT["Date Of Sale"]"
    
  • You can use standard SQL and InterSystems SQL functions, if you enclose the function name within parentheses so that it is not interpreted as a field name.
    (UCASE(PatientID)),%EXTERNAL(Gender)
    
  • You can use more advanced SQL features if you use source.field_name rather than field_name.
    %ID,'$'||source.Sales AS Sales
    
    In this case, the Sales column displays the Sales field, preceded by a dollar sign ($).
Tip:
By default, InterSystems IRIS® uses the logical values for the fields. To instead use the display values, use the SQL function %EXTERNAL.

Defining a Data Connector Listing

A data connector listing uses fields in a data connector. To define such a listing, add a listing as described earlier in this chapter and specify the following options:
  • Data Connector — Select the data connector class to use.
  • Field list — Specify the fields to include. These must be fields in the data connector. You cannot include aliases, SQL functions, or arrow syntax.
    By default, the listing displays only a property named %ID, if that exists.
  • Order by — Not used.
  • Ignore the Custom SQL query field.

Defining an SQL Custom Listing

An SQL custom listing can use fields from tables other than the source table used by the cube. (Note that it cannot use fields from a data connector.)
Note:
The system supports another kind of “custom” listing — a listing defined by users in the Analyzer. See “<listingField>” in the appendix “Reference Information for Cube Classes.”
To define an SQL custom listing, add a listing as described earlier in this chapter and specify the following options:
  • Custom SQL query — Select this.
  • Custom SQL — Specify an SQL SELECT query as described in this section.
Before looking at the details of the Custom SQL query, it is necessary to understand how the system creates listings. For any listing, the system creates a temporary listing table that contains the set of source ID values that correspond to the facts used in the current context (the context in which the user requests the listing). Your custom SQL query selects fields where the records match the source IDs in the temporary listing table. Internally, the overall query might be as follows:
SELECT source.Field1,source.Field2 
FROM BI_Study.Patient source,internal-listing-table-name list 
WHERE source.%ID=list.sourceID AND list.queryKey='2144874459'
When you specify an SQL custom query, you specify tokens to replace some of these details. Specifically, for Custom SQL, you provide a query with the following basic form:
SELECT list of field names  FROM $$$SOURCE, othertable AS alias  WHERE $$$RESTRICT AND otherrestriction
Where:
  • The FROM clause specifies multiple tables to query. In this clause, the $$$SOURCE token establishes source as the alias for the source table of the cube, and it establishes list as the alias for the temporary listing table.
    Internally $$$SOURCE is replaced by something like the following: BI_Study.Patient source,internal-listing-table-name list
    Also, othertable AS alias specifies another table to query, and an alias for this table (if wanted). You can specify additional tables as well.
  • The WHERE clause specifies conditions that join the source table to the listing table and to your additional table (or tables).
    The $$$RESTRICT token is replaced by a condition that joins the source table to the temporary listing table; internally this is replaced by something context-dependent like the following: source.%ID=list.sourceID AND list.queryKey='2144874459'
    Also otherrestriction specifies another SQL restriction. In the syntax shown above, the restrictions are combined via AND. You could use OR instead or create a more complex WHERE clause if wanted.
  • For list of field names, use a comma-separated list of field names, which can be fields in any of the tables listed in the FROM clause. For a field in the source table of the cube, use a reference of the form source.fieldname. You can include arrow syntax as well.
  • You can include aliases, and you can localize an alias by providing using the special token $$$TEXT[]. For example:
    SELECT ID,UnitsSold As "$$$TEXT["Units Solds"]" FROM $$$SOURCE WHERE $$$RESTRICT
    
  • You can also include the ORDER BY clause at the end of the query.
As a simple example (which uses only the source table of the cube):
SELECT source.PatientID,source.Age,source.HomeCity->Name 
FROM $$$SOURCE 
WHERE $$$RESTRICT
For another example (using data from other tables):
SELECT source.PatientID,FavoriteColor 
FROM $$$SOURCE, BI_Study.PatientDetails AS details 
WHERE $$$RESTRICT AND source.PatientID=details.PatientID

Defining a Map Listing (Geo Listing)

By default, Listing type is table, and the listing displays a table of information. If you have suitable data, you can instead specify Listing type as map. In this case, the listing is a map with markers to indicate geographic locations contained in the listing data. For example, the map could highlight sales locations or customer locations. For example:
images/d2model_maplisting.png
Important:
A map listing uses the Google Maps API. Be sure that your usage of this API is consistent with the Terms of Use, which you can access via a link displayed in this listing, as shown in the previous picture.
Note that in order to use the Google Maps API, you must obtain an API key. See Specifying Basic Settings in Implementing InterSystems Business Intelligence for more information.
To define such a listing, use the general instructions earlier in this chapter and do the following:
  • Specify Listing type as map.
  • Define the listing query so that it contains the fields Latitude and Longitude (case-sensitive). These fields should contain, respectively, the applicable latitude and longitude in decimal format (rather than degree/minute/second format).
    For example, SQL Query could be as follows:
    EventId, $$$IKSUMMARY as Summary, LocationCoordsLatitude As Latitude, LocationCoordsLongitude As Longitude
    
    This example is from the Aviation demo; see “Using Text Analytics in Cubes” in Advanced Modeling for InterSystems Business Intelligence.
The listing query can also contain other fields. Any additional fields are displayed in a balloon when the user clicks a map position. For example:
images/d2model_maplisting_balloon.png
This example is from the Aviation demo; see “Using Text Analytics in Cubes” in Advanced Modeling for InterSystems Business Intelligence.
Previous section   Next section