How do I add datasets to an Adhoc Blank Starter Report?

Warning: Before you can add datasets to the Adhoc Blank Starter Report, you must save the report to a client custom folder. For more information, see How do I save an Adhoc Starter Report to a client custom folder?.

To add new datasets in the Adhoc Report Builder

1. Do one of the following:

   • On the Getting Started page, click Open.

     

   • Click File > Open on the Adhoc Report Builder menu bar.

     

2. On the Open Report window, select the report to open and click Open.

   

3. In the Report Data navigation pane:
   1. Double-click Data Sources.
   2. Double-click Powerpay.

   

   The Data Source Properties window opens.

   

4. Click Use a shared connection or report model.

5. If the company data source Powerpay appears, skip to step 5.

   If Powerpay doesn't appear:

   1. Click Browse.
   2. Double-click the Client Reports folder.
   3. Double-click the Data Sources folder.
   4. Click Powerpay.
6. Select the company data source (Powerpay), and click Test Connection.

   

The following message appears: “Connection created successfully.”

7. Click OK.
8. Click OK to close the Data Source Properties window.
9. In the Report Data navigation panel, click New > Dataset.

   

    

10. On the Dataset Properties window:
    1. Accept the default or enter a new dataset name.

       Note: When adding new dataset names, Dayforce recommends adding ds as the first two characters of each dataset name. For example, for an Employee dataset, you would add dsEmployee

    2. Select Use a database embedded in my report.
    3. In the Data Source field, select Powerpay.
    4. Click Query Designer.

    

    The Query Designer window opens. Select the views (commonly known as tables) and fields to use for the report.

11. In the Database View section, select the Views folder under one of the rpt folders.

    

    Depending on your selection, a list of all available views appears.

    rptPP:

    

12. Expand the View you want to use, and a list of all available fields (also known as columns) for that view appears.

    

13. To select:
•  All fields for the view, click the check box to the left of the View.
• One or more fields for the view, click the box to the left of each field you want to select.  

  Tip: Dayforce recommends selecting individual fields instead of selecting all fields for a view.

  All selected fields appear in the Selected fields section of the Query Designer window.

  

  Note: To retrieve all the fields needed for a report, you need to select fields from more than one view. For example, in this Help topic, fields are selected for the vwPowerpayPPAddress and vwPowerpayPPEmployee views.

  13. After you select the desired fields from each view, use the tool bar in the Relationships section to complete the following steps:

      

      1. If the Relationships section isn't expanded, click the double arrow ().

      2. Click Auto Detect, and the following values appear in the Relationship section:

         • Left Table column - The views you previously selected.
         • Join Type column - Unrelated. Indicates that the selected views aren't currently linked.

         

      3. Click Auto Detect again, and the selected views no longer appear.

          

      4. On the tool bar, click the Add Relationship icon () to link views.
      5. Click in the Left Table column and select a view.

      6. Click in the Join Type column and select the type of join to use to link the two views.

         Note: The Inner join is the most commonly-used join type.

      7. Click in the Right Table column to elect another view.

      8. In the Join Fields column, click Double-click to change fields.

         

14. On the Edit Related Fields window that opens, click the Add Fields icon () on the tool bar.

    

15. Click in the Left Join Field column and select the Primary key field for your main view.

16. Click in the Right Join Field column and select the Secondary key field for the next view.

    

17. Click OK.
18. Click OK on the Query Designer window.
19. Click OK on the Dataset Properties window.

20. In the Adhoc Report Builder menu bar, click Insert > Table > Insert Table.

    

21. To insert the table, click your cursor in the body of the report.
22. From the Report Data navigation panel locate the dataset you added.
23. One by one, select each field to add to the report and drag and drop it into the inserted table in the report body.

24. When all fields are selected, click Home > Run on the Adhoc Report Builder menu bar.

    

25. To filter the information you want to appear on the report, in the Pay Group field, select one or more pay groups.

    Caution: Each time you click Run, you must select one or more pay groups.

26. Click View Report.