Intellicus Enterprise Reporting and Business Insights 18.1

Report Object

0 views August 26, 2019 0

Query Object

Query objects contain SQL query and list of the fields along with their attributes for using in Ad hoc reports and sometimes in Standard reports.

These are designed from Intellicus web portal at Main Menu -> Repository-> Report Objects -> Query Objects screen.

These objects are stored as XML in Intellicus repository.

The Intellicus web UI provides option for Adding, Deleting and Editing Query Objects.

Almost all of the operations provided by the Intellicus web UI are also available from the Java APIs.

This document intends to explain the Java APIs with Query Object manipulation purpose; there would be sample code files associated with this document.
 

Attribute of Query Objects:

Below table lists all the attributes of query objects which can be modified through Java APIs.

Information Type Description
Name Mandatory Must be Unique
SQL Statement Mandatory The SQL Statement which sources data for this QO.
Connection Name Optional If provided, then this Query object becomes associated to the given connection.
Column Details Optional

As array list.

These are the details of the fields.
Column field Name Mandatory

(if column details are given)

Must be returned by above SQL. Here it is used to match the field and associate below given caption and width attributes to it.
Column data type Mandatory, CHAR/NUMBER/DATE Default – Field data type returned by SQL.

 

Column Caption Optional, String Default – Field name returned by SQL with spaces truncated and Case converted to Title case.
Column Width Mandatory, Integer
Column Hidden Optional, Boolean Default – False
Column Hyper Link Optional, String
Column Key Field Name Optional, String
Column Group Label Optional, String Adds this column to a group, Group label must exist.
Column Alignment Optional Default- Left for Char and Date, Right for Numeric

 

Add Report Object

This Java API is used for creating a new Report Object.

Refer to <Intellicus_Install_Path>/SampleCodes/Java APIs/ReportObjects/ AddReportObject.java for sample code of this use case.

In this API, you would require to provide SQL query to create the Query Object.

You may also provide column details like column name, caption, data type, width, output format and hyperlink.

Refer to attributes table for the list of attributes you can set during creation.

Steps

  1. Initialize Report Client.
  2. Initialize Requestor UserInfo.
  3. Create query object by using the following method:
     
    Method

    Enums.IRO.TYPE.QUERY.QUERY

    Enums.IRO.TYPE.QUERY.PARAMETER

    Enums.IRO.TYPE.QUERY.nFormat

    Enums.IRO.TYPE.QUERY.Format

    Enums.IRO.TYPE.QUERY.nParameter

    Enums.IRO.TYPE.QUERY.nQuery

    Enums.IRO.TYPE.QUERY.nReportObjectType

    public static ReportObject createReportObject (Enums.IRO.TYPE.QUERY) of ReportObject class.

  1. Set the SQL Query – SQL can be set by creating com.impetus.intera.layout.sqleditor.SQLEditor class object and calling setSQL () method.

    SQLEditor sqlEditor = qoObj.getSqlEditor();

    sqlEditor.setSQL(sqlQuery,true);

    Parameters:

    sqlQuery: This is to specify the query string to be used for fetching data.

  1. Set the columns– Columns can be set by passing ArrayList containing the QueryObjectColumn object. (QueryObjectColumn contains column attributes column name, data type caption, and width and others as mentioned in above table).

    public void setColumnDetails (java.util.ArrayList columnDetails)

    Parameters:

    ColumnDetails: This is the array list of columns to be added in the query object.

    Objects of QueryObjectColumn can be created by using the constructor of com.impetus.intera.reportobjects.QueryObjectColumn in which user provides attributes like column name, caption, width and datatype to the columns.

    QueryObjectColumn(java.lang.String columnName,
    		              java.lang.String caption,
    		              java.lang.String width,
    		              java.lang.String dataType)
    String colName = Product;
    String caption = Product Name;
    QueryObjectColumn qoc1 = new QueryObjectColumn(colName, caption ,"","");
    

 

  1. Optionally, You may set other attributes of this column also.<
    // To hide this column

    qoc1.setHidden(true);

    String groupCaption = “Job”;

    //To set this column in a group

    qoc1.setGroupLabelCaption(groupCaption);

    // To set hyperlink for this column.

    String hyperLink = “http://www.google.com”;

    qoc1.setHyperlink(hyperLink);

  1. Set the connection name- setConnectionName method of com.impetus.intera.reportobjects.QueryObject class can be used to set the name of the connection used by Query Object

    public void setConnectionName(java.lang.String connectionName)

    Parameters:

    • ConnectionName: The database connection Name to be used for query object.

    Set if filter is mandatory-This method is used to set the isFilterMandatory.

    public void setIsFilterMandatory(boolean isFilterMandatory)

    of QueryObjects class.

    Parameters:

    • IsFilterMandatory: Whether mandatory filters are applied or not.

    Set the group caption.

    public void setGroupCaption(boolean isGroupCaption)

    of QueryObjects class.

  1. Add the query object. This can be done by calling addReportObject method of ReportObjectManager.]

    public void addReportObject(ReportObject reportObject, UserInfo userInfo).

    Parameters:

    • ReportObject: Java object of Query object to be deleted.
    • UserInfo: Object of UserInfo class.

    Part of Sample Code implementing “Add Report Object”

    ReportObjectManager reportObjectManager = new ReportObjectManager();

    String qoName = “TestQuery”;

    String queryId = “qoObjID”;

    String connName = “ReportDB”;

    qoObj.setName(qoName);

    qoObj.setId(queryId);

    qoObj.setCached(true);

    qoObj.setConnectionType(“Default”);

    qoObj.setConnectionName(connName);

    qoObj.setSource(“SQL”);

    reportObjectManager.addReportObject(qoObj, requestorUserInfo);

