Creating an Analytics Query

Only operators with an excellent understanding of SalesConnect data objects should create analytics queries. Designing and creating queries without using appropriate methods for extracting data may produce results that appear to yield the expected data, but are actually inaccurate. Authorized operators can save analytics queries and enable others to run, or process, the saved queries.

Using the Administration personaA persona is an interface set of menus and dashboard or home view that was designed for a single set of operators who share goals, skills, attitude and behavior patterns. Each persona's interface displays only the content and tools that are relevant for that group., select Analytics from the Feature Configuration menu. Or, select in the universal menu, and then select Analytics Queries under Analytics.
On the Analytics QueriesAn analytics or BA (for business analytics) query is a special type of saved search that selects data from SalesConnect so that you can work with it in pivot tables. When you create an analytics query, you select query criteria based on objects and columns/field names, just as with other queries. But analytics queries are different from standard queries, which are saved searches for key connections like firms, offices and reps; analytics queries are more open-ended. page, select Add.
Build the queryA query is a special type of saved search for connections or other data in SalesConnect, created with a SalesConnect query tool rather than a standard search page. When you create a query, you select query criteria based on familiar field names from the interface; the criteria you specify are transformed behind the scenes into the appropriate SQL statements. using the Add Analytics Query page.
1. Select business objectsIn object-oriented programming, an object is a software bundle of related attributes (properties) and behavior (methods). Software objects are often used to model the real-world objects that you find in everyday life. Often, you can think of an object as roughly equivalent to a table in a database, though the object has additional logic attached to it. For example, one type of "object" in the real world and in a SalesConnect application is a person, known in SalesConnect as a contact or connection. Each contact has many properties, such as a name and birth date; in SalesConnect, these are properties of the object Contact. In the real world, each contact also has many behaviors; in software such as SalesConnect, you are typically interested only in recording or managing certain types of behavior for Contact instances. Objects in SalesConnect are logically named entities mapped to Java classes: objects are the instantiations of classes. So, the class for the Contact object includes methods for setting and retrieving key properties for Contact entities, such as the contact's name. An object may call its own methods, or it may call another object's methods that are visible to it. In SalesConnect, the properties for objects defined for an application project are most typically mapped to specific database table columns. to include in the query.
  
  The order of selection for objects is always important, as it affects how you can explore the relationships between business objects in the query.
  
  Select Objects. Then, in Object List, select the root object you want to include, and drag it to the Object Editor. Object List now shows only objects related to the one you selected; that relationship is shown with an angle bracket. For example, if you selected the Contact object, the remaining objects are all shown with Contact > in front of their names.
  
  Repeat this until you've included all objects you want for the query. Objects in Object Editor may be indented to show that they are subordinate to other selected objects. For each object after the first, you can use dropdown to its right to select the type of join you want to enforce between this object and the primary one.
  
  You can remove an object from the Object Editor: Select for that object.
2. Choose the result columns for the query.
  Select Columns. Then, in Property List, select a propertyIn SalesConnect, a property is an attribute for one aspect of the state of an object. For instance, the contact object has properties that include a first name (firstName) and last name (lastName). (You can think of a property as roughly equivalent to a column.) you want as a result column, drag it to Column Editor, and drop it in the order you want it to appear. Repeat to include all result columns you want. For each included property, use the dropdown next to it to select an option:
  - No Aggregate shows just property values for this property
  - Minimum shows only the lowest value from all found values
  - Maximum shows only the highest value from all found values
  - Count shows only the number of found records for this property.
  To remove a property from Column Editor: Select for the property.
  
  To move a property in Column Editor: Select for the property, drag to the location you want, and drop.
  
  When creating a free-form SQL BA query, the order in the Column Editor must match the order of inclusion in the SQL statement.
