Forms Missing from Report Builder

My client recently upgraded to the IBM TRIRIGA 3.5.1.3 platform.  After the upgrade I found that certain forms were no longer available for selection in the Report Manager.  Looking at Form Builder, you can clearly see that there are nine forms on the triPeople business object:

Switching to Report Manager only five of them show as available to add to a new report.

It turns out that when more than one form with the same label exists for the same business object, only one of them will display on the report form.

Workflow Instances and Associations

Have you ever wanted to take a peek at the Work Flow Instance tab or Associations tab on a single tab form, or on a form where they are not set to display?  Here is an easy solution -  pop open a new browser tab and paste the URL from below, updating your server name and port, and replacing the x's with the spec ID of the record.

URL to view workflow instances (where "xxxxx" is the spec ID for the record):

http://YourServer:Port#/html/en/default/wfBuilder/wftInstanceListForRecord.jsp?SOId=xxxxx

URL to view associations:

http://YourServer:Port#/html/en/default/visualization/svgassociation/SvgAssociation.jsp?specId=xxxxx

Action Items Not Displaying

Here's an issue that I've seen a few times - the Reminders portal section shows a count of one or more action items assigned to a user, but when you drill into the Action Items portal section no action items display.  The solution is to be sure that any workflows with User Action or Approval tasks use an associated My Profile record for the assignment, not a triPeople record.  The out-of-box Action Items portal section (sysActionItems) only shows action items associated to a My Profile record.

Security Group Permissions Report

If you need to get a handle on your security groups I have a Crystal Report that displays your security details in a viewer-friendly table format.  The data is set via SQL command, so the where clause at the end of the SQL can be modified to limit the date returned as needed.  You can download the report here.

Undocumented Steps for Using BIRT

In order to use BIRT for report development in TRIRIGA, there are a few undocumented steps you will need to take to make it work. Start off by installing BIRT and the IBM TRIRIGA plug-in, using the Report User Guide for as a reference. You will then need to set the IBM TRIRIGA BIRT Preferences.

In the BIRT application menu, go to Window/Preferences and find the IBM TRIRIGA BIRT Preferences. Set the value for your TRIRIGA App Server to "http://servername:8001/remote", and include your username and password. Once this is done, click the "Test Connection" button and verify that you get a "Connected to TRIRIGA" message.

Another important step is to check the value set for FRONT_END_SERVER in your tririgaweb.properties file. This will typically be "appserver:8001", but may vary in your environment. If this value is not set correctly, the report will not display correctly in the application, you will probably see something that looks like the screenshot below.

Finally, once you have your first report built and packaged as a zip file (as documented in the Reporting User Guide), you will need to modify the archive file before uploading it to document manager. In order for TRIRIGA to read the files they need to be in the root of the zip file, as exported the zip file will not work. The easiest way to do this is copy the files out of zip file, delete any directories in the zip file and copy them back to the root of the zip file. If you don't do this, the report will display properly when you preview it in BIRT, but when you run it from the application you will get an error message.

Impact of Changing List Values

A couple of weeks ago I wrote about determining the impact of changing classification values so you can judge the impact of the change before you make a data change you will later regret. Similar to determining the impact of changing classification values, you can also determine the impact of changing list values to determine where else in the application a particular list is being used.

To determine which objects use a specific list, run the following SQL script (substituting the name of the list field you are looking for for the value in ATR_NAME below) :

select name from IBS_SPEC_TYPE

where SPEC_TEMPLATE_ID in (select SPEC_TEMPLATE_ID from IBS_SPEC_VALUE_META_DATA

where ATR_TYPE = 'List'

and ATR_NAME = 'triTaxPaidToLI')

The results of this query will show which business objects have a field (with the name specified in ATR_NAME above) that points to that list. Using this, you can go into the Data Modeler for each object, select the field that uses the list and click on 'Where Used'. This will pop up a window showing all GUI's, queries and workflows where the field is used. Pay special attention to workflows that show 'Workflow Condition' in the Action column - this will help identify workflows that may use specific classification values in their logic.

Impact of Changing Classification Values

You may find your users want you to remove/replace some classification values, but before you make the requested changes you need to determine if that classification is used anywhere else in the product and what the impact of changing it will be.

To determine which objects use a specific classification run the following SQL script (substituting the name of the root classification you are looking for for the value in red below) :

select name

from IBS_SPEC_TYPE

where spec_template_id in

(select spec_template_id from ibs_spec_value_meta_data

where atr_type = 'Classification' and

classification_root_name = 'Location Primary Use')

