Clarity

Expand all | Collapse all

CA Tuesday Tip: Hierarchical Grid Portlets - part 1

  • 1.  CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted Mar 29, 2011 08:24 PM
    CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 3/29/2011

    Today we will be starting a several week exploration into Hierarchical Grid porlets. This suggestion came in from Steve VanArsdale. Thanks Steve!

    We will start our "journey" with a very simple example which will enable us to show a list of projects with their tasks under them. With each task and project there will be a start and finish date. You will be able to "expand" each investment to view the list of tasks underneath. Here is how it is done.


    Master Object to Sub-Object Relationship Hierarchical Grid Query Example:
    ---------------------------------------------------------------------------------------------

    1. Define an NSQL with the following characteristics:

    a. The query will need to be a union of 2 queries that contain the same set of data types for their display properties.
    b. In this case we are just using the investment start and finish dates, as well as the task start and finish dates
    c. A property will need to be defined with the name "hg_has_children".
    (This naming convention is required)

    i.e. @SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.hg_has_children:hg_has_children@

    d. You will then need to define the actual query, which will select rows from the master object and subobjects tables.
    e. The master object will need to specify some value for hg_has_children.

    i.e. id hg_has_children

    f. The sub-object will need to specify null for the hg_has_children field

    i.e. null hg_has_children

    g. Each of the "union-ed" queries will specify a where clause.
    h. The master query's where clause will determine master records when hg_row_id is null.

    i.e. where @where:param:user_def:string:hg_row_id@ is null

    i. The sub-object's query where clause will need to map the sub-object to the parent using the parameter from hg_row_id@.

    i.e. where prprojectid = @where:param:user_def:string:hg_row_id@


    2. A fully formed solution to the requirements is as follows.

    SELECT
    @SELECT:DIM:USER_DEF:IMPLIED:OBJ:hgquery.NAME:Name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.start:Start@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.finish:Finish@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.hg_has_children:hg_has_children@
    from (
    select name, schedule_start start, schedule_finish finish,id hg_has_children
    from inv_investments
    where @where:param:user_def:string:hg_row_id@ is null

    union

    select prname, prstart start, prfinish finish, null hg_has_children
    from prtask
    where prprojectid = @where:param:user_def:string:hg_row_id@

    )hgquery
    where 1=1 and @filter@


    Next week, we will cover the steps to get this query into a portlet. (it is actually really easy to do and with most of the defaults you can get the query to display data. ;) )


    -Shawn Moore


  • 2.  RE: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

     
    Posted Mar 30, 2011 11:25 AM
    Great tip Shawn and thank you Steve VanArsdale for the suggestion!

    Chris


  • 3.  RE: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted Mar 31, 2011 08:57 AM
    Thanks for the tip, I'll see if I can find partII


  • 4.  RE: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted Mar 31, 2011 01:42 PM
    especially cute with the smiley faces :)
    emoticons can be great except when you are trying to use actual syntax

    got a laugh out of seeing that face throughout

    thanks for the great information as always!!!!


  • 5.  RE: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted Apr 04, 2011 07:55 PM
    Yeah, those smiley's can get distracting! :mad:

    Part II - will continue next week (I'm going to post something about Oracle tonight)

    -shawn


  • 6.  RE: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted Apr 08, 2011 06:31 AM
    Hi Shawn,

    Can we use this Hierarchy Horizontally I mean in two dimensional Portlet? Because currently we are displaying data in the months and user need to scroll horizontally to see his required month. Instead is there any possibility with this hierarchical query to have quarters in X axis and when I expand the quarter it should show months.

    Thanks in Advance


  • 7.  RE: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted Apr 12, 2011 07:46 PM
    Thats a good question. Are you using a time scaled field?


  • 8.  RE: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted Apr 13, 2011 08:39 AM
    Yes Shawn.I am displaying the various data columns for the months and even I have Quarter column in my table.Please explain me How can I display the date looks like
    +Q1||-Q2
    ___April|May|June

    Is it possible to have Hierarachical data horizantallly?

    Thanks


  • 9.  RE: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted Apr 18, 2011 06:24 PM
    chakr07,

    If I'm understanding correctly.....

    You won't be able to expand and contract horizontally. You could add columns that represent the timescale and alter the timescale settings. Based on your sketching, you probably could expand you timescaling a hierarchy, but not in both dimensions.

    Here are the rules according to the Studio Developer's Guide (12.1.0, page 184)


    Hierarchical Grid Portlets
    A hierarchical grid portlet can be used to show the structure of data that has more than one level. A parent row can have multiple child rows, and the data that shows in the parent row can be an aggregate of the child data.

    Things to Consider When Creating Grid Portlets
    When creating grids, consider the following:

    ■ If your grid contains virtual columns, the source data for those columns must be defined in the query that supplies data to the grid. In the following example the values used to create a total row of data (budget, actual, and allocated) must be provided by the query.
    ■ The y-axis (rows) of the grid can contain only one dimension of data.
    ■ If your query data contains two dimensions, either dimension be displayed in the rows. The second dimension is displayed on the x-axis (columns). Queries that contain three or more dimensions display one dimension in the rows and all other dimensions in the columns.
    ■ Metrics always display in columns.
    ■ Metrics display in the first or last header row of columns when the query data contains two or more dimensions. If the query contains three or more dimensions, the dimensions are placed in order along the x-axis (see example next page).

    -shawn


  • 10.  RE: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted Apr 19, 2011 11:32 AM
      |   view attached
    Finally managed to catch up with you fellows.
    ITW's need at the moment are internal "5million" numbers on Tasks and Projects.
    So our hierarchy grid for this exercise is Projects and Tasks.
    We got the query to work and the portlet built.
    When the user expands the Project, the Tasks appear. Thanks!
    However the column "hg_has_children" which contains the Project 5-number contains only a square on the Task.
    Is there a way to get the Task internal ID number to appear, the same as the Project?

    (example attached)


  • 11.  RE: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted Apr 19, 2011 11:38 AM
    ^ isn't that just a case of choosing to display the "task id" column (or whatever) rather that the "hg_has_children" column in your portlet output?


  • 12.  RE: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted Apr 20, 2011 04:02 PM
    How can we aggregate the data at the parent record level?... please explain


  • 13.  RE: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted Apr 26, 2011 11:18 AM
    Not certain, Dave. The task ID appears in the fourth column, of course, under the shared hierarchy name "id", so the portlet is operational.

    In any case, it works for us at ITW, resolving an obstacle to setting up XOG XML files for financial transactions, namely, getting the 5-million internal-ID number for tasks.

    Here's the NSQL query, if it helps any participants to this thread.
    SELECT
    @SELECT:DIM:USER_DEF:IMPLIED:BJ:hgquery.NAME:Name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:BJ:hgquery.ID:id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:BJ:hgquery.externalid:externalid@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:BJ:hgquery.start:Start@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:BJ:hgquery.finish:Finish@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:BJ:hgquery.hg_has_children:hg_has_children@
    from (
    select name, id id, schedule_start start, schedule_finish finish,
    id hg_has_children, code externalID
    from inv_investments
    where @where:param:user_def:string:hg_row_id@ is null
    union
    select prname, prid ID, prstart start, prfinish finish,
    null hg_has_children, prexternalid externalID
    from prtask
    where prprojectid = @where:param:user_def:string:hg_row_id@
    ) hgquery
    where 1=1 and @filter@


  • 14.  RE: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted Apr 26, 2011 11:34 AM

    steveVanArsdale wrote:

    Not certain, Dave. The task ID appears in the fourth column, of course, under the shared hierarchy name "id", so the portlet is operational
    Yes, exactly my point (must admit I didn't scroll across to see that column originally! :*) )

    So if you move that fourth column to be the first column in the list column view, how does that render to the screen?

    (Sorry; I might be misunderstanding your original Q about how to see the task id in that first column - all I was really saying was "just put it there then"? :wink: )


  • 15.  RE: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted Apr 28, 2011 10:43 AM
      |   view attached
    Hi Steve,

    From your post (of 4/19/11) I feel that you want to see the 5million task ids also when you expand on the project name.
    Please see the attached file, hope this is the output you are expecting...

    Best Regards,
    Namita Mishra


  • 16.  RE: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted Apr 29, 2011 09:36 AM
    Again, not certain, Namita.
    Can you post the query that created that output?


  • 17.  RE: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted May 06, 2011 01:46 AM

    steveVanArsdale wrote:



    However the column "hg_has_children" which contains the Project 5-number contains only a square on the Task.
    Is there a way to get the Task internal ID number to appear, the same as the Project?
    The special attribute hg_has_children MUST behave the way you see in your example OR you will get a plus symbol on evey task row (if this special attrbute is null then you don't get the plus symbol displayed).

    So, you need to use a different attribute to display the 5 million number in your portlet, typically this would simply be the "ID".


  • 18.  RE: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted May 10, 2011 05:48 PM
    Good point and answer.

    In my part II of the Hierarchical Grid Portlets, I created the portlet similar to some out of box ones. This 2nd part is a much better design and can be used to implement multiple levels. B)


  • 19.  Re: RE: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted Jul 18, 2014 06:43 PM

    Hi Shawn thanks for Share but what about if you want to get the programs, proyects and tasks? should You use another Union? could you give an example?

     

    thanks...



  • 20.  Re: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted Jul 21, 2014 10:12 AM

    Have you checked this ?

     

    Multi Level Hierarchical Portlet

     

    NJ



  • 21.  Re: CA Tuesday Tip: Hierarchical Grid Portlets - part 1

    Posted Jul 21, 2014 10:13 AM

    This is the recent discussion started by Rob

     

    Hierarchical Program Portlet

     

    NJ