Add a Data Set
A data set fetches data from the Trimble Unity database tables to display data in a report. The DataSet panel is where you provide a command type and query string and choose other options for your data set. Common Values can also be used and are generic report fields that can be added to a report.
WORKAROUND: When a data set is updated or rebound, the data set is cleared from a banded list and a table. After updating a data set, select the banded list or table in the report to open the Banded List properties. Select the correct Data Set Name from the Data section to re-establish the link. If it's not updated, no results are returned when the report is generated.
NOTE: If multiple data sets are adding, the report reads them read top to bottom.
- Open the Report Designer.
- Click the Data tab.
- Click Add in the Data Sets section.
- Double-click the DefaultDataSet.
It appears under the Data Sets section.
- Click Edit Data Set.
- Edit the Name of the data set. Data set names cannot have spaces, but underscores or camel case can be used.
- Select Command Type. The default is Text.
- Enter a query to gather the desired fields from the desired database tables. These are the fields that are used to create reports.
TIP: To add an entire table to a report, enter the following in the Query field: Select * from <table>. See Common Query Examples and Add a Dataset in the Mescius documentation for more a more in-depth explanation of data sets and crafting queries.
Add a Trimble Unity Resources Data Set
If the Trimble Unity Resources custom data source was added to pull in GIS thumbnails or attachments, enter something similar in the Query field to connect to the files wanted:
TIP: You can use an asterisk (*) in a query at the end to pull in all attachments in a folder. If there are many attachments in the folder, it may attempt to pull in thousands of records, if not more, and cause a performance backlog as well as not returning the desired results.
- GIS Thumbnails
- thumbnail:workorder[WorkOrderId].png
- This query fetches all work order related map images for the work order ID entered when the report is viewed.
- thumbnail:inspection[InspectionID].png
- This query fetches all inspection related map images for the inspection ID entered when the report is viewed.
- thumbnail:request[RequestID].png
- This query fetches all service request related map images for the service request ID entered when the report is viewed.
- thumbnail:case[Case_Object_Id].png
- This query fetches all case images from the case ID entered when the report is viewed.
- Attachments
- attachment:WorkOrder/[WorkOrderID]/*
- This query fetches all attachments related to the work order ID entered when the report is viewed.
- attachment:Inspection/[InspectionId]/*
- This query fetches all attachments related to the inspection ID entered when the report is viewed.
- attachment:Request/[RequestId]/*
- This query fetches all attachments related to the service request ID entered when the report is viewed.
- attachment:reldocs/[CA_OBJECT_ID]/*
- This query fetches all attachments related to the case ID entered when the report is viewed.
- attachment:reldocs/[CA_OBJECT_ID]/payments/*
- This query fetches all payment attachments related to the case ID entered when the report is viewed.
- attachment:reldocs/[CA_OBJECT_ID]/tasks/*
- This query fetches all task attachments related to the case ID entered when the report is viewed.
- attachment:EmployeeImage/[EmployeeSID]/*
- This query fetches all employee images related to the employee SID entered when the report is viewed. The query syntax is case sensitive and must match this exactly.
IMPORTANT: A parameter must be configured if you are entering the ID of a specific work activity when viewing the report. See Create a Parameter for more information.
To see the full process of configuring attachments for a report, see Configure Attachments for ActiveReports for more information.
Add a Trimble Unity GIS Data Set
If the Trimble Unity GIS data source was added to pull in GIS fields into a report, enter something similar in the Query field to pull data from feature layers:
- Select * From *
- This query fetches all fields from all layers.
- Select * From <layer name>
- To select all fields from a specific layer, for example, select * from BUILDINGS. You can use commas to include multiple layers.
- Select <field> From *
- This query fetches the specified field from all layers, for example, select OBJECTID from *. You can use commas to include multiple fields.
- Select * From * where <condition>
- This query selects all fields from all layers that meet a certain criteria, for example, select * from * where OBJECTID > 0.
IMPORTANT: A parameter must be configured where you are entering the ID of a specific work activity when viewing the report to pull in GIS data from the work activity. See Create a Parameter for more information. Additionally, the work activity must have the same layer either added as an asset for a work order, or an address for a case to establish the connection.
Add a Trimble Unity Smart Forms Data Set
If the Trimble Unity Smart Forms data source was added to pull in Smart Forms fields, enter something similar in the Query field to connect to the files wanted:
- Select * From <Smart Forms template name>
- This query fetches all fields from that specific Smart Forms template.
- Click Validate.
|
|
- Click OK.
BEST PRACTICE: It is best to include only the necessary fields in the query to reduce the load on the application calls to the SQL Server instance. This approach ensures that the application logic iterates through fewer fields, optimizing performance.
The fields appear in the Data Sets panel on the right side. Now these fields can be dragged and dropped in to a report.
If a data source hasn't been added, a connection to the Trimble Unity database is automatically added in the Data Sources section.