The results of this query will show which objects have a field that points to that classification root value. Using this, you can go into the Data Modeler for each object, select the field that uses the classification and click on 'Where Used'. This will pop up a window showing all GUI's, queries and workflows where the field is used. Pay special attention to workflows that show 'Workflow Condition' in the Action column - this will help identify workflows that may use specific classification values in their logic.

Locating the Module for a Business Object

If you've ever known the name of the business object you were looking for, but could not figure out which module it is located in and didn't feel like searching every module in the GUI builder, below is a script to help you out. The IBS_SPEC_TYPE table provides a list of all business objects and the IBS_MODULE table give a list of all modules, so a simple SQL script can join these two tables and give you your answer. For a list of all objects and modules use this:

select O.NAME, M.MODULE_NAME

from IBS_SPEC_TYPE o, IBS_MODULE m

where O.SPEC_CLASS_TYPE = M.MODULE_ID

order by M.MODULE_NAME, O.NAME

If you just want to get the details for a specific object you can use the script below, substituting the value in red for the name of the BO you are looking for.

select O.NAME, M.MODULE_NAME

from IBS_SPEC_TYPE o, IBS_MODULE m

where O.SPEC_CLASS_TYPE = M.MODULE_ID

and O.NAME = 'triREPaymentAdjustment'

order by M.MODULE_NAME, O.NAME

Crystal RAS on a 64 bit Server

I ran into a situation recently where I experienced some difficulty getting Crystal RAS 2008 to work properly when it was installed on a Windows Server 2008 R2 (x64). The first issue appeared when we installed the ODBC drivers but they did not appear in the ODBC Data Source Administrator. It turns out that in 64 bit versions of Windows there are actually two ODBC Data Source Administrators, one for 64 bit drivers and one for 32 bit drivers, however only the 64 bit Administrator is available in the Control Panel/Administrative Tools and the drivers we were using were 32 bit. To access the 32 bit administrator, we needed to manually find and run the following file: C:\Windows\SysWOW64\odbcad32.exe . Now we could see the ODBC drivers we installed.

Once we figured out why the ODBC drivers we not showing up in the ODBC Data Source Administrator and worked through that, our next issue appeared. We were able to create System DSN’s (using the Oracle driver) and verify that they were set up correctly using the “Test Connection” feature, but for some reason every time we ran a report we would get an error message: Failed to open the connection. Details: [Database Vendor Code: 12154]. This error message translated into ORA-12154: TNS could not resolve the connect identifier specified.

After some extensive research, it turns out that this was a fairly common issue for people using 32 bit ODBC drivers on a 64 bit Windows OS. I actually confirmed the same error message (ORA-12154) by installing Crystal Designer on the server and running a report directly from Crystal using the same ODBC connection (thereby bypassing the RAS server). As it turns out, the parentheses in the default file location, C:\Program Files (x86), cause problems with the ODBC drivers. All we had to do to fix the problem was install RAS in a directory that did not contain parentheses – I put it in C:\CrystalReports. Finally after much aggravation, RAS was working.

External Mail with Attachments

I recently had a request from a client to add an action to Contact Center that emails the 'Requested By' person a PDF explaining how to use the ESS application. The same basic method can be used to email just about any file to anyone with a People record in TRIRGA (or even someone who does not have a people record if you create a custom GUI to enter the email address).

Anyhow, here are the basic steps:

1. In the Application Setup manager there is a business object called Offline Content. Add a new record in the BO, giving it a Name and ID and attaching the file you want to send to the Offline Content field
2. Create a new query that returns only the offline content record you just created - this will be used in the workflow below
3. Create a new workflow that first runs the query above to get the content and then retrieves the person you want to email.
4. Once you have a handle on the content and person (or at least their email address), add a Create task to create a Mail/Email Message and map (or hard code) the Body and Subject fields.
5. Next, add a Create task to create a Mail/Email Address record that will be your From address (you must have a To and From email address). Map or hard code the Address and Name fields
6. Add two Associate tasks, both using the string 'Email From Address', to associate the From address to the Email Message
7. Repeat steps 5 & 6 for a To address, this time using the string 'Email To Address' for your Associate tasks
8. Now, add a Create task to create a Mail/Email Attachment. In this step map to the Content field from the triOfflineContentBI field on the Offline Content record (from the Query task in step 3)
9. Add one more Associate task the associates the Email Message to the Email Attachment using the string 'Email Attachment'
10. Finally, use a Trigger Action task to trigger SEND on the Email Message

An decent example of sending an email message with binary content exists out of the box in the current TRIRIGA application. Refer to the triRETransactionPlan - Synchronous - Send Offline Transaction Template workfow in the Project module for reference.

