Filter in grid portlet does not display data unless the Show All button is clicked

[sparc.helpdesk]

I have a custom portlet that displays the baseline tasks of projects. Here when I filter provide inputs to the filter section and Filter the data, it does not display any results and show No items to display.

 

My protlet settings initially.

My Portlet results:

But when I click Show All, once the complete data is displayed only then my filter section works.

Also, I do not have any mandatory filter fields.

and the portlet picks data from the MAT VIEW.

 

I have changed the portlet settings to:

Now, all the data is displayed when the user accesses the Portlet Page and it takes around 2 min to display the data.

How can I solve the issue of my filter?? How do I make it work without Show All or Automatically Filter option?

 

Regards,
Soumya P

[David Morton]

Are your filter fields set up correctly?

 

( I'm not quite sure if I follow correctly what you are describing, but if a "filter" doesn't return data but then you press "Show All" and it does return data, then this is normal behaviour when the filter doesn't match any data (since "Show All" is clear-all-filters-then-query ) )

[sparc.helpdesk]

Hi David,

I agree to your statement. But when I directly use the filter without doing a Show All, my portlet does not display any data.

 

Regards,

Soumya P

[Deactivated User]

I think you have it set correctly not to display data until you filter - that is put a value in the filter field and press  .

That way it does not take time to retrieve the data you to do not need.'

 

Display all works, so your query is OK.

 

With a value in the filter field do not get data.

That means either there is not data or

you filter field is wrong.

 

Display the filter field in the portlet to see if you get the same values you would enter in the filter filed.

 

Eg. you project ID field seems to display the code. Do you have the code or the internal ID in the query?

 

If it is the OBS field then check the query how the obs unit and type are associated to the project.

[sparc.helpdesk]

Hi Urmas,

 

1. I have not included the OBS column in my query, may be this is causing an issue.

 

But when I do Show all and then filter data based on project ID or baseline or Project Manager, I get data. Please see below screen shot

 

Filter based on OBS but this is not picked to the column.

Is it necessary to add a column for OBS in the grid as well??

[Deactivated User]

When the portlet and the filter work  there is no need to have other columns than what you have considered necessary..

For troubleshooting you could add the OBS to see if that data is what you are expecting.

As you illustrated the project filter appears to work OK.

[sparc.helpdesk]

The data that is provided is correct.

But this Portlet display data onlyafter I do Show All, later apply the filter.

 

But when I directly provide the data to the filters it shows be No items to display as explained in the beginning of my query. Please see below screen shot.

[David Morton]

If you have a non-displayed filter field with a default value which is excluding your data, then this could also explain your scenario.

(but this is a bit complicated to set up, so I would not think you would have done that 'accidentally')

[Deactivated User]

.That is one possibility

Did you put the OBS field in the portlet?

When you have it then copy a field value to the filter field and press filter and see if you get results

[sparc.helpdesk]

Hi,

 

I have included the OBS field in the portlet as well.

Whatever I do I am not able to see the results when I filter data.

Below is my NSQL:

SELECT
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:LTRIM (obsrp.PATH, 'ALL/'):PROJECT_OBS@,
@SELECT:DIM:USER_DEF:IMPLIED:BASEL:TSK.ID||TSK.TASK_ID||TSK.BASELINE_ID:KEY@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.ID:ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.CODE:PRJ_CODE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.NAME:PRJ_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.ACTIVE:ACTIVE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.MANAGER:MANAGER@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.BASELINE:BASELINE_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.BASELINE_DESCR:BASELINE_DESCR@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.BASELINE_CDATE:CREATED_DATE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.TASK_ID:TASK_ID@,  
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.TASK_NAME:TASK_NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.CURRENT_START:CURRENT_START@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.CURRENT_FINISH:CURRENT_FINISH@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.CURRENT_USAGE:CURRENT_USAGE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.BASELINE_START:BASELINE_START@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.BASELINE_FINISH:BASELINE_FINISH@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.BASELINE_USAGE:BASELINE_USAGE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.DELTA_STARTDATE:DELTA_START@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.DELTA_FINISHDATE:DELTA_FINISH@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BASEL:TSK.DELTA_USAGE:DELTA_USAGE@
FROM Z_R148_MV TSK,
(SELECT record_id AS ID, n.path
          FROM prj_obs_associations a, prj_obs_units_flat f, nbi_dim_obs n         
  WHERE branch_unit_id = NVL(@WHERE:PARAM:USER_DEF:INTEGER:PARAM_obs_id@,5000001)           
  AND a.unit_id = f.unit_id
  AND f.unit_id = n.obs_unit_id
  AND a.table_name = 'SRM_PROJECTS')OBSRP
WHERE OBSRP.ID = TSK.ID
AND (@WHERE:PARAM:USER_DEF:STRING:ACTIVE@ IS NULL OR TSK.ACTIVE=@WHERE:PARAM:USER_DEF:STRING:ACTIVE@)
AND (@WHERE:PARAM:USER_DEF:STRING:CODE@ IS NULL OR TSK.CODE=@WHERE:PARAM:USER_DEF:STRING:CODE@)
AND (@WHERE:PARAM:USER_DEF:INTEGER:MANAGER@ IS NULL OR TSK.MANAGER=@WHERE:PARAM:USER_DEF:INTEGER:MANAGER@)
AND (@WHERE:PARAM:USER_DEF:STRING:BASELINE@ IS NULL OR TSK.BASELINE=@WHERE:PARAM:USER_DEF:STRING:BASELINE@)
AND @FILTER@

 

 

Let me know if anything is wrong here.

 

Regards,
Soumya P

[David Morton]

nbi_dim_obs is a (legacy) datamart table ; if you are not running the datamart jobs it will be empty.

 

Does your query work in SQL only when supplying a "parameter"? - I would start to debug from there rather than in the application. (Obviously we have no idea what your custom Z_R148_MV table contains)

[sparc.helpdesk]

Hi David,

 

We are yet to upgarde our system to higher versions. Currently we are in V14.2 patch 5.

We are still using nbi tables and Datamart job is running on daily basis. When I run the query in backend with parameter it still fetches data without any issues. Only issue is with my Filter section in the portlet.

 

Z_R148 is a mat view to pick the usage details:

CREATE MATERIALIZED VIEW SPATTST1.Z_R148_MV (ID,CODE,NAME,ACTIVE,MANAGER,BASELINE,BASELINE_ID,BASELINE_DESCR,BASELINE_CDATE,TASK_ID,TASK_NAME,CURRENT_START,CURRENT_FINISH,CURRENT_USAGE,BASELINE_START,BASELINE_FINISH,BASELINE_USAGE,DELTA_STARTDATE,DELTA_FINISHDATE,DELTA_USAGE)
TABLESPACE SPARCDAT
PCTUSED    0
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          2M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOCACHE
NOLOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
/* Formatted on 7/17/2017 12:27:54 PM (QP5 v5.185.11230.41888) */
(  SELECT inv.id,
          inv.code,
          inv.name,
          INV.IS_ACTIVE Active,
          CMN.FULL_NAME Manager,
          pb.code baseline,
          PB.ID baseline_id,
          PB.description baseline_descr,
          PB.CREATED_DATE Baseline_Cdate,
          p2.prid task_id,
          p2.prname task_name,
          P2.PRSTART Current_Start,
          P2.PRFINISH Current_finish,
          ROUND (NVL (SUM ( (pass.prestsum + pass.practsum) / 3600), 0), 0)
             current_USAGE,
          --p2.prwbssequence seq,
          PBD.START_DATE baseline_start,
          PBD.FINISH_DATE baseline_finish,
          ROUND ( (PBD.USAGE_SUM / 3600), 0) baseline_usage,
          (pbd.start_date - p2.prstart) delta_startdate,
          (pbd.finish_date - p2.prfinish) delta_finishdate,
          (  (PBD.USAGE_SUM / 3600)
           - (NVL (SUM ( (pass.prestsum + pass.practsum) / 3600), 0)))
             delta_usage
     FROM prassignment pass,
          prtask p2,
          prj_baselines pb,
          prj_baseline_details pbd,
          inv_investments inv,
          cmn_sec_users cmn
    WHERE     prtaskid(+) = p2.prid
          AND p2.prprojectid = inv.id
          --AND p2.pristask = 1
          AND INV.MANAGER_ID = CMN.ID
          AND p2.prid = pbd.object_id(+)
          AND pb.ID = pbd.baseline_id
          AND pbd.object_type = 'TASK'
 -- AND (:param_icode IS NULL OR INV.Code = :param_icode)
 --AND UPPER (pb.NAME) = UPPER (:param_baselinename)
 GROUP BY inv.id,
          inv.code,
          inv.name,
          INV.IS_ACTIVE,
          cmn.full_name,
          pb.code,
          pb.id,
          PB.description,
          PB.CREATED_DATE,
          p2.prid,
          p2.prname,
          P2.PRSTART,
          P2.PRFINISH,
          PBD.USAGE_SUM,
          PBD.START_DATE,
          PBD.FINISH_DATE);

 

Regards,

Soumya P

[David Morton]

If your query runs OK in SQL with a parameter instead of an OBS id, but does not run in the application with an OBS filter selected then I would look at the lookup you have used against the filter field to ensure that it is definitely returning the expected value.