Setting up Profile Queries
Overview
The Govern Profile Query is a visualization of query results. This visual representation can be in the form of Pie Charts, Graphs of Data, or Data grids. When a dataset is retrieved, the impact upon the end user is increased significantly when the data is presented in a format that is easy for the viewer to visualize. Profile queries are presented as icons that are displayed on the Govern Release 6.0 ribbon. When the user clicks the icon that is used to represent the Profile Query, the query result is displayed. Optionally after the data is displayed, the resulting chart may also be configured to display a report when the user clicks on the image of the chart.
 Control Name |
Ribbon Image |
Description |
| Label |
 |
Displays a user-defined text label in
the Govern ribbon. |
| Large Label |
 |
Unlike the text label, a large text label icon is
displayed in the Govern ribbon.
Descriptive text appears below the larger
text label. |
| Picture |
 |
This is a picture icon that will display a
thumbnail representation of the selected image. |
| Pie Chart |
 |
Based upon the statements that are in the
query, a pie chart is displayed to represent
the data results. |
| Column Chart |
 |
Based upon the statements that are in
the query, a column chart is displayed
to represent the data results. |
| Line Chart |
 |
Based upon the statements that are in
the query, a line chart is displayed to
represent the data results. |
|
|
|
Configuring the Profile Query
Prior to configuring the control in the OpenForms Designer, it will be necessary to design queries to retrieve the data that will then be presented. In addition users must approach this process with the understanding that units that are being retrieved must also be formatted for use in the control.
Queries Tab
The parameters under the Queries tab are used to configure the profiles with queries.
Profile Editor – Queries tab command buttons
Add a New Query: Click this button to add a new Query to the current profile.
Remove Selected Query: To remove the currently selected query from the profile, click Remove Selected Query.
| NOTE: You are able to make multiple or extended selections using the Ctrl or Shift keys in this list. |
Profile Editor – Queries tab parameters
Control Type: Select one of the control types for your query from the drop down menu.
Query: This list will display the English Long Name of the available queries.
When the Open icon is clicked , the SQL Query editor is launched to create a new query; when a query is already configured in the parameter, it will be displayed. Refer to SQL Query Editor for information about creating queries with the Govern SQL Query Editor.
Tool Tip: Enter the text for a Tool Tip that is displayed when the user pointer is hovered over the ribbon icon. This parameter has 3 possible states that can be selected.
- Fix – Select this option to display the current text string as a fixed text description, i.e. tool tip.
- Query – When selected, the parameter will change to a drop down list of queries. The result of the queries can then be used to determine whether the tool tip is displayed.
- Logical Expression – Select this option to display a list of logical expressions that can be used to determine whether the tool tip is displayed.
Visibility: This is a flag that is set to indicate that the icon will be displayed in the Govern Ribbon.
- Fix – Select this option to display the icon as a fixed image.
- Query – When selected, the parameter will change to a drop down list of queries. The result of the queries can then be used to determine whether the icon is displayed. Refer to SQL Query Editor for information about creating queries with the Govern SQL Query Editor.
- Logical Expression – Select this option to display a list of logical Expressions; the visibility of the controls icon will be based upon the result of the expression. Refer to Logical Expression Editor for information about creating Logical Expressions with the Govern Logical Expression Editor.
On Click group
The options in the On Click group when selected will determine what action occurs when the selected query item is clicked in the ribbon. The options are as follows:
None: Selecting None is an indication that no action will be performed when the user clicks on the Profile Query; this is the default.
Open Form: When selected, you will have the option to indicate which is to be opened when the user clicks on the query.
Open Report: Click to select the option to choose a report that will be displayed.
Open View Query: Select the Open View Query option to open a selected View Query.
Execute Command: To execute a command, click this option; available commands can be selected from the list.
Open GIS: When selected, this option will open a window with an embedded GIS output screen.
Open Query Tool: Selecting this option will open the Govern Query Tool (QT).
Open Batch Process: To configure the query to load a batch process, select this option.
| NOTE: Multiple or extended selections using the Ctrl or Shift keys are not allowed in this list. |
Configuring a Profile Query Link
A Govern installation will contain a variety of Profile Queries; these “base queries” should never be modified. When a customized query is required, users can do one of the following after making copies of Govern base queries:
- Users can study the query for an understanding of how they work and then create their own.
- Users can directly modify the copied query to obtain their desired results.
NOTE: In Governs list of base SQL queries, Profile Queries are identified with a dh preceding the name. as in…dhQryNm – QueryName These queries are seen under the SQL Definition List: in the SQL Query Editor.
Depending on the type of Profile Query required, the configuration steps can be simple. The profile query type that can be used to display the results is dependent on the “Statement Keywords” that are used in the query. The following is a list of the type of profile query and the required SQL statement keyword.
| Profile Query Type |
SQL Statement Keywords Required |
| Grid |
No specific statement keyword required. |
| Line Chart |
CHART_TITLE, SERIE_TITLE, CODE, VALUE |
| Column Chart |
CHART_TITLE, SERIE_TITLE, CODE, VALUE |
| Pie Chart |
CHART_TITLE, CODE, VALUE |
When the above statement keywords are present in the query, then Profile Query Type will be valid. If the statement keywords are not present and an incompatible Profile Query Type is selected, the icon will not appear in the Govern ribbon.
Example Profile Queries
The following SQL statements should serve as example of Profile Query types in Govern. Queries are designed in the SQL Query Editor in the Govern New Administration (GNA); see SQL Query Editor for details about creating and editing SQL queries.
Example 1 – Grid
The following is an example of a Profile Query that will produce a Query Grid.
—-START—-
— smPC11
— Property Assessment (Current + Last 4 Year)
— Query Grid
SELECT
MA_MASTER.YEAR_ID ‘Fiscal Year’,
(‘$’ || CAST(ISNULL(MA_MASTER.LAND_VALUE,0) AS VARCHAR)) ‘Land Value’,
(‘$’ || CAST(ISNULL(MA_MASTER.LAND_AG_VALUE,0) AS VARCHAR)) ‘Land Agricultural Value’,
(‘$’ || CAST(ISNULL(MA_MASTER.BLDG_VALUE,0) AS VARCHAR)) ‘Building Value’,
(‘$’ || CAST(ISNULL(MA_MASTER.MISC_VALUE,0) AS VARCHAR)) ‘Miscellaneous Value’,
(‘$’ || CAST(ISNULL(MA_MASTER.CAMA_VALUE,0) AS VARCHAR)) ‘Cost Value’,
(‘$’ || CAST(ISNULL(MA_MASTER.INCOME_GRM_VALUE,0) AS VARCHAR)) ‘Income GRM Value’,
(‘$’ || CAST(ISNULL(MA_MASTER.INCOME_DIR_VALUE,0) AS VARCHAR)) ‘Income DIR Value’,
(‘$’ || CAST(ISNULL(MA_MASTER.COMP_SALES_VALUE,0) AS VARCHAR)) ‘Comp. Sales Value’,
(‘$’ || CAST(ISNULL(MA_MASTER.MRA_VALUE,0) AS VARCHAR)) ‘Market Value’,
(‘$’ || CAST(ISNULL(MA_MASTER.APPRAISED_VALUE,0) AS VARCHAR)) ‘Appraised Value’
FROM MA_MASTER
WHERE MA_MASTER.FROZEN_ID=0
AND MA_MASTER.YEAR_ID>=Year ID-4
AND MA_MASTER.P_ID=Parcel ID
ORDER BY MA_MASTER.YEAR_ID DESC
—-STOP—-
For a Grid type query, when data is retrieved from a column, that will not have a column heading. The query will need to specify the column headings with an alias as illustrated above after the SELECT statement.
NOTE: The above query will only work for a Column Chart or Line Chart type query.
Example 2 – Column Chart or Line Chart
The following is an example of a Profile Query that will produce a Column Chart or Line Chart.
—-START—-
— dhMA01
— Appraised Value by Year
— Column Chart or Line
SELECT (‘Appraised Value by Year (in $ 100K )’) CHART_TITLE,
(‘Appraised Value’) SERIE_TITLE,
MA_MASTER.YEAR_ID CODE,
CAST((SUM(CAST(ISNULL(MA_MASTER.APPRAISED_VALUE,0)AS NUMERIC (20,2))/100000)) AS
NUMERIC(20,2)) VALUE
FROM MA_MASTER
WHERE MA_MASTER.YEAR_ID>=(Year Id-4)
AND MA_MASTER.FROZEN_ID=0
GROUP BY MA_MASTER.YEAR_ID
ORDER BY MA_MASTER.YEAR_ID DESC
—-STOP—-
NOTE: The above query will only work for a Column Chart or Line Chart type query.
For the Column Chart or Line Chart type, as indicated in the grid above, a CHART_TITLE, a SERIE_TITLE, CODE, and VALUE statement keywords need to be
present. In addition the numeric data results should also as a rule be formatted as
well. This is seen in the AS NUMERIC statements.
Example 3 – Pie Chart
The following is an example of a Profile Query that will produce a Pie Chart.
— dhMA02
— Appraised Value By Property Type for the Current Year
— Pie Chart
SELECT (‘Appraised Value By Property Type for the Current Year (in $ 100K )’)
CHART_TITLE,
ISNULL((SELECT VT_USER.SHORT_DESC_EN FROM VT_USER WHERE
VT_USER.TABLE_NAME=’PROPTYPE’ AND
VT_USER.CODE=ISNULL(PC_LEGAL_INFO.PROPERTY_TYPE,’N/A’)),’N/A’) CODE,
CAST((SUM(CAST(ISNULL(MA_MASTER.APPRAISED_VALUE,0)AS NUMERIC (20,2))/100000)) AS
NUMERIC(20,2)) VALUE
FROM MA_MASTER
INNER JOIN PC_LEGAL_INFO
ON MA_MASTER.P_ID=PC_LEGAL_INFO.P_ID
AND MA_MASTER.YEAR_ID=PC_LEGAL_INFO.YEAR_ID
AND MA_MASTER.FROZEN_ID=PC_LEGAL_INFO.FROZEN_ID
WHERE MA_MASTER.FROZEN_ID=0
AND PC_LEGAL_INFO.YEAR_ID=Year Id
GROUP BY PC_LEGAL_INFO.PROPERTY_TYPE ORDER BY PC_LEGAL_INFO.PROPERTY_TYPE
—-STOP—-
NOTE: The above query will only work for a Column Chart or Line Chart type query.
For the Pie Chart type, as indicated in the grid above, a CHART_TITLE, a CODE, and a VALUE statement keywords need to be present. Again, as with the Column Chart and Line Chart types, numeric data results should also be formatted as well. This is seen in the AS NUMERIC statements.
Steps to Configure the Profile Query In order to illustrate the configuration of a profile query, we will first use an existing query.
Steps to Configure the Profile Query
In order to illustrate the configuration of a profile query, we will first use an existing query.
Step 1 – Design and Review the Query
Due to the fact that we will be using an existing Govern base query, no SQL design is required. It is nonetheless recommended that the SQL statements of the base query be reviewed. A review process can provide assurance that the required statement keywords are present for the desired Profile Query. Refer to the SQL Query Editor for details about creating queries.
For the example, the query will display a visual representation of the assessment for the past 5 years from the current fiscal year; a Line Chart profile query will be used.
To review the query…
1. In the Govern New Administration (GNA), select the Editors tab.
2. On the Ribbon select the SQL Query Editor.
3. Under the SQL Definition List pane on the Left Hand Side (LHS), locate the Assessment over the last 5 years query; as a profile query, its name will be preceded with a “dh”. The name used here is as follows, dhPC1 – Assessment over the last 5 years; this title will appear as Assessment over the last 5 years when the profile query is being configured.
SELECT (‘Appraised Value over last 5 years’)CHART_TITLE,’Current Parcel’
SERIE_TITLE,MA_MASTER.YEAR_ID CODE,SUM(ISNULL(MA_MASTER.APPRAISED_VALUE,0)) VALUE
FROM MA_MASTER
WHERE MA_MASTER.YEAR_ID>=(Year Id-4) AND MA_MASTER.FROZEN_ID=0 AND
MA_MASTER.P_ID=Parcel ID
GROUP BY MA_MASTER.YEAR_ID
ORDER BY MA_MASTER.YEAR_ID DESC
| NOTE: As required the CHART_TITLE , SERIE_TITLE, CODE, and VALUE key words are required. |
Step 2 – Configure in the Profile Editor
In the To review the query…
1. In the Govern New Administration (GNA), select the Editors tab.
2. On the Ribbon select the Profile Editor.
3. When the Profile Editor is open, select the Profile that will contain the Profile Query.
For this example select Property Control from the List of Profiles pane on the LHS.
Step 3 – Add the Profile Query to the Profile
As indicated earlier, Profile Queries are configured under the Queries tab of the Profile Editor.
To configure the Profile Query…
1. On the Queries tab, click Add a new query (A); a blank label will appear in the horizontal area directly below the Queries tab (B).
NOTE: This horizontal area can be used as a representation of the Govern Ribbon when the profile is open and a search has been performed.
The type of Profile Query control that we want to use is a Line Chart…
2. For Control Type, click to select Line Chart from the drop down menu.
3. For the Query parameter, select the Profile Query that was designed. For this example, a Govern base query is being used; select the English Long Description Name that appears in the drop down menu list.
| NOTE: If a query needs to be modified or created, click the Open icon to launch the SQL Query Editor. By default if there is a currently selected query, it will be opened. |
Queries are designed in the SQL Query Editor in the Govern New Administration (GNA); see SQL Query Editor for details about creating and editing SQL queries.
4. In the optional Tool Tip field enter a description that will be displayed when the users pointer hovers over the control displayed in the Govern Ribbon; by default the Fix option is selected. Refer to Tool Tip for details on this option.
5. The Visibility option is by default set to True, an indication that the control’s icon will be visible; by default the Fix option is selected. Refer to Visibility for details on this option.
6. In the On Click group, the option of None is selected by default. If a Form, Query, or Report is required to be opened when the Profile Query result screen is opened, then it will be necessary to select one of the options. Refer to the On Click group for option details.
After the changes have been made, note the red dot on the Profile tab, an indication that a modification has been made to the profile.
7. Click Save on the Profile tab, after a brief pause the changes will be saved to the profile.
Step 4 – Review the Profile in Govern
Once the profile has been modified with the addition of the Profile Query controls, the final step is verifying that the changes are visible and functional in Govern.
To view a configured Profile Query in Govern…
1. Open the Govern Release 6.0 application.
2. Under the File menu open the Profile that the Profile Query was configured with.
3. Select the home tab.
4. Use the Predefined Search to perform a search.
5. Load the results into the Dataset Treeview.
When the search results have been loaded into the Dataset Treeview, the Profile Query controls will be displayed in the Ribbon.
| NOTE: A ribbon with the Profile Query icons that have been configured will not be displayed until a Search has been performed. |
Behavior of the Profile Query Control
When the user pointer is hovered over the control, a thumbnail preview of the data result is displayed with the Tool Tip. A click on the Control’s icon will display the data results in a tabbed window.
The display window has two (2) tabs…
Chart: The Chart tab is the default display view. It contains the Profile Query Result. The Chart tab also contains a Print icon.
- Click Print to print the chart.
Results: A click on the Results tab displays the tabulated date results that are used to generate the Chart representation. The Results tab contains a Print icon and a Export to Excel icon.
- Click Print to print the table.
- Click Export to Excel to export the table data as an Microsoft Excel spreadsheet.
| NOTE: In order to be able to use the Microsoft Excel export option, Microsoft Office containing Excel or Microsoft Excel standalone must be installed on the system that Govern is installed on. |
Loading...