Extracting Security Data

If you need the details on how your security groups are set up, below is some SQL to extract that data. The query below only pulls the security at the GUI level, but that is the level that contains the most information. If you only want a summary of read/write/delete access add ass.service_id > 50 to the where clause. That will prevent GUI and section action access from displaying. Here is a link to a Crystal report using this query to put the data in a more user friendly format.

SELECT
spec.spec_name group_name,
sa.module_name system_name,
'GUI' object_type,
gui.gui_name object,
ass.service_label permission,
ass.service_id
FROM app_object_permission aop
JOIN ibs_module sa ON
sa.module_id = aop.application_id
JOIN application_service ass ON
ass.service_id = aop.service_id
JOIN ibs_spec spec ON
spec.spec_id = aop.group_id
JOIN gui_header gui ON
gui.gui_id = aop.template_id
WHERE
spec.spec_name != 'Admin Group' AND
aop.tab_id = -1 AND
aop.section_id = -1 AND
aop.field_id = -1
ORDER BY 1, 2, 3, 4, 6

Setting Up Bulk Print Functionality

If you have a need to bulk print multiple objects, such as work tasks, you can use the following process to create a bulk print utility.

1. Create a Crystal report that works successfully on the Print Preview tab
2. Create an object/GUI to use for bulk printing
3. Include a query section on the bulk print GUI that allows you to find and associated tasks using a unique association string
4. Write a query that gets the record id of the tasks with an association filter using the string you defined above to tasks (i.e., get all tasks associated to the bulk print object with a string of ‘has task’ or whatever you defined)
5. Create a URL section action on the bulk print GUI similar to this: /html/en/default/common/RepBuilder.jsp?source=bulk&docId=XXXXXX&showFilters=false&queryName=triTask_triWorkTask_cus___triWorkTask___Selected___Tasks___Bulk___Print
6. Replace the value after ‘docId=’ with the Document ID of the Crystal report (if you mouse over the doc in Document Manger you will see the Doc ID in the bottom left corner of your screen)
7. Replace the value after ‘queryName=’ with the name of your filter query (look at the format above it Module_Object_Query Name with a triple underscore for spaces between words in the query name)
8. You should be ready for testing at this point, associate one or more work tasks to the bulk print GUI and call the action you defined above.

Monitoring Java Memory

If you find your app server running out of Java heap space, or just want to monitor it to look for issues, running the Java Monitoring and Management console is a relatively easy method to accomplish this. To set this up, you will need to add the following to the startup properties for the JVM, typically the run.bat (or run.sh) file:

-Dcom.sun.management.jmxremote.port=6778
-Dcom.sun.management.jmxremote.authenticate=false
-Dcom.sun.management.jmxremote.ssl=false

This starts the JVM with a monitoring server on port 6778 (you can pick any port you want, just change the number above). Then, from your desktop (you must have the JDK 1.5+ installed, not just the JVM) you would run the following at a command prompt:

java -jar jconsole.jar appserver:6778

Where "appserver" is the hostname or IP address of the app server. If jconsole isn't in your path, you should be able to find it in the "bin" directory under your JDK installation. That should pop up a handy console window where you can monitor various statistics on the server. One thing to watch for is "PS Old Gen" completely filling up, as this will make your TRIRIGA server non-responsive.

If you don't have the JDK installed (but have a JVM) you can still access the JConsole by just downloading the jconsole.jar file to your local system and running the above command from the same directory.

Thanks to Eric Glass for this tip!

Using Google Charts in TRIRIGA

A customer asked a coworker of mine to embed some dashboard-style graphs on his portal to look something like this:
You can do pretty much exactly that with the Google Charts API; here is an example:

http://chart.apis.google.com/chart?chs=225x125&cht=gom&chtt=European%20Affairs&chl=72&chd=t:72

This will render a PNG image with the following parameters:

chs: chart size, e.g. 225x125
cht: chart type, here "gom" = Google-O-Meter
chtt: chart title
chl: label at end of arrow, here I just used the data value
chd: chart data, here "t:72" gives a value of 72

You can embed this in an HTML document by doing something like:

<img src="http://chart.apis.google.com/chart?chs=225x125&cht=gom&chtt=European%20Affairs&chl=72&chd=t:72"/>

More information is here:
http://code.google.com/apis/chart/basics.html

Many thanks to Eric Glass for this cool solution!

Concurrent License Usage

As part of an effort to monitor maximum concurrent license usage, a colleague of mine wrote a SQL script to query ACTIVE_SESSION_USER joined to T_LICENCEDETAILS. He then created a batch script to run the the SQL script with SQLplus and another batch script to concatenate the results. The results were dumped to a .csv file every five minutes. Once the sample data had been gathered, he pulled it into Excel for analysis and created this nifty chart:


