---
title: "Clarity PPM | Learn with Rego: Jaspersoft Relative Date + More"
description: "This week, we answer your questions about Clarity PPM (CA PPM), including SQL, GEL scripts, and Jaspersoft \"Loading.\""
---
[](http://regoconsulting.com)
- [Home](http://regoconsulting.com)
# Clarity PPM | Learn with Rego: Jaspersoft Relative Date + More
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.](https://communities.ca.com/docs/DOC-231169456)
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](https://communities.ca.com/external-link.jspa?url=http%3A%2F%2Fcommunity.jaspersoft.com%2Fwiki%2Fhow-change-start-day-week-relative-dates)
[http://community.jaspersoft.com/questions/803328/relative-dates-daterange-class](https://communities.ca.com/external-link.jspa?url=http%3A%2F%2Fcommunity.jaspersoft.com%2Fquestions%2F803328%2Frelative-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**
****
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.
[http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec1660532.aspx](https://communities.ca.com/external-link.jspa?url=http%3A%2F%2Fwww.ca.com%2Fus%2Fsupport%2Fca-support-online%2Fproduct-content%2Fknowledgebase-articles%2Ftec1660532.aspx)
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,](https://www.linkedin.com/in/davidbzywiec) [Darren Greer,](https://www.linkedin.com/in/darrenrgreer) [Chris Shaffer,](https://www.linkedin.com/in/clshaffer) [Jenn Rinella,](https://www.linkedin.com/in/rinellajenn) [Navdeep Joshi,](https://in.linkedin.com/in/navdeep-joshi-pmp-capm-389a8a9) and [the Rego Team](http://regoconsulting.com/our-company/) for this great material. [This article was first published on the CA Community site. Join the conversation here.](https://communities.ca.com/docs/DOC-231169456)
### **Let Rego Be Your Guide**
Rego also offers [free health assessments](https://regoconsulting.com/clarity-ppm/#health), [webinars,](https://regoconsulting.com/events-ca-ppm/) and [half-day training classes for Clarity PPM](https://regoconsulting.com/free-pm-and-jaspersoft-training/) on Roadmaps in the Modern UX, Power BI, Portfolio Management, and Jaspersoft.
For a full list of Clarity PPM Services, visit [regoconsulting.com](http://regoconsulting.com/).
By [Rego Consulting](https://blog.regoconsulting.com/author/rego-consulting)|August 19, 2016
#### Share on Social Media
- [Tweet](https://twitter.com/share)
### About the Author: [Rego Consulting](https://blog.regoconsulting.com/author/rego-consulting)
As the leading PPM, ITBM, and Work Management services provider, Rego Consulting has helped hundreds of organizations achieve a higher return on their software investment, including 50% of Fortune 100 and 70% of Fortune 20 companies.
### Related Posts
[Permalink](https://blog.regoconsulting.com/4-necessities-pmo-charter)
#### [Clarity PPM | Four Necessities for a PMO Charter](https://blog.regoconsulting.com/4-necessities-pmo-charter)
February 28, 2019 | [0 Comments](https://blog.regoconsulting.com/4-necessities-pmo-charter#comments-listing)
[Permalink](https://blog.regoconsulting.com/whats-new-in-ca-ppm-clarity-15.5-modern-ux-features)
#### [Clarity PPM | Should We Upgrade to 15.5? - Modern UX Features](https://blog.regoconsulting.com/whats-new-in-ca-ppm-clarity-15.5-modern-ux-features)
November 05, 2018 | [0 Comments](https://blog.regoconsulting.com/whats-new-in-ca-ppm-clarity-15.5-modern-ux-features#comments-listing)
[Permalink](https://blog.regoconsulting.com/clarity-early-bird-special-register-by-january-31st-to-save-300)
#### [Clarity PPM | Early Bird Special at RegoUniversity](https://blog.regoconsulting.com/clarity-early-bird-special-register-by-january-31st-to-save-300)
January 24, 2018 | [0 Comments](https://blog.regoconsulting.com/clarity-early-bird-special-register-by-january-31st-to-save-300#comments-listing)
[Permalink](https://blog.regoconsulting.com/the-ppm-podcast-episode-6-davey-zywiec)
#### [Clarity PPM | The PPM Podcast: Davey Zywiec on Reporting Strategies](https://blog.regoconsulting.com/the-ppm-podcast-episode-6-davey-zywiec)
December 20, 2017 | [0 Comments](https://blog.regoconsulting.com/the-ppm-podcast-episode-6-davey-zywiec#comments-listing)
[Permalink](https://blog.regoconsulting.com/ca-ppm-clarity-event-is-a-5-star-experience)
#### [Clarity PPM | 5 Reasons You Can't Miss This PPM Conference](https://blog.regoconsulting.com/ca-ppm-clarity-event-is-a-5-star-experience)
November 21, 2017 | [0 Comments](https://blog.regoconsulting.com/ca-ppm-clarity-event-is-a-5-star-experience#comments-listing)
[Permalink](https://blog.regoconsulting.com/the-ppm-podcast-new-from-rego-consulting-ep2)
#### [Clarity PPM | The PPM Podcast: Tammi Reel-Davis, Montefiore Information Technology](https://blog.regoconsulting.com/the-ppm-podcast-new-from-rego-consulting-ep2)
November 14, 2017 | [0 Comments](https://blog.regoconsulting.com/the-ppm-podcast-new-from-rego-consulting-ep2#comments-listing)
[](https://cta-redirect.hubspot.com/cta/redirect/2652075/655865ab-5c17-4a7d-8626-9917913749d3)
[](https://cta-redirect.hubspot.com/cta/redirect/2652075/80445cd5-d730-4608-a09c-dcaaeff582b4)
### Subscribe to Email Updates
[](http://www.facebook.com/share.php?u=https%3A%2F%2Fblog.regoconsulting.com%2Flearn-ppm-with-rego-jaspersoft-relative-date-remove-the-submit-for-approval-button-sql-a-gel-template-to-get-a-user-for-a-xog-and-jaspersoft-loading%3Futm_medium%3Dsocial%26utm_source%3Dfacebook) [](http://www.linkedin.com/shareArticle?mini=true&url=https%3A%2F%2Fblog.regoconsulting.com%2Flearn-ppm-with-rego-jaspersoft-relative-date-remove-the-submit-for-approval-button-sql-a-gel-template-to-get-a-user-for-a-xog-and-jaspersoft-loading%3Futm_medium%3Dsocial%26utm_source%3Dlinkedin) [](https://twitter.com/intent/tweet?original_referer=https%3A%2F%2Fblog.regoconsulting.com%2Flearn-ppm-with-rego-jaspersoft-relative-date-remove-the-submit-for-approval-button-sql-a-gel-template-to-get-a-user-for-a-xog-and-jaspersoft-loading%3Futm_medium%3Dsocial%26utm_source%3Dtwitter&url=https%3A%2F%2Fblog.regoconsulting.com%2Flearn-ppm-with-rego-jaspersoft-relative-date-remove-the-submit-for-approval-button-sql-a-gel-template-to-get-a-user-for-a-xog-and-jaspersoft-loading%3Futm_medium%3Dsocial%26utm_source%3Dtwitter&source=tweetbutton&text=Clarity%20PPM%20%7C%C2%A0Learn%20with%20Rego%3A%20Jaspersoft%20Relative%20Date%20%2B%20More)
### Recent Rego Articles
### Sign up for our Weekly Newsletter
As the leading PPM, Work Management, and Agile services provider, we have helped hundreds of organizations achieve a higher return on their software investment.
### CONTACT US
Questions? We can help.
[info@regoconsulting.com](mailto:info@regoconsulting.com)
888.813.0444
### Stay up-to-date
Get the latest news in industry best practices, thought leadership, and software updates.
### Solution Capabilities
[Clarity PPM](https://regoconsulting.com/clarity-ppm/)
[Rally](https://regoconsulting.com/rally/)
[Apptio](https://regoconsulting.com/apptio/)
[Sciforma](https://regoconsulting.com/sciforma/)
[Jira](https://regoconsulting.com/jira/)
[Microsoft | SharePoint](https://regoconsulting.com/microsoft/)
[Smartsheet](https://regoconsulting.com/smartsheet/)
[Meisterplan](https://regoconsulting.com/meisterplan-itdesign/)
### Quick Links
[Our Company](https://regoconsulting.com/our-company/)
[RegoUniversity](https://www.regouniversity.com/)
[RegoXchange](http://regoxchange.com/)
[Careers](https://regoconsulting.com/careers/)
Copyright 2022 Rego Consulting Corporation
[Linkedin](https://www.linkedin.com/company/rego-consulting?trk=biz-companies-cym)[Facebook](https://www.facebook.com/RegoConsulting/)[Youtube](https://www.youtube.com/channel/UCdA5xEiDrX--y2nVX2wC1UA?view_as=public)[Twitter](https://twitter.com/regoconsulting)
[Privacy Policy](https://regoconsulting.com/privacy-policy/)
