Report Queries to Extract Data - Intergraph Smart 3D - Help - Hexagon PPM

Intergraph Smart 3D Reports

Language
English (United States)
Product
Intergraph Smart 3D
Subproduct
Drawings and Reports
Search by Category
Help
Smart 3D Version
13
SmartPlant Foundation / SDx Version
10

Data for reports can be extracted from the software in a variety of ways:

  • With queries to the Reports database. The Reports database contains unions of views on the other databases (Site, Model, Catalog, Site Schema, and Catalog Schema). Although these queries retrieve data from the model and catalog, they actually query the Reports database instead of going directly to the other databases. Most delivered reports query the Reports database when you run the reports from this task or from the Tools > Run Reports command in other tasks. The Reports database is also used to return data when a report is updated using the Update Document(s) or Update Now command and when the report query is executed through the Edit Template command.

  • With queries containing filters that use bound objects to retrieve data directly from the model databases rather than the Reports database.

  • By using the drawing that contains an embedded report to define the data the report includes.

  • With software written by consultants to gather data for a report.

Regardless of how reports extract data from the software, most reports contain one or more query templates that use either SQL statements or property-based queries to select data to include in the report. The sections that follow describe these two types of queries.

Queries

Queries rely on statements to retrieve data. You can write your own queries or you can customize those delivered with the software to retrieve the information you want. Queries can use filters in combination with statements to select data from the database. A typical query has a select statement that ends with a WHERE clause. The underlying filter software works differently depending on the database you use. For SQL Server, the filter SQL creates a temporary table called @RPTtemptable. You perform a "select oid from @RPTtemptable" to get the oids returned by the filter. In SQL Server, the code follows the example:

SELECT oid,...
FROM ...
JOIN ...
WHERE oid in (select oid from @RPTtemptable)

The oid column name may require qualification, depending on the rest of the statement. However, the SQL variable @RPTtemptable must be used exactly as it is here.

For ORACLE, the query is executed implicitly to return the oids from the filter. You use 'FilterQuery' as a placeholder for the filter generated statement. In ORACLE, the code follows the example:

SELECT oid,...
FROM ...
JOIN ...
WHERE oid in ('FilterQuery')

Queries can require parameters that you must supply when you run the report. To specify parameters for a query, you can do one of the following:

  • Use an existing report template that includes a query with parameters as the basis for your report

  • Add a query that contains parameters to your report using the Tools > Add Query command

If you change the parameters for a report template or add a query with parameters to the template, you can define parameters for an existing report based on that template by right-clicking the report in the Detail View and then clicking Parameters on the shortcut menu.

Excluding Objects from Reports

You can use the Reporting Requirement property to exclude objects from reports. Set this property to Not to be reported for any objects you do not want to include in reports.

The Reporting Requirement property is not case sensitive. If this property is set to Undefined, To be reported, or is left blank, Smart 3D includes the object in reports.

You must also edit the .rqe file so that it does not report the object.

  • If you need to generate a report that includes all of the objects, including the objects set to Not to be Reported, set the argument to blank (Arg=""). Blank is the default condition for this argument.

The following QueryInterpreters work with the NotToBeReported argument:

  • CMATPipingMTO

  • CPipingMTOInterpreter

  • CQueryInterpreter

Check the ProgID of RUN_TIME section of the .rqe file to verify that you are using one of these QueryInterpreters.

If there is no filter tag in the .rqe file, such as in the following example, you cannot use the Reporting Requirement property in this way.

In this case, you can report only those objects set to something other than Not to be reported by appending an SQL query to the SQL tag.

See Also

Create a new report template from an existing template
Report Templates
Edit a Report Template
Report Templates Folder