- Introduction
- Creating the Java DataSource
- The Initialize Method
- The Prepare Method
- The GetFilteredResults method
- The getFieldValue Method
- The Reporting Form
- The Report Object
- Deploying the Report
- The Final Result
- Conclusion
Introduction
Reporting with a Java DataSource is the most powerful and customizable type of reporting. We can pull, modify, and filter data in a variety of ways, bound only by the limitations of Java and SQL. IdentityIQ has a lot of options for reporting and you should verify those other ones won't meet your needs better, creating custom Java has a lot more potential for impactful human error. You can find details on reporting in general, including a list of datasource options, here.
However, sometimes those other methodologies won't meet the requirements and something more customizable is needed. In this post, we are going to explore the main pieces the report that you'll need to have a functioning report.
Creating the Java DataSource
I’ve attached a demo report using a Java DataSource to showcase some functionality, and you can use it as a template/base for your own reporting. Note that this is a Java file, NOT an XML IdentityIQ object, so you can’t import it in the traditional way. One way to quickly deploy changes is to build a clean war file and then pull the compiled .class folder out of the build/extract folder and, after shutting down Tomcat, replace the deployed .class file with the new one.
For the purpose of this example, this post has an attachment which contains a report that joins the Task Definition object to the Task Result object to provide data on both. You can download the attached files and use them in your own environment, modify and test them however you like to see the functionality described in this document.
The first piece of the report we want to examine is the Initialize method of the Java DataSource.
The Initialize Method
This method, excerpted below, pulls from the values entered into the report form (found in the map variable called "arguments") and copies them over to the customQueryOptions map.
if (arguments.containsKey("prioritize")) {
customQueryOptions.put("prioritize", arguments.getString("prioritize"));
}
if(arguments.containsKey("created")) {
Map<String,Long> requestDateRange = (Map<String,Long>) arguments.get("created");
if(requestDateRange.containsKey("start") && requestDateRange.get("start")!=null) {
customQueryOptions.put("createdStart", requestDateRange.get("start").toString());
}
if(requestDateRange.containsKey("end") && requestDateRange.get("end")!=null) {
Date endDate = new Date(requestDateRange.get("end")+86399999);
Long lo = endDate.getTime();
customQueryOptions.put("createdEnd", lo);
}
}In each section we check whether the arguments map has the key for a particular value. If it does, we put it in the customQueryOptions map like we see here. If the field is a date range, we handle the beginning and end of the date range separately. The value for the date range field key will contain a map of the date range with keys "start" and "end", which is inserted into customQueryOptions separately as "createdStart" and "createdEnd".
The Prepare Method
This is where we create our SQL statements and pull the data from the database. Generally, it's good to start by building your base SQL statement, and then adding filter statements to the end.
In the sample report, there are several ways to build this initial base query - Firstly, we check to see whether there is a Custom object in the system which could contain the base SQL query. This can be a good idea to ensure that future changes to the base SQL statement can be made without having to make changes to the Java class, which is a pain to deploy and runs the risk of more damaging human error that takes more time to debug. The Custom object the sample code is looking for is called "Demo Mapping Custom", it will then look for fields in the Custom object called "DemoRight" and "DemoLeft". If these fields or that object do not exist, the report will build the SQL statement using a default. The sample code attached to this document does not include a Custom object, and uses the default SQL statement.
Whether you have or do not have a Custom object, you will eventually end up at code that looks like this:
if((!customQueryOptions.containsKey("prioritize")) || customQueryOptions.get("prioritize").equals("Task Definition"))
{
isTable = "spt_Task_Definition td left join spt_task_result tr on td.id = tr.definition";
pri = "td";
}
else
{
isTable = "spt_Task_Definition td right join spt_task_result tr on td.id = tr.definition";
pri = "tr";
}How this table is built is based off of "prioritize" from the customQueryOptions. This is a Form input wherein the user can choose whether they would like the results to prioritize the Task Definition object or the Task Result object, that is, a Left Join or a Right Join. The pri variable is used to store the prioritized table so that we can ensure shared values (like name) are pulled from that table. This way, when the Join finds an entry in the prioritized table which is not in the other table, it will pull from the table it has.
The cool thing about this is it showcases how Form inputs can be used for more than just plain filtering and that you can get as creative as you like about the options you provide and how you choose to build this query.
After we have our starting SQL statement, filter statements are added based on the Form input data. It looks like this:
if(customQueryOptions.containsKey("createdStart"))
{
System.out.println("in created start sql query filter");
if(b)
{
sql += " where "; // We use where for the first filter, that's what this is for
b = false;
}
else
sql += " and ";
sql += "tr.created >= \'" + customQueryOptions.get("createdStart") + "\'";
}
if(customQueryOptions.containsKey("createdEnd"))
{
System.out.println("in created end sql query filter");
if(b)
{
sql += " where ";
b = false;
}
else
sql += " and ";
sql += "tr.created <= \'" + customQueryOptions.get("createdEnd") + "\'";
}
if(customQueryOptions.containsKey("name"))
{
System.out.println("in name sql query filter");
if(b)
{
sql += " where ";
b = false;
}
else
sql += " and ";
sql += pri + ".name like \'%" + customQueryOptions.get("name") + "%\'";
}
if(customQueryOptions.containsKey("status"))
{
System.out.println("in status sql query filter");
if(b)
{
sql += " where ";
b = false;
}
else
sql += " and ";
sql += "tr.completion_status = \'" + customQueryOptions.get("status") + "\'";
}The main things we want to be careful of is ensuring the first filter statement leads with "where" and all ones after lead with "and". Also, notice that for the date objects operators are used which compare the Long dates with those found on the table, and "name" is set up to return any result where the name contains the searched upon value using the "like" operator and wildcards, rather than a strict equality. Usually there are fields, like name, where this is almost always preferable. In this use case, many Task Result objects are set up to automatically rename by appending numbers at the end when a new version of a Task is run, and this way one can filter upon the base name and retrieve all of those results.
Once we have our SQL statement, the Prepare method is also where the query is executed, and the results are stored in a ResultSet object called rs and sent to the getFilteredResults method.
The GetFilteredResults method
This method provides an extra layer of customization options.
It's basically a handoff. We start with the results in ResultSet rs and the end in a List of Lists, resultList. Every element of resultList is an ArrayList which represents one row of results pulled back from the SQL query
These rows are stored individually in record before being added to resultList. This is a great spot to do final tweaks on some of this data - You can add an additional column and use it for something you calculate from the data, as one example. In the sample report, this is used as an opportunity to do some processing of the messages field, which is stored as a longtext field on the table. Sometimes these cause issues either by being difficult to digest or having characters that create unexpected issues later. In this case, the code removes three characters which can cause issues, converts the whole field to a String, and inserts it into resultList.
while (rs.next()) {
/*
This iterates through every row on the results returned from the SQL query. If there's any last
minute data manipulation you want to do after pulling the results and before you ship, this is the
place to do it. This is the lowest performance place to make these kinds of changes, so only touch
it if there's literally no other way. If you get a requirement that forces you to use this method,
it's reason enough to reconsider whether you actually need that requirement.
For this example, I'm manipulating data from a longtext value, which sometimes contains stuff that
isn't valid and will fail to return to the report. Just generally you sometimes want to massage
these unstructured fields a bit
*/
String[] record = new String[columns];
for (int i = 1; i < columns+1; i++) {
String data = "";
record[i - 1] = rs.getString(i);
if(i == 5) // The messages field; It is 6th in the constructed SQL query, which makes it item 5
{
Clob clob = rs.getClob(i);
if(clob != null)
{
Reader is = clob.getCharacterStream();
StringBuffer sb = new StringBuffer();
int length = (int) clob.length();
if(length > 3000)
length = 3000;
if (length > 0)
{
char[] buffer = new char[length];
int count = 0;
try{
while ((count = is.read(buffer)) != -1)
{
sb.append(buffer);
}
data = new String(sb);
} catch(Exception e)
{}
}
/*
Later, in the getFieldValue method, having these characters in the result can cause
us some problems, so we're removing them. We can do other modifications too, whatever
kind of formatting we want to do. Clobs are usually really unwieldy data so
whatever it takes to make them human-readable, now is a good time
*/
data = data.replace("<","");
data = data.replace(">","");
data = data.replace("/","");
record[i-1] = data;
}
else
{
record[i - 1] = rs.getString(i);
}
}
resultList.add(record);
}
}This method is the most performance-impacting place to make these kinds of changes - If you can do whatever you need to do through the SQL statement or the getFieldValue method, you should.
The getFieldValue Method
This is what's called by the report to actually get the values for the individual fields, one row and field at a time. This is your very, very last opportunity to make modifications before the user sees the results. This is, for example, where we generally convert the created field to a human-readable format, as shown below:
else if (fieldName.equals("created")) {
DateFormat forma = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss a");
Date d = new Date();
if(currentRow[0] != null)
{
long milliSeconds= Long.parseLong((String) currentRow[0]);
d.setTime(milliSeconds);
String dateString = forma.format(d);
return dateString;
}
return "";
} currentRow is the row we are examining and returning to the report at this moment. When we built our base SQL statement, we requested the "created" field first, so that is stored in currentRow[0].
The Reporting Form
The Form is an XML object with which we're going to retrieve the report parameters. In the page where we customize the report, the Form will be on the page named after the section label, in our case this is "Request Parameters" (you can define multiple sections with different names). The values entered here are passed to the initialize method of the Datasource in the "arguments" variable. Some example fields from our sample form are:
<Field columnSpan="1" displayName="Prioritize" helpKey="prioritize" name="prioritize" type="string" value="ref:prioritize">
<AllowedValues>
<String>Task Definition</String>
<String>Task Result</String>
</AllowedValues>
</Field>
<Field columnSpan="1" displayName="Created Date" helpKey="create_dt_start_use" name="created" type="string" value="ref:created">
<Attributes>
<Map>
<entry key="xtype" value="daterange"/>
</Map>
</Attributes>
</Field>
</Section>You'll notice each field has, as the value, "ref:[field name]". This actually refers to the Argument in the TaskDefinition object where the entry is stored, which I'll discuss in greater length in the Report Object section. In general, though, this Form should look very similar to other Forms in IdentityIQ, which you can learn all the tips and tricks for using the Forms document.
The actual Form the user will see for the sample, with this code, looks like this:
The Report Object
This XML object is the actual report TaskDefinition object. It includes a lot of pieces which, though simpler-looking than the Java Datasource, glue the whole thing together.
In the example, a LiveReport is defined in the Attributes map which looks like this:
<LiveReport title="Demo Task Report">
<DataSource dataSourceClass="sailpoint.reporting.reports.DemoTaskReport" type="Java"/>
<ReportForm>
<Reference class="sailpoint.object.Form" name="Demo Task Report Form"/>
</ReportForm>
<Columns>
<ReportColumnConfig field="name" header="Task Name" sortable="true" width="110"/>
<ReportColumnConfig field="type" header="Task Type" sortable="true" width="110"/>
<ReportColumnConfig field="created" header="Created Date" sortable="true" width="110"/>
<ReportColumnConfig field="status" header="Completion Status" sortable="true" width="110"/>
<ReportColumnConfig field="runlength" header="Run Time" sortable="true" width="110"/>
<ReportColumnConfig field="messages" header="Messages" sortable="true" width="110"/>
</Columns>
</LiveReport>The DataSource field is used to define what and where the datasource actually is. So, the java class (including its path starting at the src folder in your build) and the type (Java).
The ReportForm is the Form object you're using for
The ReportColumnConfig fields include the header, which is what the column will be called when displayed to the user, as well as the actual field name (called field) which is passed to the getFieldValue part of the Datasource. Where it checks what the fieldName equals, it refers to this value.
Another essential aspect to a functioning report is the Arguments, which look like this:
<Argument name="created" type="String">
<Description>date bulk request created</Description>
</Argument>This is what's being referred to in the value field in the Form. If you are missing either this or the ref: statement in the form, the entry data you're trying to filter on will not be saved or passed to the Datasource.
Deploying the Report
The XML objects will go where XML objects go, that is, in the Config folder. The Java file, however, will go into the src folder on the build, which is also the root for the class path that is referenced in the TaskDefinition object. So this line:
<DataSource dataSourceClass="sailpoint.reporting.reports.DemoTaskReport" type="Java"/>Means that the .java file can be found in the build in src/sailpoint/reporting/reports/DemoTaskReport.java
The build process compiles this into a .class file, and it will be deployed with the identityiq.war file on the app servers.
The Final Result
If this is all set up, the sample report looks like this:
Conclusion
So, to recap, the Form gets the parameters which are passed to the Java DataSource to pull the query results, which are used to build the final display values in the Report object. This is far more customizable than other ways of creating a report, and can help meet more unusual reporting requirements.
