Project the Same Query Results Differently in Charts and Tables

If charts and tables use the same query, each widget can show a different set of fields from the query results. You can also specify a different field order for each widget. For example, to change the measure that appears on each axis in a scatter plot, reorder the measures for that widget.

The following compare table query shows multiple measures grouped by a dimension. The Expected Amount measure is calculated based on the other two measures: Amount * Probability.

The following compare table query shows multiple measures grouped by a dimension. The Expected Amount measure is calculated based on the other two measures: Amount * Probability.

To show only the Expected Amount and Name fields for this widget, edit the widget and then select Hide for the other measures. (To show a hidden field, select Show. To reorder a field, select Move Up or Move Down.)

The following example shows two charts based on the same query. The second chart shows the effect of hiding the other two measures.

"chart_2": { 
    "parameters": { 
        "columns": [ "Name", "C" ], 
        "visualizationType": "hbar", 
        "step": "Amount_Prob_1", 
        ... 
    }, 
    "type": "chart", 
    ... 
}

Consider the following guidelines when configuring the “columns” JSON property.

• If fields are listed in "columns" and you add a measure or grouping to the query, the new field doesn’t appear in the widget until you add it to “columns.”
• If "columns" isn’t specified in the widget properties or is set to an empty array, the widget shows all fields available in the query results.
• CRM Analytics ignores fields specified in “columns” that don’t exist in the query. If none of the fields in “columns” exist in the query, the widget doesn’t show any fields.
• The order in which you specify fields in “columns” is the order in which they appear in the widget.

For charts and tables that use the same query, keep these behaviors in mind.

• If a chart widget’s underlying query returns multiple rows for the same grouping value, the chart only displays the first record for each unique grouping value and ignores the rest. As a workaround, customize the query in SAQL form.
• In a lens or dashboard, when you hide one of the groups of a chart with multiple groupings, the chart is no longer grouped by the hidden field.

Note There are two ways to show, hide, or reorder columns. Do it by editing the query or editing the widget. If you perform these actions at the query level, your changes impact all widgets that use the query and can break widgets that use the query. For example, a widget breaks if you hide a required measure.