Adding Dynamic Group By to a report

This example shows how to use Dynamic Group By to group results in a list by the Team, Pay Group, Calc Group, and Shift Pattern columns. The user selects one of those columns when running the report, and the report returns results that are grouped by the selected column. This example uses the Work namespace in the WFMStandard package, but the concept can be applied a report using any namespace or package.

  1. Create a new report using the sample report template.
    See Creating a report.
  2. Drag a List to the third block on the report page and add these query items to the list:
    Namespace > Query Subject > Query Item
    Work > Team > Team Name
    Work > Pay Group > Pay Group Name
    Work > Calc Group > Calc Group Name
    Work > Shift Pattern > Shift Pattern Name
  3. In the Toolbox pane, expand Textual and drag the Query Calculation object into the list.
    The Data item expression dialog box is displayed.
  4. Specify a name in the Name field.
  5. In the Expression Definition field, specify this query to determine which column to group the results by, based on the GroupBy parameter selected by the user. 
     if (?GroupBy? = 'Team') then
 ([Work].[Team].[Team Name])
 else if (?GroupBy? = 'Pay Group') then
 ([Work].[Pay Group].[Pay Group Name])
 else if (?GroupBy? = 'Calc Group') then
 ([Work].[Calc Group].[Calc Group Name])
 else
 ([Work].[Shift Pattern].[Shift Pattern Name])
  6. Click OK.
  7. Select the newly created column and click Group/Ungroup.
  8. Open a prompt page and add a Value prompt for the user to select the GroupBy parameter:
    1. In the Toolbox pane, expand Prompting and drag the Value Prompt item to the right pane.
      The Prompt Wizard dialog box is displayed.
    2. Select the Use existing parameter option and select GroupBy in the drop-down box.
    3. Click Next.
    4. Clear the Create new query check box and click Finish.
    5. Click Show properties and double-click the Static Choices property.
      The Static Choices dialog box is displayed.
    6. Click +.
    7. Specify Team in the Use field and TEAMS in the Display field.
    8. Click OK.
    9. Repeat steps 8.f to 8.h to add these values:
      Use Display
      Pay Group PAY GROUPS
      Calc Group CALCULATION GROUPS
      Shift Pattern SHIFT PATTERNS
    10. Click OK.
  9. Click Run options > Run HTML.
When you run this report, you are prompted to select the GroupBy parameter. Depending on your selection, the report results are grouped by Team, Pay Group, Calc Group, or Shift Pattern.