Intellicus uses a step named “Lookup” at the query object level. With this step, you can perform external field level lookup operations (like geo coding, reverse geocoding, currency conversion etc.) before proceeding to the format step.
You must have a valid lookup type connection to extract data for transformation.
To start transforming data, go to Navigate > Design > Query Object
On the Query Object Designer, you need to do the following:
- Add Lookup step between Data Source and Format after selecting the required table.
- On the Lookup step, select connection from a list of connections whose provider type is LOOKUP.
- Based on the lookup type and platform specified at connection level, you can now specify parameters, and properties required to successfully achieve field lookup.
- For each lookup field, you can specify multiple parameters which are required by REST-URI to get the desired response.
- Each parameter has a parameter name and associated values. Parameter values can also be lookup fields (as suggested by intelli-sense). Example: <%source_ip_address%> as shown in the Figure 2.
The looked up fields would get generated on clicking the Result tab.
Figure 2: Geolocation Lookup
On the Query Object > Lookup step, you need to configure the below properties for geolocation lookup:
|Connection||Select from available lookup connections list||When a connection name is selected, then this lookup service provider fetches data from that connection|
|Lookup Fields||Lists all the fields for lookup||The list of all the fields from the input of lookup step. These fields can be used to perform the lookup operation(s)|
|Parameters||Type yourself||For each lookup field, you can specify parameters which are required by REST URI to get the desired response|
|Request Path||Type yourself||The request path of the REST URI of lookup service provider|
|Record Pattern||Select from list||Click Get Patterns to fetch all the possible record patterns. Choose the fields from the list that you want to perform lookup on|
Specifying Geographic Role
Once you have looked up geographic fields, you can select corresponding geographical roles that are automatically populated (from a configuration file) on the Format step.
If the field list has latitude or longitude or both already available, then Intellicus provides a provision to set Latitude-Longitude format for such fields.
A Configuration file named “gisConf.json” which is used to define the geographical roles (like continent, country, city etc.) should be placed at “<Intellicus installation path>\ReportEngine\config\maps\GIS”
Figure 3: Specifying geographic role at format step
You can now configure the below properties for geographic roles at the Format step:
Format Step Properties
|Field||Name of Field
|Original name of the field|
|In which step did this field originate in the Query Object.
Helps in tracking a field source in a complex Query Object
|Caption||Type yourself||This is name of the field which you can see under the Fields list while designing a report|
Drill Down to another Report
|A field can be made hyperlink to visit a web page or drill down to another report (parameterized report) at run time.
You can hyperlink the field to open a specific URL.
|Group Label||Type yourself or Select from list||To create a new group, type the new group name.
To put this field into an existing group, select group name from the drop-down list.
This helps to categorize fields if the number of fields fetched from SQL is large
|Hidden||Check/Uncheck||Check = makes this field invisible to users for reporting process.
It also hides this field from next Query Object if this Query Object is used as a Data Source
|Geographic Role||Select from list||Select the role like Country, City, State, Latitude, Longitude, etc.|
|GIS Format||Select from list||For Geographic role as either “Latitude” or “Longitude”, you can select its format as one of the below: Signed degrees format, Degrees minutes, Degrees only|
|Width||Number 1-100||Default width of this field when dragged onto a report|
|Output Format||Format String||This field value will be formatted using the format string.
Useful for Date and Number formatting.
If you need to decide the format string at run time, then select “Apply Locale Default” property in the Format String selector dialog
|This field, when put on a report, its values will be aligned to the selected side by default|
|Input Format||Format String||The format string decides the prompting format for the value for this field on Ad hoc Filter screen.
This is useful in prompting date values in desired format.
This is also used to input IP address format for number values
|User Time Zone||Select Time zone||Specify the time zone in which the date data is to be converted and displayed.
The Report Server calculates the difference between Database Time Zone and User Time Zone and does time conversion.
For deciding User Time Zone at run time, select SYS_USER_TZ value
|Render As||Select from list:
|Select Render As Image in case the string field has reference to an image path|
|Width||Type yourself||Width of the rendered image|
|Height||Type yourself||Height of the rendered image|
|Source Type||Select from list:
|The path of the image to be rendered can be a relative system path (example: ../images/intellicus_logo.gif) or an external URL (example: http://www.intellicus.com/Images/Intellicus_Logo.png)|