Clarity

  • 1.  Simple filter logic to return the latest version of status report

    Posted Jul 25, 2012 08:40 PM
    Hi guys. I have created a "status report" subobject in a project for PMs to do their reporting.

    I have built a simple list view portlet to list all the status report subobjects across multiple projects.

    I am looking for a simple way to filter this list to only show the latest report for each project. Date created does not work as people may produce reports at different times.

    Does anyone have some simple filter logic (prefer to avoid queries) something like "version no. = latest" that I could build into a power filter?

    Thanks in advance


  • 2.  RE: Simple filter logic to return the latest version of status report
    Best Answer

    Posted Jul 26, 2012 03:29 AM
    I can't immediately think of a way of doing this in a object based portlet (I assume that is what you have built?).

    It would be easy to do this in a query (NSQL) based portlet, but you would then lose the ability to update data directly in the portlet - if you do not have that requirement then the NSQL portlet is the way to go! :grin:

    --

    (the only way I can think of making it work in an object-based portlet would be to have some process that flags the "latest" report for each project with a boolean value (and unflags all other reports for that project) - then this becomes a simple filter for the portlet. However the complexity of building such a process far outweighs the complexity of building a NSQL based portlet.)


  • 3.  RE: Simple filter logic to return the latest version of status report

    Posted Jul 26, 2012 07:36 AM
    Agree with Dave, NSQL portlet is the most practical solution. We've done something similar using a custom DB function which either gets you the internal ID of the latest status report sub-object instance or the one immediately preceding it.

    Here's the MS-SQL code for the function ( I think it was based on an existing accelerator pack one in fact).
    CREATE FUNCTION [niku].[LATEST_STATUS_RPT_FCT]
    (@p_projectId numeric,
    @p_sequence char(4))
    /* Get the ID of the latest or previous status report entry */
    RETURNS  numeric
    AS
    BEGIN
    
    DECLARE @rptId numeric
    
    
    If upper(@p_sequence) = 'CURR'
    
    
    BEGIN
    
    /* Latest block */
    
    
    
    
    SELECT @rptId = id 
    
    
    
    
    from
    
    
    
    
    
    (select rank() over (order by created_date asc) rank, created_date, id
    
    
    
    
    
    from odf_ca_prog_rep r
    
    
    
    
    
    where r.odf_parent_id = @p_projectId) as x
    
    
    
    
    where rank = (select count(id) from odf_ca_prog_rep where odf_parent_id = @p_projectId)
    
    END
    
    
    
    If upper(@p_sequence) = 'PREV'
    
    Begin
    
    
    SELECT @rptId = id 
    
    
    
    from
    
    
    
    
    (select rank() over (order by created_date asc) rank, created_date, id
    
    
    
    
    from odf_ca_prog_rep r
    
    
    
    
    where r.odf_parent_id = @p_projectId) as x
    
    
    
    where rank = (select count(id) -1 from odf_ca_prog_rep where odf_parent_id = @p_projectId)
    
    
    END
    
    Return @rptId
    
    END
    Then in the portlet NSQL you can call this function in your WHERE clause or use it inside a case statement to evaluate to 1 or 0 then use that value as "Is Latest?" flag.

    Owen


  • 4.  RE: Simple filter logic to return the latest version of status report

    Posted Aug 09, 2012 12:40 PM

    Dave wrote:


    (the only way I can think of making it work in an object-based portlet would be to have some process that flags the "latest" report for each project with a boolean value (and unflags all other reports for that project) - then this becomes a simple filter for the portlet. However the complexity of building such a process far outweighs the complexity of building a NSQL based portlet.)
    Agree with Dave about not using a process to do this.
    Would it be too much to ask the Project Manager to manually flag the latest Status Report (and un-flag the old one) if you are completely against the idea of NSQL - I know how much PM's like extra clicks and steps in their reporting processes, so this should be fine, No!?
    If not this then you could use a Business Objects report and get it to only pick the max created date for each one. That said if you are doing this then you probably making it more complicated than just using your own NSQL in a portlet.

    Last hope would be that if you enforce a common monthly cycle for creating these new status reports across all your projects, could you use the power filter to seek only those entries where the created date falls after the latest status reporting date (or at least greater than the last cycle date?)

    Let us know how you got on...