The SQL used to gather the data behind the chart is:

column dcol new_value mydate noprint
select to_char(sysdate,'YYYYMMDDHH24MISS') dcol from dual;
set echo off
set feedback off
set linesize 100
set pagesize 0
set sqlprompt ''
set trimspool on
spool c:\temp\concurrency\&mydate._output.csv
select to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS')||','||b.username||','||b.peoplefullname||','||c.userlicence
from tridata9.active_session_user a, tridata9.t_myprofile b, tridata9.ibs_spec_assignments isa, tridata9.t_licencedetails c
where a.user_id = b.spec_id
and b.spec_id = isa.spec_id
and isa.ass_spec_id = c.spec_id;
spool off
exit

Thanks Chris for your contribution!

Workflow Instance Settings

With the 2.6 platform there are now four possible settings for saving workflow instance data. You can always save instance data, never save it, or save it based on the "Save Workflow Instances" setting in each individual workflow either always or only when running in production mode. The choice is dictated by the value of WF_INSTANCE_SAVE tririgaweb.properties file. The options are:

• ALWAYS - always save all instance data
• NEVER - never save any instance data
• PER_WORKFLOW_ALWAYS - save instance based on the workflow "Save Workflow Instance" setting in each individual workflow
• PER_WORKFLOW_PRODUCTION - save instance based on the workflow "Save Workflow Instance" setting in each individual workflow only when in production mode. If ProductionMode = N then workflow instances will always be saved
  

Form Reports with Multi-Line Smart Sections

I found some odd behavior when creating a form report displaying multi-line smart section rows. I created the table but only the first cell of each row was getting populated with data, none of the additional columns displayed any data.

Here is an example of the html code I used in the table:

<tbody>
    <tr>
      <td>Work Task ID</td>
      <td>Task Name</td>
    </tr>
    <tr>
      <td>%%triWorkTasks_triIdTX%%</td>
      <td>%%triWorkTasks_triNameTX%%</td>
    </tr>
</tbody>

The workaround is to add a space after the for each cell containing the %% tags, so it looks like this: . After doing that the data displayed as expected.

Here is the fixed version of my example:

<tbody>
    <tr>
      <td>Work Task ID</td>
      <td>Task Name</td>
    </tr>
    <tr>
      <td >%%triWorkTasks_triIdTX%%</td>
      <td >%%triWorkTasks_triNameTX%%</td>
    </tr>
</tbody>

Finding the Parent Path

If you have ever needed to hierarchical extract data from an 8i system to put in a data integrator template for import into a TRIRIGA 9 system, you probably know about the challenge of pulling the parent path. The following SQL statement can be used to extract this data (this example is for Space Class Current):

select a.spec_id, a.sys_parent1, a.classname,
(select b.fullpath from tridata.t_spaceclasscurrent b where b.spec_id = a.sys_parent1) "PARENTPATH"
from tridata.t_spaceclasscurrent a
order by a.fullpath

Business Connect

If you are using Business Connect for integration development and would would like a tool that can log into your application server, show all of the available methods and allow you to manually execute them, you may want to consider SoapSonar by Crosscheck Networks (http://www.crosschecknet.com/download/download.php). They offer both an enterprise edition (with a free trial) and a free personal edition that will provide access to the Business Connect WSDL Methods.

To use the product, enter your WSDL location (i.e., http://yourServerName:8001/ws/TririgaWS?wsdl) and click on the Capture WSDL button. You will see a screen that looks like this:

To execute a method use the tree on the left to drill into it. Once the method is selected look at the schema fields on the request, enter any required data (depending on the method you are calling), set the authentication to use your API user, and click execute.

In the example below, I executed getModules which has no required input. The screen show shows the response from the server.

Add a Documents Tab to a Custom Object

Adding a Documents tab to a custom object is a very simple process. Here’s what you do:
• In the Data Modeler, create an association between your object and the Document module, Document object using an association string of Has Document with a reverse string of Is Document For
• Revise and republish the business object
• Open the GUI Builder and add a xxDocuments tab
• Under your new tab create a query section called xxRelatedDocuments using Document - Display - Associated to Current Record as the query, setting the Association Type to Has Document
• Re-label the DeAssociate action to Remove
• Add a new action called Upload, check the Pop Up box, set the Action Type to Custom and enter this URL: /html/en/default/docmgmt/objectupload/objectFrameSet.jsp?folderId=-1&showFolders=true&multiFile=true
• Publish your GUI