Loading
Sales Performance Management
Table of Contents
Filter by (0)Add
Select Filters

          No results
          No results
          Here are some search tips

          Check the spelling of your keywords.
          Use more general search terms.
          Select fewer filters to broaden your search.

            Search all of Salesforce Help
            Search all of Salesforce Help
            Create CRM Analytics Pivot Tables with Salesforce Spiff Data in Data 360

            You are here:

            1. Salesforce Help
            2. Docs
            3. Sales Performance Management

            Create CRM Analytics Pivot Tables with Salesforce Spiff Data in Data 360

            Visualize Salesforce Spiff statement and obligation data in CRM Analytics after you sync Spiff commission data into Data 360.

            Required Editions

            Available in: both Salesforce Classic (not available in all orgs) and Lightning Experience
            Available in: Enterprise, Unlimited, and Developer Editions
            Available for an additional cost in: Professional Edition with Web Services API Enabled
            User Permissions Needed
            To create a dashboard: (Salesforce) Create CRM Analytics Apps
            Important
            Important First, set up the Data 360 managed package and connect a Salesforce Spiff data stream. See Set Up the Data 360 Managed Package for Salesforce Spiff.

            Create these pivot tables in CRM Analytics.

            • A statement pivot table shows statement records that contain summary data, such as plan name, rep name, statement total payout, and worksheet calculations.
            • An obligation pivot table shows rows of records from line-by-line payout rules in Spiff, with record-level attributes such as Opportunity CloseDate, ARR, and OwnerId.

            Create an App in CRM Analytics

            To create a pivot table, first create an app that hosts the dashboard with data for the table.

            Complete the steps in Create an App, and name the app something like Spiff Reporting Pivot Tables.

            Create a Dashboard for a Statement Pivot Table

            Create a pivot table for a statement report, with columns for currency, string, and date values.

            1. Set up the pivot table.
              1. In the app you created, select Create | Dashboard.
              2. Click Create Blank Dashboard.
              3. Drag the table widget to the dashboard canvas, leaving space to the left for another widget you add later. Resize the table.
              4. Click the table icon on the canvas, select the Salesforce Data Cloud data source, click the Data Models tab, and select the acon-commission_statement_value object.
              5. Click the table mode icon in the upper right and select Pivot Table.
              6. On the Data tab in the left panel, click Count of Rows under Columns, select the Sum measure, and then select ValueNumber.
              7. On the Data tab, under Group By, click the add button (+) and add the SourceName and StatementId fields.
            2. Add a column for currency values.
              1. Under Sum of ValueNumber, click the add button (+) and select Add Column | Formula.
              2. For Column Alias, leave the default B value.
              3. For Column Header, enter Currency.
              4. Under Calculation, click the Fields dropdown and select CurrencyCode.

                The formula field populates with the min() function.

                min(SpiffDC__CurrencyCode__c)
              5. Click Apply.
            3. Add a column for string values.
              1. In the Editing Column header, click the add button (+) to add another column.
              2. For Column Alias, leave the default C value.
              3. For Column Header, enter String.
              4. Under Calculation, click the Fields dropdown and select ValueString.

                The formula field populates with the min() function.

                min(SpiffDC__ValueString__c)
              5. Click Apply.
            4. Add a column for date values.
              1. In the Editing Column header, click the add button (+) to add another column.
              2. For Column Alias, leave the default D value.
              3. For Column Header, enter Date.
              4. Under Calculation, click the Fields dropdown and select ValueDate.

                The formula field populates with the min() function.

                min(SpiffDC__ValueDate__c)
              5. Click Apply.
            5. Update the label for the Sum of ValueNumber column.
              1. In the Editing Column header, click the left arrow button to go to Column A, currently named Sum of ValueNumber.
              2. Rename the column Number.
              3. Click Apply.
              4. Click Done.
            6. Add the list widget.
              1. Drag the list widget to the dashboard canvas, to the left of the table you previously added.
              2. Click the list icon on the canvas, select the Salesforce Data Cloud data source, click the Data Models tab, and select the acon-commission_statement_value object.
              3. For Fields, select SourceName.
              4. In the right panel, select the Query tab.
              5. For Faceting, select Include.
              6. For Selection Type, select Multiple Selection (required).
              7. Save your changes.
            7. Rename the dashboard Statement Pivot Table and save it under the Spiff Reporting Pivot Tables app you created.

            When you select fields from the list widget, the fields are added to the pivot table.

            The list widget includes all fields that you turned on for Data 360 in Spiff. If you select an obligation field for a statement pivot table, the field shows the minimum value from the full list of values associated with the statement. For example, the CloseDate obligation field can have multiple deals associated with a single statement, because not all deals close on the same day. If you include the CloseDate field in a statement pivot table, the field shows the minimum date value.

            Obligation fields that contain amounts (numbers) are aggregated with the sum() function. For example, if you add the ARR obligation field to a statement pivot table, all deals associated with the statement have the ARR amounts aggregated in the table.

            To simplify a statement pivot table in CRM Analytics, consider adding only statement-level fields for Data 360 in Spiff. See Sync Fields for Data 360 Reporting with Salesforce Spiff.

            Create a Dashboard for an Obligation Pivot Table

            Create a pivot table for an obligation report, with columns for currency, string, and date values.

            1. Set up the pivot table.
              1. In the app you created, select Create | Dashboard.
              2. Click Create Blank Dashboard.
              3. Drag the table widget to the dashboard canvas, leaving space to the left for another widget you add later. Resize the table.
              4. Click the table icon on the canvas, select the Salesforce Data Cloud data source, click the Data Models tab, and select the acon-commission_statement_value object.
              5. Click the table mode icon in the upper right and select Pivot Table.
              6. On the Data tab in the left panel, click Count of Rows under Columns, select the Minimum measure, and then select ValueNumber.
              7. On the Data tab, under Group By, click the add button (+) and add the ObligationId and SourceName fields.
            2. Add a column for currency values.
              1. Under Min of ValueNumber, click the add button (+) and select Add Column | Formula.
              2. For Column Alias, leave the default B value.
              3. For Column Header, enter Currency.
              4. Under Calculation, click the Fields dropdown and select CurrencyCode.

                The formula field populates with the min() function.

                min(SpiffDC__CurrencyCode__c)
              5. Click Apply.
            3. Add a column for string values.
              1. In the Editing Column header, click the add button (+) to add another column.
              2. For Column Alias, leave the default C value.
              3. For Column Header, enter String.
              4. Under Calculation, click the Fields dropdown and select ValueString.

                The formula field populates with the min() function.

                min(SpiffDC__ValueString__c)
              5. Click Apply.
            4. Add a column for date values.
              1. In the Editing Column header, click the add button (+) to add another column.
              2. For Column Alias, leave the default D value.
              3. For Column Header, enter Date.
              4. Under Calculation, click the Fields dropdown and select ValueDate.

                The formula field populates with the min() function.

                min(SpiffDC__ValueDate__c)
              5. Click Apply.
            5. Update the label for the Min of ValueNumber column.
              1. In the Editing Column header, click the left arrow button to go to Column A, currently named Min of ValueNumber.
              2. Rename the column Number.
              3. Click Apply.
              4. Click Done.
            6. Add the list widget.
              1. Drag the list widget to the dashboard canvas, to the left of the table you previously added.
              2. Click the list icon on the canvas, select the Salesforce Data Cloud data source, click the Data Models tab, and select the acon-commission_statement_value object.
              3. For Fields, select SourceName.
              4. In the right panel, select the Query tab.
              5. For Faceting, select Include.
              6. For Selection Type, select Multiple Selection (required).
              7. Save your changes.
            7. Rename the dashboard Obligation Pivot Table and save it under the Spiff Reporting Pivot Tables app you created.

            When you select fields from the list widget, the fields are added to the pivot table.

            The list widget includes all fields that you turned on for Data 360 in Spiff. If you select a statement field for an obligation pivot table, the field doesn't show any data. For example, if you include the Statement Name field in the obligation pivot table, the field is always empty.

            To simplify an obligation pivot table in CRM Analytics, consider adding only obligation-level fields for Data 360 in Spiff. See Sync Fields for Data 360 Reporting with Salesforce Spiff.

            See Also

             
            Loading
            Salesforce Help | Article