Dear Community Members,
Do you need some extra queries in your tool box or help getting Jaspersoft to recognize your HTML?
We took your questions in today's Q&A.
1. Benefit Plan - Simple Query
2. Crosstab Cell Style (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.
Anyone have a simple query to pull the fields for the benefit plan of record on the project?
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.
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?
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
Here's a query that can help drill down to the usage:
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
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
The query looks in the data dictionary tables for fields that could reference a location. To look for fields that could reference the location, 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.