What's Wrong with this Query? | Learn with Rego

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

Dear Community Members,

 

We're welcoming in 2017 with a post full of technical goodies. Can you guess what's wrong with the query? Would you like to see all the fields on object views or understand how the Capacity Planning Portlet aggregates demand? It's all here in today's Q&A.

 

And a note before we begin. January 15th is the early bird deadline to join us at regoUniversity 2017 for CA PPM (Clarity) training. With 100+ companies, we dive into Resource, Project, and Portfolio Managementfor all skill levelswhether functional, governance, or technical. The event is April 23-27, 2017 at the beautiful La Cantera Resort and Spa in San Antonio, Texas. Registration materials, course descriptions, and hotel registration is now available at regouniversity.com

We're also excited to announce An Introduction to Application Portfolio Management, our first webinar of the year, is now available for registration. The event is hosted by Jen Scarlato, Senior Solution Consultant at Rego consulting. Every year IT leaders are tasked with driving down costs, being more efficient, and bringing greater value to the organization. "It takes a significant amount of time to manage enterprise-wide applications, department applications, applications brought over with a new acquisition, and those rogue apps," said Jen Scarlato. "This webinar will introduce leaders to APM, show them how an APM strategy can significantly reduce costs, and then provide steps on how to get started." Reserve your seat for January 26, 12:00PM EST.

 

 

Q&A Summary

 

1. Guess what's wrong with this XOG Date query?

 

2. How does the Capacity Planning Portlet work? 

 

3. Is there a Query we can use to see what fields are on Object views?

 

4. Do you have a GEL template for grabbing the user for a XOG, assuming the user doesn't have XOG rights? 

 

5. Anyone know what causes this XOG error

 

Please feel free to comment on any alternative answers you've found.

 

We love your input (always).

 

 

1.

Can you guess what's wrong with this XOG? It isn't populating the date, even though it’s pulling from the query and converting properly for SQL Server.

It’s the exact same format as if I populate it directly through the UI.
<ColumnValue name="survey_date">2016-05-02 00:00:00.000</ColumnValue>

The xog does NOT fail. It simply doesn’t enter the date.

This is what I use to grab the date I need:

Select inv.name,
 inv.code,
       ocp.rego_lls_svy_name,
       CONVERT(VARCHAR,ocp.rego_lls_svy_due,121)
                                
  from inv_investments inv
    join odf_ca_project ocp on inv.id = ocp.id
where inv.id = ?

Answer

 

The convert format for time requires a T between the day and time. Format 126.

 

 

 

2.

How is the Capacity vs Allocation report designed? 

Answer

 

The Capacity Planning Portlet pulls resource OBS or Dept OBS, but it aggregates the demand differently. Capacity is always based on Primary Role where Demand is ‘Investment Role.' So the Capacity Overview is by OBS . . . in other words, who sits within a specific OBS. 

 

 

 

3.

Does anyone have any SQL statements that can help me to see what attributes are on what object views?

I am in the middle of a fun and exciting partition exercise (that’s right, be jealous), and I need to look across the system and identify attributes that are on the edit, create, and filter views.

 

Answer

 

The following page proved to be the easiest to work with and tweak. https://communities.ca.com/message/241821655


 

 

4. 

Does anyone have a GEL template for grabbing the user for a XOG? This is assuming the person triggering the XOG doesn’t have XOG rights and some ‘dedicated’ admin-like account needs to be used.

I have an example that grabs current user, but from what we're building, 90% of the user base won’t have rights to create an instance let alone XOG it in.

Answer 1
  <!-- Get sessionId by username -->
  <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"/>
  <!-- Get a DB Connection to Clarity -->
  <gel:setDataSource dbId="Niku" var="clarityDS"/>
  <core:choose>
    <core:when test="${sessionID == null}">
      <gel:log level="ERROR"> Cannot login to Clarity XOG. Check username and password. </gel:log>
    </core:when>
    <core:otherwise>
      <gel:log>SessionID: ${sessionID}</gel:log>
    </core:otherwise>
  </core:choose>

 

Answer 2
        <!-- Get sessionId by username -->
        <gel:parameter var="username" default="admin"/>
        <core:new className="com.niku.union.security.DefaultSecurityIdentifier" var="secId" />
        <core:invokeStatic var="userSessionCtrl" className="com.niku.union.security.UserSessionControllerFactory" method="getInstance" />
        <core:set var="secId" value="${userSessionCtrl.init(username, secId)}"/>
        <core:set var="XOGUsername" value="${secId.getUserName()}"/>
        <core:set var="sessionID" value="${secId.getSessionId()}"/>
        
        <!-- Checking whether a sessionID is returned.  If not, it means that Login was not successful. -->
        <core:choose>
            <core:when test="${sessionID == null}">
                <gel:log level="ERROR"> Cannot obtain a session id. </gel:log>
            </core:when>
            <core:otherwise>

 

Answer 3

<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"/>

Change "xogadmin" to whatever login you want to use, and you don’t need to use the second-to-last line: <core:set value="${secId.getUserName()}" var="XOGUsername"/>. 

 

This is the preferred method if you are xogging to the same instance. You can’t use this method if you are xogging across instances though.  You have to actually make the soap call to get the session ID. 

 

 

5.

Anyone know what causes this error? I’ve looked at the file.  I don’t have any bad characters, and all attribute code is 20 characters or less.

    <Description>[Error] :0:0: attribute "code" has a bad value: the value does not match the regular expression "[a-z]([a-z0-9_]){1,29}".
                Invalid attribute in input document. Please remove all instances of attribute from document elements and try again.
                [Element : customAttribute, Attribute : code ].
[Error] :0:0: attribute "code" has a bad value: the value does not match the regular expression "[a-z]([a-z0-9_]){1,29}".
                Invalid attribute in input document. Please remove all instances of attribute from document elements and try again.
                [Element : customAttribute, Attribute : code ].
[Error] :0:0: attribute "code" has a bad value: the value does not match the regular expression "[a-z]([a-z0-9_]){1,29}".
                Invalid attribute in input document. Please remove all instances of attribute from document elements and try again.
                [Element : customAttribute, Attribute : code ].
</Description>

Answer
The validation fails and creates this error if you have the code and column name for the attribute in all UPPER_CASE.

 

THANK YOU------------------------------  

A special thanks to Jenn Rinella, Mindy Calderon, Atul Kunkulol, Chris Shaffer, Danny Massimini, Juan Ortega, Rajini Mamidi, Navdeep Joshi, Virginia DeCeglia, Ben Rimmasch, and the Rego Team for this Q/A material.

 

Join a Rego Guide------------------------------   

Half-day classes in Jaspersoft or Portfolio Management are Free and Limited. 

Outcomes