Learn with Rego | Jaspersoft Relative Date, Remove the Submit for Approval Button, SQL, GEL Template for Getting a User for a XOG, and Jaspersoft Loading

Document created by navzjoshi00 Champion on Aug 17, 2016Last modified by navzjoshi00 Champion on Feb 3, 2017
Version 3Show Document
  • View in full screen mode

16_08_LWR_05_Community_header.jpg

Dear Community Members,

 

In this week's Learn PPM with Rego, we'll explore five 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.

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 line in red. Change the yellow 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 returnsno 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

 

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, and the Rego Team for this great material.

1 person found this helpful

Attachments

    Outcomes