Clarity PPM | A Simple Query

Do you need some extra queries in your tool box or help getting Jaspersoft to recognize your HTML? We took three of your questions in today's Q&A.

And before we jump in, remember it's your last chance to sign up for Clarity PPM (CA PPM) Training at RegoUniversity. The conference is entirely dedicated to developing your best-practice skill set for Project and Portfolio Management in Clarity PPM. Registration closes July 1st, 2019.

We're also excited about our March Webinar: ANNUAL PLANNING | Challenges and Opportunities for the Modern IT Organization. Save your seat for March 15th, 2017 @ 12:00PM EST.

And now it's time for the Learn with Rego Weekly Q&A. Thanks to those who submitted questions.

Learn with Rego Q&A

1. Benefit Plan - Simple Query

2. Crosstab Cell Style (Jaspersoft Reading Text vs HTML)

3. Unable to Delete Location

Please feel free to comment on any alternative answers you've found. At Rego, we always love your input. 

1.

Anyone have a simple query to pull the fields for the benefit plan of record on the project?

Answer

 FROM  inv_investments i
       JOIN odf_ca_project ocp ON i.id = ocp.id
       JOIN fin_plans fp ON fp.objectid = i.id
          AND fp.is_plan_of_record = 1
          AND fp.plan_type_code = 'FORECAST'
       JOIN fin_plans bp ON bp.id = fp.benefit_plan_id

In my case, for my "from,"  I just needed the description field for the benefit plan.

2.

I have a report where I've used a SQL Aggregate function to concatenate Team Resources (roles included). I have concatenated HTML tags, so only the Roles show highlighted.

The part where Jasper is not working as I expected it to is the display. It considers the whole field as text, instead of considering the HTML text as HTML.

I set the markup to HTML and tried the same thing with the Styled option too.

Has anyone successfully tried this before?

Answer

If you're using the - RTRIM (XMLAGG  - function to aggregate, it will convert the field into text, so the tags are considered text too.

Try replacing it with ListAgg. Works like a charm. (Note: we have tags in the SQL rather than Jasper Variable + Style.)

3.

I am not able to delete a location. The error is as follows:

The location is tied to a department, but when I try to delete the association, I get the following error:

Here's the error in the log file:

I think this is the bottleneck, but I haven't found a solution.

1.    None of the projects are using the location

2.    None of the resources are using the location

3.    The location is not used in any of the financial transactions or financial plans

Answer

In the case of the user above, we found the location in Finance > Setup > Defaults.

Below is a query that looks in the data dictionary tables for fields that could reference a location. Just replace the “myLocationID” with the ID of the location you’re trying to delete. You can execute the queries in the vSQL column to see if any records actually reference your location.

 SELECT TC.TABLE_NAME, TC.COLUMN_NAME
 , 'SELECT ''' || TC.TABLE_NAME || ''' TABLE_NAME, ''' || TC.COLUMN_NAME || ''' COLUMN_NAME, COUNT(*) CNT FROM ' || TC.TABLE_NAME || ' WHERE ' || TC.COLUMN_NAME || ' = ''myLocationID'' UNION ALL' vSQL
 FROM USER_TAB_COLUMNS TC
 WHERE 1=1
 AND (TC.COLUMN_NAME LIKE '%LOCN%' OR TC.COLUMN_NAME LIKE '%LOCATION%')
 AND TC.TABLE_NAME NOT LIKE '%_V' AND TC.TABLE_NAME NOT LIKE '%_V2'
 AND TC.TABLE_NAME NOT LIKE 'NBI_%' AND TC.TABLE_NAME NOT LIKE 'TEMP_%' AND TC.TABLE_NAME NOT LIKE 'Z_%'
 ORDER BY TC.TABLE_NAME, TC.COLUMN_NAME

Thank You------------------------------ 

A special thank you to Virginia DeCeglia, Vikram Baddela, Atul Kunkulol, Jenn Rinella, David Matzdorf, Sankhadeep Dhar, and the regoUniversity Team for this Q/A material.