This feature is one of the most fundamental and important in Solution Builder. Data Query is a transitional level which takes data from various Data Sources and shows it in a unified user-friendly format. The data is represented in classical table format; each column can be configured in different ways depending on source and target. The article below describes how Data Queries can be configured and used in different Modules of Workspace Management application.
Creating and Editing a Data Query
Login to Workspace Management with administrator user and open Administration application. Select Navigation Item "Data Queries". Data Queries can be added, edited and deleted.
Certain general information, such as Name, Description and Default can be set in this section.
- Name: Name of Data Query should be unique. It is system information.
- Description: The purpose of Data Query should be described here.
- Default: For the same data source, for example, Data Definition, a user can create several Data Queries that can be used directly in different modules of the application, in this case the name of Data Query is referenced. But there are also cases, for example, if we use the Data Query in Layout Designer in Object Picker, when only a Data Definition is set, but the system uses corresponding Data Query. In this special case, the Data Query with "Default" = True is used by default.
There can only be one Data Query with "Default" = True for the same Data Definition. This option is always checked for the first Data Query created for the Data Definition. If the user sets "Default" = True for the next one, then this option will be automatically unchecked for the previous default Data Query for the corresponding Data Definition.
A user can choose one of the three options for the source of data to be used in Data Query. Depending on the chosen data source, the corresponding subform for filling of necessary objects will be shown:
Option 1: Data Definition
Data Definition can be selected using Schema Picker. All Data Definitions are available for selection. See example of using of this type of Data Source.
Option 2: WEB Service
WEB Service Operation should be selected here. All WEB Service Operations registered in the system are available. Operation field is mandatory. If defined Data Query works with data collection returned in specific property, then this property should be set in the corresponding field. See example.
Option 3: SQL Query
In case the "Data Definition" option does not cover the use case, the user can access data in the database directly using an SQL Query.
- SQL Query: valid SQL query should be set here. User can validate the query using the "LOAD COLUMNS" Button, but it will also generate the columns in the section bellow.
- Allow Cache: if checked the results of query will be cached on the server. User can also define how long it will be cached in the field "Cacheinterval".
- CultureSensitive: if checked then cache will be deleted after changing of the culture.
- CacheInterval: how long the query result is cached on the server.
- Such parameters are available in SQL Query:
- @LCID_long - Long language code for the current user session. Ex. 1031
- @LCID_short - Short language code for the current user session. Ex. 9
- @CurrentUserId: SPSUserClassBase.ID of current user
Columns can be added manually or generated using the button "LOAD COLUMNS". The second way is easier, for each attribute/field/property returned from the Data Source, one column will be generated. It makes sense first to generate columns using the button and then edit the generated columns, delete the columns that will not be used and add a new one if something special is needed.
The Edit dialog of the Column contains 3 sections.
- Expression [(...)]: the field contains the expression that defines the data taken from Data Source. The way to set the expression depends on the Data Source:
If the Data Source is Data Definition then ASQL expression should be used. In this case, the title "Expression [(...)]" contains a hint, for example "Expression (Subject)" or "Expression (Pickup)". Example of simple Expressions:
"Expression (StringType)" = Subject.
If the Data Source is WEB Service then Java Script expression should be set using contract from the service operation. You can see the contract details when you click on the service operation in Data Query.
If the Data Source is SQL Query then the only way to create the column is to use the LOAD COLUMNS button. Expression cannot be changed. All complex logic should be implemented at the level SQL Query.
- Name: technical name of the column. The name should be unique inside of the Data Query.
- Position: this field defines the order of columns in visualizations of the Data Query, for example, in the grid.
- Title Type: there are three options. If a user selected "No Title", the Title will be disabled. It is meaningful, for example, in case of icon data. "Take From Expression" means that the title will be taken from the Data Source. "Individual Display String" allows you to set any string, the value from Data Source will be pre-selected and can be edited.
- Title: title of the column in visualizations of the Data Query, for example, in the grid. It is set depending on option "Title Type", see above.
- Alignment: self-explanatory.
- Width: in visualizations of the Data Query, for example, in the grid, in px.
- Display: there are three options. If user selected "Visible", the column will be present in the grid and in search and sorting. A "Hidden" column is a column that is not present in the grid by default but can be added in grid settings on the right side (see example below). "None" means that the column is not seen in the grid but is used in search.
Example of hidden Property and how it can be added manually. Here the "Responsible" column is hidden:
- Visibility: the options bellow are self-explanatory.
- Format: date formatting rules. E.g. predefined 'fullDate', 'medium', 'shortTime', 'timeAgo', 'timeAgoShort or composed like 'yyyy-MM-dd- HH:mm:ss Z.
Search & Sorting
The Search and sorting options are configured differently for different Data Sources. For Data Definition and WEB Service Data Sources, there are more possibilities. For SQL Query Data Source, the complex logic should be implemented at the level of the SQL Query.
For Data Definition and WEB Service Data Sources
- Use in keyword search: if checked then this column will be used in keyword search, if not the only advanced search is possible. Involving of too many columns in keyword search can cause the slower work of the search, so this field should be used rational.
- Search Display Type: "Yes" - the column is available in advanced search, "No" - the column is not available in advanced search.
- Automatically defined: the condition for the column will be automatically present for the newly created filter, run standard advanced search.
- Search Expression: expression defining the value to be used in search. This field should be set analogously to the "Expression" in Data section of the column depending on Data Source (Data Definition or WEB Operation).
- Sorting: the options below are self-explanatory.
- Sorting Expression: expression defining the value to be used in sorting. This field should be set analogously to the "Expression" in Data section of the column depending on Data Source (Data Definition or WEB Operation).
For SQL Query Data Source, the corresponding sub-form is more primitive. The fields are filled as described for the previous case.
The formatting of the columns in grid is configured here. There is a possibility to create conditional formatting and add images. See also examples of possible configuration of the format in grid.
Configuration of image
- Display Image: it is possible to show in the column instead of the usual text the
or "Image together with the Text": .
All the available images are here https://material.io/icons/
Configuration of colors
- Use Color For:
See the examples for more information depending on the value set in "Use Color For". If several options Cell Text / Cell Background / Row Text / Row Background needed, use trick with invisible column.
- Column Background Color: is selected from color picker. It has a higher priority than the color configuration above.
Data Definition >> Data Query >> Dataset View >> Navigation Item + Grid + Search Filters
This is the most common scenario in the Workspace Management Navigation Item creation. See for example the Changes Navigation Item. It is created on the basis of the Change Request Dataset view Dataset View. It uses the Changes Data Query and represents data from the SPSActivityClassBase Data Definition. A user can create his/her own Navigation Item using this example.
The general information about Data Source option "Data Definition" is here.
WEB Service Data source
See Data Query "Portal Orders" as a nice example of the WEB Service Data Source.
The general information about the Data Source option "WEB Service" is here.
SQL Query Data Source
See, for example, Data Query "Top 10 Most Consumed Services". It uses the following SQL Query:
SELECT TOP 10 serv.ArticleID + ' - ' + serv.Name AS ServiceName, Count(serv.ArticleID) AS Bookings_Count, serv.[Expression-ObjectID] as ObjectId, sot.Name as EntityType FROM SVCServiceBookingClassBase ibook INNER JOIN SPSArticleClassBase serv ON serv.ID = ibook.Service INNER JOIN SPSCommonClassBase com ON com.[Expression-ObjectID] = serv.[Expression-ObjectID] JOIN dbo.SchemaObjectType sot ON com.TypeID = sot.ID WHERE ibook.OrderType = 10 AND ibook.ProvisioningStatus = 1 AND NOT EXISTS (SELECT * FROM SVCServiceBookingClassBase ubook WHERE ubook.OrderType = 20 AND ubook.ProvisioningStatus = 1 AND ubook.InstallationBooking = ibook.ID) GROUP BY serv.ArticleID, serv.Name, serv.[Expression-ObjectID], sot.Name ORDER BY Bookings_Count DESC
This Data Query is used for the following chart on the Service Catalog Landing Page:
For such complicated constructions, it is logical to use an SQL Query Data Source.
General information about the Data Source option "SQL Query" is here.
See the following screenshot showing how to configure formatting in a Data Query for a grid.
Trick with invisible Column
An attentive user can notice that the options Row Text, Row Background, Cell Text, Cell Background, Column Background Color cannot be used at the same time. There is a workaround for this case. A user should create one (or two) more column with the same Expression, make it invisible and configure the "Row" logic in it.
General information about Graphics configuration is here.