Filter in grid portlet not working

[matej256]

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

[Davor Tot]

Hi Matej,

 

What happens if you only have @filter@ in your where clause?

 

Davor

[matej256]

Hi Davor,

 

Nothing, I've had only @FILTER@ before in my NSQL but filtering showed also any results....

 

Matej

[Davor Tot]

Hi Matej,

 

How about access to the DB view?

Are you using same DB user for Clarity and your SQL developer?

 

I have also seen different results from SQL developer and Clarity before and this was usually because different date format for these users...

 

Davor

[navzjoshi00]

Can you try filtering the Vendor in CAPS ?

 

NJ

[matej256]

Hi Davor,

 

I'm still using niku admin in Clarity and also in SQL developer....

Moreover I think the problem could be in different format when in param star/end date its like MM/DD/YYY (5/1/2016),

while transdate has DD-MON-YY (19-MAY-16) format. Is it automatically handled by NSQL or should be set manually?

 

@Navzjoshi: How? Vendor is picked up by browse vendors lookup....

 

Matej

[Sridhar Bandaru]

Is 'param_vendor_filter' associated to any lookup? If so, can you verify the hidden key of the lookup, is it expecting ID or name?

[matej256]

Hi Sridhar,

 

Good point I think... when I've checked Vendor lookup, hidden key is code so I should rather compare vendor codes than description (name). Now it's like that (hope correct):

 

Lookup:

Param vendor filter (no change):

 

Changed to vendor (as vendor_code) (there was vendor_desc before):

 

Then my portlet produced interesting result

     a) there should be more columns for the date range (according to my result in SQL developer)

     b) vendor param in filter disappeared - I cannot filter anymore there

     c) As I got know from other forum topics this error which is shown means that IDs (DIM property) is not unique, but I'm sure it            has to be, as it's used as ID in the view....Or can it be another reason there?

 

Thanks

 

Matej

[Sridhar Bandaru]

From the SQL results you have shown earlier,I can observe duplicate IDs.

[Davor Tot]

Hi Matej

 

Use rownum just to be sure...

 

Davor

[Deactivated User]

In the NSQL you have

rv.vendor_desc

and the value in in your SQL is

Ranstadt Duesseldorf

and in the portlet image it appears to me that there is no space between the two words. Is that the so?

 

Normally  what I should do is put the filter in the NSQL to use the ID.

Then use a lookup which displays the Name (or description) to select and then passes the id of the selected Vendor to the portlet. Which relates to the same as sridhar_bandaru writes above

That way it makes the selection easy and no errors due to typing.

[Deactivated User]

So you solved that problem while I was typing.

Try to set the filter not to read only.

 

As you can see in the SQL developer there are several rows for the same ID.  Either use the row number or add something to the ID to create an unique ID like the date converted to a a string.

 

Apparently your BETWEEN is not working as desired, an option is to split it  and use greater than start and less than end (or end plus 1)

[matej256]

Hi Urmas,

 

After complications with vendor filter (also disappearing and also has to be read only) I considered the better way to put vendor code in the NSQL code because this portlet should be only used for one vendor...

 

ID - DIM, yes you are right, I had to be blind yesterday... so my tip is ID - Transdate - Hours. That should be unique for all columns....

 

The start/end date I've used from one ppt file from RegoUniversity because cannot find how are these dates handled.

Maybe when we found out vendor and ID were the causes, only WHERE @FILTER@ could be enough to use?

 

Thanks for your points.

 

Matej

[matej256]

Hi All,

 

So finally I got it. Vendor code is set in NSQL so I've only shown Vendor description as read only in filter section.

I've used then day of tracked hours as "Fast Close Days" filter only with WHERE @FILTER@ clausule and it works fine.

 

Only thing I've noticed it when I've checked "required" in this date filter, it's not shown with * in the portlet....

Portlet without asterisk in date filter (*):

 

Thanks to all for your valuable hints

 

Matej