Dear Community Members,
In this week's Learn PPM with Rego, we'll explore five Clarity PPM (CA PPM) questions and answers.
- Can we use "Relative Date" in Jaspersoft?
- How do we remove the Submit for Approval Button?
- Can you help with some SQL?
- What's a GEL template for grabbing a user for a XOG?
- Have you seen the Jaspersoft Loading. . . (never loads) with some OOTB reports?
Please feel free to comment on any alternative answers you've found within the CA Community here.
We love your input (always).
------------------------------
1
Has anyone implemented "Relative Date" in an Input Control? This BO function doesn’t appear to be in Jaspersoft?
Answer
There is relative date in Jaspersoft. On the date it has a small tool tip saying if you put ‘WEEK’ it will get the relative date. Here’s a few articles for reference.
http://community.jaspersoft.com/wiki/how-change-start-day-week-relative-dates
http://community.jaspersoft.com/questions/803328/relative-dates-daterange-class
------------------------------
2
Can the “Submit for Approval” button be removed using UI Themes? I heard it could, but I can't find out how to do it.
Answer
The code is as follows:
/* hides submit for approval */
button[onclick*="submitForm('page','pma.ideaPropertiesSubmitForApproval','odf_name=idea'"]{
display:none;
}
------------------------------
3
Can you help with some SQL? We have a set of lookups that all have 5 possible answers (or none).
I need to average those across each completed object, so I’m taking the average of each one individually and summing the total. The filter is the project ID and survey completion, which can return more than one row. Basically I need something like the following for 10 separate questions.
SELECT '01. Responsibilities were clearly identified' ,
SUM(CASE WHEN SQ1= 1 THEN 0.2/Count(1)
WHEN SQ1= 2 THEN 0.4/Count(1)
WHEN SQ1= 3 THEN 0.6/Count(1)
WHEN SQ1= 4 THEN 0.8/Count(1)
WHEN SQ1= 5 THEN 1.0/Count(1)
ELSE 0
END) avg_sq1
FROM odf_ca_rego_prj_survey svy
WHERE project_id = :param_projid
AND survey_completed = 1
Answer
The following will give you a union of the rows and average over the row:
SELECT '12. Project milestones were met as expected' question,
svy.project_id project_id,
SUM(SQ12)/COUNT(1) avg_sq
FROM odf_ca_rego_prj_survey svy
WHERE svy.project_id = :param_projid
AND svy.survey_completed = 1
GROUP BY svy.project_id
UNION
SELECT '13. Results of this project will meet the business need' question,
svy.project_id project_id,
SUM(SQ13)/COUNT(1) avg_sq
FROM odf_ca_rego_prj_survey svy
WHERE svy.project_id = :param_projid
AND svy.survey_completed = 1
GROUP BY svy.project_id
------------------------------
4
Does anyone have a GEL template for grabbing the user for a XOG? . . . assuming the person triggering the XOG doesn’t have xog rights and some ‘dedicated’ admin–ish account needs to be used.
Answer
<gel:parameter default="xogadmin" var="username"/>
<core:new className="com.niku.union.security.DefaultSecurityIdentifier" var="secId"/>
<core:invokeStatic className="com.niku.union.security.UserSessionControllerFactory" method="getInstance" var="userSessionCtrl"/>
<core:set value="${userSessionCtrl.init(username, secId)}" var="secId"/>
<core:set value="${secId.getUserName()}" var="XOGUsername"/>
<core:set value="${secId.getSessionId()}" var="sessionID"/>
You don’t need the second-to-last line (bolded). Change the bolded xogadmin to whichever login you want to use. This is the preferred method if you are xogging to the same instance. You can’t use this method if you are xogging cross instances. You have to actually make the soap call to get the session ID.
------------------------------
5
Jaspersoft OOTB reports show 'Loading...' on the page and never finishes.
Answer
This known defect, labeled CLRT-78902, affects reports that include a pie chart for Risks or Issues by Priority, including the following five reports:
Project Storyboard
Project Status Summary
Project Risk, Issue and Change Summary
KPIs by Project Type (Dashboard)
Top 50 Project Watchlist
Good news. There are two work-arounds.
Here's the first. Check out the tech document from CA: TEC1660532. It asks you to run a report that returns no results. For example, choose your project and select status = cancelled. Then when you run the report and loading appears, you can click cancel, and the report will stay on the screen. A caveat, if your results return more than one page, sometimes the page scroll button doesn't appear.
Next, try taking the pie charts off the report (the charts for Risks and/or Issues by Priority). To remove them, we recommend copying the OOTB report, editing it in JasperStudio by just deleting the two charts, and then publishing again.
Note: This issue only happens for people with no data in Issues & Risks for the Project Storyboard report. When there is data, and the issue and risk charts can populate, the Project Storyboard report runs fine, as do the other reports.
------------------------------
*A special thanks to David Zywiec, Darren Greer, Chris Shaffer, Jenn Rinella, Navdeep Joshi, and the Rego Team for this great material. This article was first published on the CA Community site. Join the conversation here.
Let Rego Be Your Guide
Rego also offers free health assessments, webinars, and half-day training classes for Clarity PPM on Roadmaps in the Modern UX, Power BI, Portfolio Management, and Jaspersoft.
For a full list of Clarity PPM Services, visit regoconsulting.com.
