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.

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, Navdeep Joshi, and the Rego Team for this great material. This article was first published on the CA Community site.  Join the conversation here.