This document outlines the process of modifying an HQMS report and contains some useful technical information.
- Active Reports editor
- Admin access to HQMS
- SQL Server access
- Web server access
1. Determine the system/module number for the module you wish to report on:
a. Log in to HQMS as an administrative user
b. Right-click in a blank area
c. Select View System XML
d. Look for the name of the module on the left. The number next to it will be the system/module number. This number will be important in the Active Reports editor.
2. Determine the database fieldname when you know the label name in HQMS:
a. View the System XML as instructed above
b. Click the module in question
c. Click in the window on the right pane
d. Search for your label by clicking Edit, Find (example: “Corrective Action #”). You will see a line like this:
Tips in Active Reports:
a. After adding a label to the report, you need to update the “Tag” in the Property ToolBox in this format: ModuleNumber_fieldname. From the example above for the Corrective Action # field, this would be typed in the Tag property: 49_referencenumber. Type the fieldname in lowercase.
b. Text in the Caption property will be displayed on the report when designing it and previewing it. When the report is run thru HQMS, the caption will be replaced with the actual label.
2. Saving Reports
a. Report filenames should not have any spaces. Use the underscore ( _ ) character instead.
3. Previewing Reports
a. If you wish to see a preview of the report in the Active Reports editor, you must build a Connection String in the ADO Object. This will connect the report to the data, but is not actually needed when the report is run thru HQMS. See Building a SQL Connection String below for details.
b. After clicking Preview, type “hqms_admin” (without the quotes), for the UserGUID.
4. Pareto/Grouping Reports
a. A Group Header needs to be added to the report. Set the DataField property to the fieldname that you wish to group on.
b. Make sure to modify the Source statement in the ADO Properties object. The grouping fieldname needs to be placed in this. Here is an example in the Corrective Action system to group by the RootCause field: Exec SP_RPT_PARETO ‘VPQ_ActionCas’,’RootCause’,’Where UserGUID=”<%UserGUID||XXXX%>”’
5. Sub Reports
a. Set the ShowParameterUI of the subreport to False (click in the gray area surrounding the report to display the main property box).
Data connection information in Active Reports:
1. Building a SQL Connection String:
a. Right-click the ADO Object in the report (or add this object) and choose properties.
b. Click the Build button.
c. Choose Microsoft OLE DB Provider for SQL Server on the Provider tab and click Next.
d. Type the name of the SQL server in 1. (the database may be listed in the drop-down)
e. Choose Windows Integrated security or specify a user name and password (your DB admin will tell you what to put here)
f. Select the database in 3.
g. Click Test Connection, then OK, and OK again.
h. Click OK to close the Properties window.
2. Basic select statement for a report (for the ADO object):
a. Select * from VPQ_ActionCAs where UserGUID = ‘<%UserGUID%>’
b. The above statement will prompt for a UserGUID when previewing thru ActiveReports. Please use “hqms_admin” without quotes when prompted.
3. Typical select statement when saving the final version of the report:
a. Select * from VPQ_ActionCAs where UserGUID = ‘<%UserGUID%>’ order by referencenumber
Add the report to HQMS:
1. Copy report file (RPX) into the Reports directory in the HQMS web folder.
2. Modify the TPQ_SYS_Reports table
a. Add a new row to the table
b. Fill in the following fields:
i. Report Title
ii. Report Description
iii. Report File Name (name.rpx)
iv. System ID (get this by viewing the System XML)
v. XML Data – leave blank for now (see step d below)
vi. Report Type
- Detail – 1
- Other types – Null
vii. Unlisted – set to 0
viii. Where Type – set to Null, except set to 1 for a Pareto report
c. Run the query by clicking the exclamation icon at the top (this should populate the GUID field).
d. XML Data – copy and paste the XML data from a similar type of report (if this report was based on another report, copy its
XML data). Note: If unable to copy the XML Data, expand the columns and rows vertically and horizontally.