Add Query Object from CSV Source

This Java API is used for creating a new Query Object from a CSV source file.

Refer to <Intellicus_Install_Path>/SampleCodes/Java APIs/ReportObjects/ AddReportObject.java for sample code of this use case.

In this API, you would require to provide SQL query to create the Query Object.

You may also provide column details like column name, caption, data type, width, output format and hyperlink.

Refer to attributes table for the list of attributes you can set during creation.

Steps

  1. Initialize Report Client.
  2. Initialize Requestor UserInfo.
  3. Create CSV Source Object

    //CSVSource object

    CSVSource csvSource =new CSVSource();

  1. Set path for the CSV Source

    //Set path of file along with name under connection

    //if connection is created at “E:\csv” and CSV file is in //”E:\csv\subfolder\test.csv”

    //then setpath will be csvSource.setPath(“subfolder/test.csv”);

    csvSource.setPath(“test/deptdetails_test.csv”);

    //Set separator if not a default separator i.e ‘,’

    csvSource.setSeparator(“|”);

  1. Create query object with this CSV Source and set various attributes

    QueryObject qoObj=QueryObject.createQueryObject(“QoName01”, csvSource, “ConnectionFile”, requestorUserInfo);

    //To set the ID for Query Object manually

    qoObj.setId(“QoId01”);

    //Set Category ID in order to create query object in a specific category.

    qoObj.setCategoryId(“Cat1”);

    //To set caching true or false for the Query Object

    qoObj.setCached(true);

    //To set the description for the Query Object

    qoObj.setDescription(“This is a Test Query .”);

  1. Save query Object in Repository

    //Save Query Object

    qoObj.save(Enums.QueryObject.Action.ADD, requestorUserInfo);

Get Query Object by Name

This Java API is used to get a java object representing Query Object by its name.

The object can be used to fetch details and/or then to modify details and replace Query Object in the repository.

Refer to <Intellicus_Install_Path>/SampleCodes/Java APIs/ReportObjects/ GetReportObjectByName.java for sample code of this use case.

Steps:

  1. Initialize Report Client.
  2. Initialize Requestor UserInfo.
  3. Call getReportObjectByName method of impetus.intera.reportobjects.ReportObjectManager by type casting returned object to com.impetus.intera.reportobjects.QueryObject

public ReportObject getReportObjectByName

( String reportObjectType,

String reportObjectName,

UserInfo userInfo)

Parameters

  • Report Object Type: Pass IRO.TYPE.QUERY.
  • Report Object Name: Name of the Query Object to be fetched.
  • User Info: Credentials of user requesting this information.

For getting attributes of Query Object you need to type-cast the Report Object into Query Object.

QueryObject qoObj =
(QueryObject)reportObjectManager.getReportObjectByName(Enums.IRO.TYPE.QUERY, qoName,userInfo);

  1. Call getColumnDetails method on the java object of query object returned. This method will return array list of all the columns.

Method

public java.util.ArrayList getColumnDetails()

Returns:

Returns the array list of columnDetails.

  1. Call getConnectionName method on the java object of query object returned. This method is used to get the name of the connection used by Query Object

public java.lang.String getConnectionName()

Returns:

Returns the name of the connection.

Call isFilterMandatory method on the java object of query object returned.

public boolean isFilterMandatory()

Returns:

Returns whether mandatory filters are applied or not.

Part of Sample Code implementing “get Report Object By Name”

ReportObjectManager reportObjectManager = new ReportObjectManager();

String qoName = “All Country Sales”;

QueryObject qoObj=(QueryObject)reportObjectManager.getReportObjectByName(Enums.IRO.TYPE.QUERY, qoName, requestorUserInfo);

Get Report Object List

This Java API is used to retrieve the list of Report Objects from Intellicus repository.

Refer to <Intellicus_Install_Path>/SampleCodes/Java APIs/ReportObjects/ GetReportObjectList.java for sample code of this use case.

Steps

  1. Initialize Report Client.
  2. Initialize Requestor UserInfo.
  3. Call getReportObjectList of com.impetus.intera.reportobjects.ReportObjectManager to retrieve an array List of Query Objects from the Intellicus repository.

    public java.util.ArrayList getReportObjectList(java.lang.String   roType,UserInfo userInfo)

    Parameters:

    Report Object Type: Pass Enums.IRO.TYPE.QUERY.

    The returned java.util.ArrayList will have each element as a Query Object retrieved from the Intellicus repository.

    Part of Sample Code implementing “get Query Object List”

    ReportObjectManager reportObjectManager = new ReportObjectManager();

    ArrayList reportObjList=new ArrayList();

    ReportObject repObjectQuery=ReportObject.createReportObject(Enums.IRO.TYPE.QUERY);

    reportObjList=reportObjectManager.getReportObjectList(repObjectQuery.getType(), requestorUserInfo);

