Dear Community Members,

In this week’s Learn PPM with Rego, we’ll explore five Clarity PPM (CA PPM) questions and answers.

  1. Can we use “Relative Date” in Jaspersoft?
  2. How do we remove the Submit for Approval Button?
  3. Can you help with some SQL?
  4. What’s a GEL template for grabbing a user for a XOG?
  5. 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).



Has anyone implemented “Relative Date” in an Input Control? This BO function doesn’t appear to be in Jaspersoft?


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.



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.


The code is as follows:

/* hides submit for approval */







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



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


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



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.


<gel:parameter default=”xogadmin” var=”username”/>

<core:new className=”” var=”secId”/>

<core:invokeStatic className=”” 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.



Jaspersoft OOTB reports show ‘Loading…’ on the page and never finishes.


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.

About the Author: Rego Consulting

As the leading Strategic Portfolio Management (SPM), Project Portfolio Management (PPM), Technology Business Management (TBM), Agile and expert services provider, Rego Consulting has helped hundreds of organizations achieve a higher return on their software investment, including 60% of Fortune 100 and 70% of Fortune 20 companies.

Share This Story, Choose Your Platform!

Stay up-to-date. Join our newsletter!