Build a Cross Tab-type Data Source

A Cross Tab data source, when generated, looks very much like an Excel spreadsheet with rows and columns.  As an example, to use a Cross Tab data source to show a count of work orders completed by month and resource, follow these steps.  

1. Go to Data Sources > New.

2. Enter a Name for your data source, such as "Resource WO Count".

3. Enter a Description for the data source, such as "Count of work orders by month and resource".

4. In the Segment drop-down menu, select the segment in which this data source should appear.
5. In the Type drop-down menu, select Cross Tab.
6. In the Module drop-down menu, select Work Orders.
7. In the Category drop-down menu, select GENERAL.

8. In the Sub Category drop-down menu, select GENERAL.

9. In the Status drop-down menu, select ACTIVE.

10. If you want to select a Saved Query or check the Use On-The-Fly Ad-Hoc Query option, you can do so. We will leave these blank for our example.

11. Click Save.

12. When the screen changes to the Data Source Edit screen, select the Data Source Editor tab.

    Three smaller drop zones within the Column Area drop zone open.

    • Row - Determines which field is used for the rows of the report.

    • Column - Determines which field is used for the columns of the report. The distinct values from this field are used as column headers.

    • Data - Determines which field is counted or supplied to a numeric aggregate function (Average, Sum, Min, Max).

13. First, expand the Related Tables group in the Suggested Fields list and then Assignments. Click and drag the Resource Last Name field to the Row zone.

14. Next, click and drag the Completed Date field from the Additional Fields group to the Column zone and the Filter By Area.

15. In the Filter By Area, select This Year from the Condition drop-down menu.

16. Click and drag the Status Description field from the Common Fields > Required group to the Filter By Area.

17. For the Status Description field criteria, select Completed.

18. Click Apply Filter.

19. Click and drag the WO Number field from the Common Fields > Required group to the Data zone.

20. Click the Options button for the WO Number field and select Count in the Summary Functions drop-down menu.

21. Close the Options menu.

22. Click Save.

    You've now created a Cross Tab data source that you can use as-is, or link to a report definition.