Other related APIs are:

  • public ArrayList getReportObjectList(String roType, Filter filterObj, UserInfo userInfo)
  • public ArrayList getReportObjects(String roType, boolean cached, UserInfo userInfo)
  • public ArrayList getReportObjects(String roType, UserInfo userInfo)
Get Parameter Object List

This Java API is used to retrieve the list of Query Objects from Intellicus repository.

Refer to <Intellicus_Install_Path>/SampleCodes/Java APIs/ReportObjects/ GetReportObjectList.java for sample code of this use case.

Steps

  1. Call getReportObjectList of com.impetus.intera.reportobjects.ReportObjectManager to retrieve an array List of Query Objects from the Intellicus repository.

    public java.util.ArrayList getReportObjectList(java.lang.String   roType,UserInfo userInfo)

    Parameters:

    Report Object Type: Pass Enums.IRO.TYPE.QUERY.

    The returned java.util.ArrayList will have each element as a Query Object retrieved from the Intellicus repository.

    Part of Sample Code implementing “get Parameter Object List”.

ReportObjectManager reportObjectManager = new ReportObjectManager();

ArrayList reportObjList=new ArrayList();

ReportObject repObjectQuery=ReportObject.createReportObject(Enums.IRO.TYPE. PARAMETER);

reportObjList=reportObjectManager.getReportObjectList(repObjectQuery.getType(),requestorUserInfo);

Delete Report Object

This Java API is to delete an existing Report Object from Intellicus Repository.

For deleting a report object, you would require to provide the name of the Query Object to be deleted. If report object with the given name exists, then that would be deleted, otherwise it gives an error message that “No such Query Object Found”.

Refer to <Intellicus_Install_Path>/SampleCodes/Java APIs/ReportObjects/ DeleteReportObject.java for sample code of this use case.

Steps:

  1. Initialize Report Client.
  2. Initialize Requestor UserInfo.
  3. Get Report Object –Retrieve the Report Object to be deleted from the Intellicus Report repository. For this, call getReportObjectByName() method of com.impetus.intera.reportobjects.ReportObjectManager.

    Public ReportObject getReportObjectByName (Enums.IRO.TYPE.QUERY, String reportObjectName, UserInfo userInfo)

    Parameter:

    • Report Object type: Pass Enums.IRO.TYPE.QUERY for the Query Object.
    • ReportObjectName: The name of the query object to be deleted.
    • UserInfo: Object of UserInfo class.

    //To get the Object for the Query Object Class.

    QueryObject qoObj=(QueryObject)reportObjectManager. getReportObjectByName(Enums.IRO.TYPE.QUERY,qoName,userInfo);

    Delete the Report Object – For deleting the Report Object, call deleteReportObject method of

    com.impetus.intera.reportobjects.ReportObjectManager

    public void deleteReportObject(ReportObject reportObject, UserInfo userInfo)

    Parameters:

    • ReportObject: Java object of Query object to be deleted.
    • UserInfo: Object of UserInfo class.

    //This will used to Delete the Query Object.

    reportObjectManager.deleteReportObject(qoObj,userInfo);

    Part of Sample Code implementing “Delete Report Object”

    ReportObjectManager reportObjectManager = new ReportObjectManager();

    String qoName = “newqo1”;

    QueryObject qoObj=(QueryObject)reportObjectManager.getReportObjectByName(Enums.IRO.TYPE.QUERY, qoName,userInfo);

    reportObjectManager.deleteReportObject(qoObj,requestorUserInfo);

Replace Query Object

Query object can be replaced by calling the replaceReportObject() method of com.impetus.intera.reportobjects.ReportObjectManager.

reportObjectManager.replaceReportObject(qObject,userInfo);

Part of Sample Code implementing “Replace Report Object”

ReportObjectManager reportObjectManager = new ReportObjectManager();

String qOName=”TestReplace”;

QueryObject qObject=(QueryObject)reportObjectManager.getReportObjectByName(Enums.IRO.TYPE.QUERY,qOName,requestorUserInfo);

String colName = “BANKS.BANKID”;

String caption = “BankID”;

String width = “3”;

String dataType = “NUMBER”;

String grpLabelCaption = “Job”;

//create the column to be added and set its attributes.

QueryObjectColumn qocAdd = new QueryObjectColumn(“BANKS.BANKID”,”BankID”,”3″,”NUMBER”);

qocAdd.setAlignment(1);

qocAdd.setHidden(false);

qocAdd.setGroupLabelCaption(grpLabelCaption);

//add this column in the query object

qObject.addColumn(qocAdd);

reportObjectManager.replaceReportObject(qObject,requestorUserInfo);

Other related APIs for accessing QO/PO are:

  1. public LinkedHashMap getAllPOMap()
  2. public LinkedHashMap getAllQOMap()