XOG Services: Query API

ccppmop1591
Use the Query API to execute Studio NSQL-based queries from the XOG. The Studio Query is referenced by its code. The response is formatted as record elements. It is often not sufficient to read data only as predefined objects. For example, full object instance data that includes dependencies may be too much detail. Or, you may need data from multiple objects or from database tables that have no defined objects.
The Query API requires a valid license for Studio.
Example: Studio Query
code: sample.getresources
NSQL
:
SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:R.FULL_NAME:[email protected], @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:MR.FULL_NAME:[email protected], @SELECT:METRIC:USER_DEF:IMPLIED:COUNT(*):PROJECT_COUNT:[email protected] FROM SRM_PROJECTS P, SRM_RESOURCES R, SRM_RESOURCES MR, CMN_SEC_USERS U WHERE P.CREATED_BY = U.ID AND U.ID = R.USER_ID AND R.MANAGER_ID = MR.USER_ID AND @[email protected] GROUP BY R.FULL_NAME, MR.FULL_NAME HAVING   @[email protected]
Example: XML Query
<Query xmlns="http://www.niku.com/xog/Query"> <Code>sample.getresources</Code> </Query>
Example: Result
<QueryResult xmlns="http://www.niku.com/xog/Query"> <Code>sample.getresources</Code> <Records> <Record> <rsrc>Administrator, Niku</rsrc> <project_count>178</project_count> <manager>Administrator, Niku</manager> </Record> </Records> </QueryResult>
Query API Root Elements
These are the Query API root elements:
  • Code
    (Required) Identifies the NSQL unique identifier that is defined in Studio.
    Type:
    String
  • Filter
    (Optional) Identifies the NSQL filter columns that are defined in Studio.
    Type:
    User-defined
The Query Filter
The WSDL for queries defines filter parameters in addition to the code identifier, allowing ad-hoc queries that are based on a Studio Query using the XOG. For every column that is selected in the query, you are given multiple filter possibilities.
Example: Exact Match
To filter on a specific value for a column, use the column name directly and pass the value in which you are interested. This example retrieves a single row for resource CorpApp Administrator. Any deviation in the rsrc value of 'Administrator, CorpApp' returns nothing.
Example
<Query xmlns="http://www.niku.com/xog/Query"> <Code>sample.getresources</Code> <Filter> <rsrc>Administrator, CorpApp</rsrc> </Filter> </Query>
Example: Wildcard Query
The wildcard filter behaves like any grid filter field. The filter automatically appends a wildcard asterisk (*) to the end of a value. You can also insert your own asterisk anywhere in the filter string, including at the beginning. This approach is not recommended when filtering large data sets, as performance is severely degraded.
The wildcard filter is available only on columns of type String.
Example: From the sample.getresources
<Query xmlns="http://www.niku.com/xog/Query"> <Code>sample.getresources</Code> <Filter> <rsrc_wildcard>Admin</rsrc_wildcard> </Filter> </Query>
Example
<Query xmlns="http://www.niku.com/xog/Query"> <Code>sample.getresources</Code> <Filter> <rsrc_wildcard>Admin*CorpApp</rsrc_wildcard> </Filter> </Query>
Capture Bounded and Unbounded Ranges
The from and to filters perform a
greater than or equal to
and
less than or equal to
operation on a given value. Use these filters to capture a bounded range or separately for an unbounded range.
Example: From Filter
This unbounded example returns all records with project_count greater than or equal to 1:
<Query xmlns="http://www.niku.com/xog/Query"> <Code>sample.getresources</Code> <Filter> <project_count_from>1</project_count_from> </Filter> </Query>
Example: To Filter
This bounded example returns all records with rsrc values that start with the letters A through E:
<Query xmlns="http://www.niku.com/xog/Query"> <Code>sample.getresources</Code> <Filter> <rsrc_from>A</rsrc_from> <rsrc_to>E</rsrc_to> </Filter> </Query>
Example: Exporting Query Results to a Tab-Delimited Text File
This example uses GEL to execute the NSQL query "xog_query_test", which is the default NSQL query. This example shows how to retrieve the query results and export them to a tab-delimited text file.
For information about using GEL with the XOG, see
GEL Scripting
.
Example
<gel:script xmlns:core="jelly:core" xmlns:xog="http://www.niku.com/xog" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:f="jelly:com.niku.union.gel.FileTagLibrary" xmlns:nikuq="http://www.niku.com/xog/Query" xmlns:util="jelly:util"> <!-- Construct the Query API request for the NSQL query "xog_query_test" --> <gel:parse var="xoginput"> <Query xmlns="http://www.niku.com/xog/Query"> <Code>xog_query_test</Code> </Query> </gel:parse> <soap:invoke endpoint="http://localhost/niku/xog" var="xogresponse"> <soap:message> <soapenv:Envelope> <soapenv:Header> <Auth> <Username>admin</Username> <Password>niku2000</Password> </Auth> </soapenv:Header> <soapenv:Body> <gel:include select="$xoginput"/> </soapenv:Body> </soapenv:Envelope> </soap:message> </soap:invoke> <!-- Extract the sessionID so we may logout later --> <gel:set asString="true" select="$xogresponse/soapenv:Envelope/soapenv:Body/xog:SessionID/text()" var="sessionID"/> <gel:out>SessionID = ${sessionID}</gel:out> <!-- Extract the records --> <gel:set select="$xogresponse/soapenv:Envelope/soapenv:Body/nikuq:QueryResult/nikuq:Records" var="records"/> <gel:set asString="true" select="$records" var="recordstext"/> <gel:out>${recordstext}</gel:out> <!-- Create a tab-delimited file from the results --> <f:writeFile fileName="projectData.txt" delimiter="&#x9;" embedded="true"> <gel:forEach select="$records//nikuq:Record" var="xog_record"> <f:line> <gel:forEach select="$xog_record/*" var="xog_data"> <gel:set var="xog_data" select="$xog_data/text()" asString="true"/> <f:column value="${xog_data}"/> </gel:forEach> </f:line> </gel:forEach> </f:writeFile> <!-- Now log out --> <soap:invoke endpoint="http://localhost/niku/xog" var="logout"> <soap:message> <soapenv:Envelope> <soapenv:Header> <Auth> <xog:SessionID>${sessionID}</xog:SessionID> </Auth> </soapenv:Header> <soapenv:Body> <xog:Logout/> </soapenv:Body> </soapenv:Envelope> </soap:message> </soap:invoke> </gel:script>