AnsweredAssumed Answered

Filter in grid portlet not working

Question asked by matej256 on Jun 7, 2016
Latest reply on Jun 8, 2016 by urmas

Hi All,

 

I'm struggling with creating a grid portlet with some filters. I thought everything is fine, but I'm still getting no results.

Could you please point me where I'm wrong?

 

Purpose: Create custom portlet with filtering "start" and "end date" for days when resources have tracked their hours against their projects. It's filtered only for 1 vendor which is set in the portlet by default. I've re-used our DB view which contains pretty all the same data we need (except 2 columns) so the final query is quite easy... ID - DIM in the NSQL is created in the view by Resource ID and Project ID, so should be unique...

 

Query in SQL developer:

 

 

Then NSQL in the Clarity query:

SELECT    
         @SELECT:DIM:USER_DEF:IMPLIED:project:RV.ID:ID@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:RV.TRANSDATE:DAY_OF_TRACKED_HOURS@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:RV.IS_CODE:IS_ORDER@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:RV.MANAGER_NAME:MANAGER@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:RV.MANAGER_NAME_2:MANAGER2@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:RV.RESOURCE_ID:RESOURCE_ID@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:RV.FIRST_NAME:FIRST_NAME@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:RV.LAST_NAME:LAST_NAME@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:RV.PRIMARY_ROLE:PRIMARY_ROLE@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:RV.INPUTTYPECODE:INPUT_TYPE_CODE@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:RV.BILLRATE:COST_RATE@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:RV.VENDOR:VENDOR_CODE@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:RV.VENDOR_DESC:VENDOR_DESCRIPTION@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:RV.PROJECT_NAME:PROJECT_NAME@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:RV.HOURS:HOURS@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:RV.COST_CENTER:COST_CENTER@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:RV.RESOURCE_CLASS:MANPOWER_CATEGORY@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:ODF.HEN_PROJECT_TYPE:ORDER_TYPE@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:project:DEP.SHORTDESC:PROJECT_DEPARTMENT@


 FROM z_hen_alltimes_base_v rv 
   inner join odf_ca_project odf on odf.id = rv.project_id
   inner join pac_mnt_projects pac on pac.id = odf.id 
   inner join departments dep on dep.departcode = pac.departcode
WHERE rv.transdate BETWEEN @WHERE:PARAM:USER_DEF:DATE:start_date@ 
                       AND @WHERE:PARAM:USER_DEF:DATE:end_date@
AND rv.vendor_desc = @WHERE:PARAM:USER_DEF:STRING:vendor_filter@   
AND @FILTER@



Note: rv.transdate originally coming from prb_slice table as slice_date.

  

Portlet shows no data for me:

 

Has someone any idea why? I'm quite annoyed of this "easy" thing...

 

Thanks a lot for your inputs!

 

Matej

Outcomes