Thursday, May 13, 2010

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.

Thursday, March 4, 2010

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.

Tuesday, January 12, 2010

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.

spec.spec_name group_name,
sa.module_name system_name,
'GUI' object_type,
gui.gui_name object,
ass.service_label permission,
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
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