Loading

Creating a Parameter Control to Sort the View

Дата публикации: Feb 27, 2025
Задача

How to use a parameter to choose which field to sort your view by. 

 

Действия

The below steps are based on Sample - Superstore data:
 

Parameter, Calculation, and View creations step

 

Step 1: Create a parameter

  1. Right-click in the Data pane and select Create Parameter. 
  2. In the Create Parameter dialog, do the following, then click OK: 
    • Name: Sort By 
    • Data Type: String
    • Allowable Values: List
    • For Value, enter the following list: "Region", "Sales", "Month", "Month, Sales".  
  3. Right-click the [Sort By] parameter in the data pane and select Show Parameter Control.
  4. Create a second parameter called [Sort Order] with the values "asc" and "desc"

Step 2: Create a calculated field

  1. Select Analysis > Create Calculated Field.
  2. Name the field Sort by Dimension. Enter the following formula, then click OK:
    CASE [Sort By]
    WHEN 'Sales' THEN RANK(SUM([Sales]), [Sort Order])
    WHEN 'Month' THEN RANK(MIN( INT( DATETRUNC('month', [Order Date]) )), [Sort Order])
    WHEN 'Region' THEN RANK(MIN([Region]), [Sort Order])

    WHEN 'Month, Sales' THEN ( RANK(MIN( INT( DATETRUNC('month', [Order Date]) )), [Sort Order]) * 100000 ) + RANK(SUM([Sales]), [Sort Order])
    END

Step 3: Build your view 

  1. Right-click and drag [Order Date] to the Rows shelf
  2. In the Drop Field dialog, select MY(Order Date) and click OK
  3. Drag Region to the Rows shelf after MY(Order Date)
  4. Drag [Sales] to the Columns shelf
  5. Drag [Sort By] to the Rows shelf, placing it in the leftmost position
  6. Right-click [Sort By] on the Rows shelf and uncheck Show header
Дополнительные ресурсы
  • Dimensions on the Rows or Columns shelf are treated hierarchically, so sorting on a dimension will not affect any dimensions left of it on the shelf. This is why [Sort By] must be in the first position on the Rows shelf.
  • The RANK() function is a table calculation, which can be customized by changing how it is computed. For more information, please see Transform Values with Table Calculations
  • If sort dimensions are not added to the view, then the sort may not work as expected. If the example view only had [Region] then sorting by "Month" would not appear to do everything because every region has the same months.
  • If desired, you can create a calculated field that will change the granularity in the view. Something like:
    • CASE [Sort By]
      WHEN 'Region' THEN [Region]
      WHEN 'Month' THEN STR(DATETRUNC('month', [Order Date]))
      ELSE ' '
      END
  • Dates need to be converted to an integer using INT() so that dates will retain chronological order
  • "Month, Sales" is a nested sort. In general, to create nested sorts, multiply the rank of the first sort by a large number and then add the rank of the second sort. The number should be large enough that the ranks 'stay separate' within the result. For example, adding together rank 1 and rank 3 should look like 103 rather than 4. This allows reach dimension/measure to be sorted separately in the final view.
Номер статьи базы знаний

001453736

Вложения

Parameter Control Options_v2019.1.twbx

1252 KB

 
Загрузка
Salesforce Help | Article