3. Define any parameters you want to use in the query's criteriaIn a query, a criterion is a single point of comparison. For instance, one criterion in a query might be reps in the state of Michigan. Query and search criteria are based on properties in the primary SalesConnect objects associated with the type of connection or other record which the query or search finds..
  Parameters aren't required; they allow those running the query to choose some of the values used, making individual queries more flexible.
  1. Select Parameters, and then select Add Parameter.
  2. In the new parameter line, enter a prompt over the value New Parameter to set the text that's shown to operators when they need to choose a value for the parameter.
  3. If you want, associate the parameter with a property, taking advantage of that property's default field type for selecting data and any associated validation listA validation list is a list of valid values for a particular field (property). The values included in many lists can be maintained in SalesConnect..
    
    In the dropdown just after the parameter prompt, select the object containing the property you want. Another dropdown appears right after the one showing the selected object; use it to select the property you want. (This property doesn't need to be included in the query, though it often will be.) Once you select a property, its data type and default field control may be shown in non-editable fields right after the property.
  4. If you didn't associate the parameter with a property, select a data type for it, in the dropdown that defaults to String.
  5. If it is editable, you can use the dropdown list next to the data type to select the type of control to display when it's time to select values.
  6. If you want, enter or select a Default value.
  7. If you want, select Required so that everyone who uses this query must choose a value for this parameter.
4. Define criteria to filter the records included in the query's results. You can add criteria based on object/property combinations or on object subqueriesA subquery is really a separate query that is nested inside the WHERE clause of a primary query. Subqueries can allow you to refine a query in several different ways. They can: (1) Allow you to expand a query to include criteria based on properties from additional objects that contain related data. For instance, when you create a Rep query, you can include a subquery that includes criteria based on properties in the Transaction History object. Transactions in the Transaction History object are generally associated with specific reps, so criteria based on the Transaction History object allow you to expand your search for Reps to related data. (2) Limit the results returned by a query based on criteria for a parent object. For instance, when you create a Rep query, you can include a subquery that includes criteria based on properties in the Firm or Office objects. All Reps are associated with Firms and Offices, so subqueries that limit the Firms or Offices with which the Reps returned by a query may be associated, you are limiting the results based on criteria for a parent object. (3) Search within a pre-selected subgroup of the active entity type. For instance, when you create a Rep query, if you know that you only want to find Reps for whom an email address is stored in SalesConnect, but you want to add other criteria to the query as well, it may be most efficient to include a subquery that pre-selects only those Reps with email addresses in SalesConnect and then add other query criteria to select within that group..
  Add object/property criteria to a query
  1. Select Add Criteria. As you add criteria, you can include parentheses as needed, with ( and ).
  2. Select the business object/property you want for this criterionIn a query, a criterion is a single point of comparison. For instance, one criterion in a query might be reps in the state of Michigan. Query and search criteria are based on properties in the primary SalesConnect objects associated with the type of connection or other record which the query or search finds.. A dropdown shows available options, mostly in the format object > property; at the bottom are some subquery options.
  3. In the next dropdown list, select the comparison operator to use.
  4. In the dropdown list after the comparison operator, select an option for values, and then complete the needed information for that option.
    
    For Value, enter a value or values to be used with the selected operator, in the next field. You may be able to select from a list. If you type values, separate each value from the others with a semi-colon ( ; ). For example, red;green;blue is a valid list.
    
    For Special, select a specialA special is a kind of variable you may be able to specify as a value to use in a query criterion or in other places in SalesConnect. Some specials are Current Date/Time, End of Day, and Start of Year; these specials all refer to specific dates and/or times, while other specials may return other data. When the query or other item using a special is processed, the special returns the current value that the special represents. in the next dropdown list.
    
    For Property, select another property whose value you want to use, in the next dropdown list.
    
    For Parameter, select the parameter you want in the next dropdown. Each time the query's run, a prompt will request a value for the parameter.
  5. Repeat these steps to define as many object/property criteria as you need. When you add more criteria, select And or Or at the beginning of the new line to determine how it is used in the query.
  Select for a criterion if you want to delete it.
  
  To move a criterion: Click for the criterion, drag it to the location you want in the list, and drop it there.
  Add object subquery criteria to a query
  1. Select Add Criteria. As you work with criteria, you can add parentheses as needed, with ( and ).
  2. Select the subquery you want for this criterion. A dropdown list shows those available; subqueries represent objects in SalesConnect, include the text (Subquery), and are listed after object > property values, in the same list.
  3. In the next dropdown list, select the comparison operator you want:
    
    Has finds records in the subquery object that match the criteria.
    
    Does Not Have finds records in the subquery object that don't match.
  4. In the next dropdown, select the object related to the subquery object that contains a property you want to use in the search. As soon as you select an object, the dropdown list is replaced by a link.
  5. Select the object link. Subqueries now shows that you are working with a subquery object.
  6. Add criteria to the new subquery as needed.
  Select the top-level object in the Subqueries field to return to top level of your query; select any subquery object to return to the criteria defined for it.
Set sorting options for results.
1. Select Ordering. The Order By Editor may show some suggested properties to use for sorting. You can change these as needed.
2. In the Property List, select a property you want to sort by, drag it to the Order By Editor, and drop it in the list. Repeat this step until you have all of the properties you want to use listed in the Order By Editor, in the order you want.
  
  To remove a property from Order By Editor: Select for the property. To move a property in Order By Editor: Select for the property, drag it to the location you want and drop it.
3. In the first dropdown list next to each property in the Order By Editor, select any aggregate option you want:
  - No Aggregate sorts by property values.
  - Minimum sorts by the lowest value from all found by the query
  - Maximum sorts by the highest value from all found by the query
4. In the next dropdown list for each property, choose Ascending or Descending.
If you want to save the query:
1. Complete fields at the top of the page to name and describe the query.
2. Set security to choose the operators and rolesRoles are linked to security and personas in SalesConnect. Each role represents a group of operators who perform similar tasks and who need access to similar features. Each operator is assigned to at least one specific role. Roles may be included in security rules and in territory category definitions, too. that will be able to use the query, using the Security Type area. Private is for your use only. Public is for use by all. (This option isn't always available.) Shared is for a specific group represented by a security ruleSecurity rules are used to control access to specific tools, controls, pages and other features in SalesConnect. Each security rule includes roles and/or operators who will have access to the items secured by that rule., so you'll have to select one. If you select Shared, is shown next to the field, and you can use it to show a popup you can use to select (or add and then select) the rule you want. Once selected, the name of the rule is shown in the field. See Manage your security rules for more detailed steps.
  
  All queries can be maintained by authorized administrative operators.
3. Click Save. Your new query should now be available for use. You should test it to make sure it returns the intended results.