319
edits
Changes
→the SQL Field
Strings from lookup tables (i.e. ImsmaEnum) should always refer to the key value rather than the translated value.}}
==Special Fields ==In addition to providing access to all of the data fields on a particular object, the iReport data sources provide access to several special fields to access hard to reach data from the IMSMA database. ===The SQL Field===Most iReport data sources have access to a special field called the SQL field to access hard to reach data. This field allows report designers to insert and execute an SQL query to access data from within an iReport template. It's usage requires a knowledge of SQL and the IMSMA database structure and is only recommended for IMSMA administrators with a strong information management background. In most cases the field has the name in the form of: ''[object]''SQLFieldFor example HazardSQLField AccidentSQLField MRESQLField These fields can be added during the [[Create a Basic i-Report Template|iReport Wizard]] process or manually added later. To use the SQL field To use the SQL field to select data from the IMSMA database:# In '''Report Inspector''', choose the desired SQLField (e.g. HazardSQLField) to view it's properties in the '''Properties''' pane.# Edit the name of the SQL field by adding a unique value to the end of the SQL field in parentheses. For example, ''HazardSQLField(testSQL)'' or ''AccidentSQLField(VictimCount)''.# Choose the '''Properties''' setter to open the '''Properties List'''.#: [[Image:IReport SQLField1.png|center|200px|''SQL Field Properties'']]<div align="center">''SQL Field Properties''</div># Choose '''Add''' to create a new property.#: [[Image:IReport SQLField2.png|center|300px|''Create the SQL Property'']]<div align="center">''Create the SQL Property''</div># Set the '''Property Name''' to '''''SQL'''''# Set the '''Property Value''' to the desired SQL statement. #: [[Image:IReport SQLField3.png|center|300px|''Set the SQL Values'']]<div align=Editing "center">''Set the SQL Values''</div># Return to the report and use the new SQL Fieldjust like any other string field. ====SQL Field Examples====The SQL statement can be any valid SQL statement that returns a result. This field can be used to retrieve data that is otherwise difficult to access areas of the database. You can use any valid PostgreSQL statement in the SQL field including* VIEWS, JOINS, HAVING, sub-selects, and sub-queries* PostrgreSQL functions such as COALESCE()* a special parameter called {guid} that passes the value of the {guid} from the report to the SQL statement. This can be used like a regular string to limit the SQL statement according to the object in the report. Examples of SQLField statements include: SELECT hazard_localid FROM hazard WHERE hazard_guid = '{guid}' SELECT COALESCE(sum(qty),0) as QTY� FROM hazreducdeviceinfo� WHERE hazreduc_guid = '{guid}' AND� ordnance_guid IN (SELECT ordnance_guid FROM ordnance WHERE model LIKE '%AP%') ===links.toGuid===
==Sorting and Grouping==