Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Multi Select Filter fields

Highlighted
Ben-mor
Super Collector

Multi Select Filter fields

Hello everyone,

My question is regarding Multi-select filter fields. I am having a problem getting them to compeltely work and I am unsure as to why.

Basically I have created a list portlet that shows all the enabled users for the company. One column on this list is represented with RESOURCE_CATEGORY_CODE which can have one of the 5 values; FULL_TIME, PART_TIME, CONTRACTOR A, CONTRACTOR B and SUPPLIER.
I have been asked to provide a multi-select filter on this so the user can select 2 of the 5 values and it will just return the rows that have those values in.

The SQL data source I have created is very simple and only pull data from one table. See below.

SELECT USERNAME USER_NAME, FIRST_NAME FIRST_NAME, LAST_NAME LAST_NAME, START_DATE START_DATE, END_DATE END_DATE, RESOURCE_CATEGORY_CODE CATEGORY, USER_ID USER_ID
FROM
KNTA_USERS
WHERE (END_DATE >= sysdate OR END_DATE is NULL)
AND RESOURCE_CATEGORY_CODE =[P.RESOURCE_CATEGORY_CODE.TO_STRING]

I have tried variations of the filter field i.e. AND RESOURCE_CATEGORY_CODE IN([P.RESOURCE_CATEGORY_CODE]) etc but I am having no luck. There is no SQL error however it when ever I choose more than two values no results are returned. I have attached screenshot of datasource and

Please can anyone help?!

Much appreciated!

Ben
22 REPLIES
Ben-mor
Super Collector

Re: Multi Select Filter fields

(2nd screenshot)

of the filter field.

Thanks
Mahen M
Honored Contributor

Re: Multi Select Filter fields

Hi Ben,
Can you paste the validation of the filter field?

Regards,
Mahendran M
Erik Cole
Honored Contributor

Re: Multi Select Filter fields

AND RESOURCE_CATEGORY_CODE IN ([P.RESOURCE_CATEGORY_CODE.TO_STRING])

should do it. If not, make a typo in the query somewhere and post the SQL from the subsequent error message so we can see what its doing with your filter.
Ben-mor
Super Collector

Re: Multi Select Filter fields

Hi Mahendran

Please find attached a screen shot of the Validation for the filter field.

Thanks

Ben
Mahen M
Honored Contributor

Re: Multi Select Filter fields

Yes... introducing error in Query is the best way of analysis.

I hope 'RESOURCE_CATEGORY_CODE' stores ID

[P.RESOURCE_CATEGORY_CODE] Stores Name

or vice-versa

Please check up this data type consistency.
Thank you.

Regards,
Mahendran M
Ben-mor
Super Collector

Re: Multi Select Filter fields

Hi Erik,

I have tried the .TO_STRING method, but sadly that does not work either. I have mad ea typo in the query and attached it to this thread (SQL Error1). I have also attached two other SQL Errors from other things I have tried. Each error seesm to show a '?' in the Error.

Thanks Ben
Ben-mor
Super Collector

Re: Multi Select Filter fields

SQL Error2

AND #RESOURCE_CATEGORY_CODE = '[P.RESOURCE_CATEGORY_CODE.TO_STRING]'
Ben-mor
Super Collector

Re: Multi Select Filter fields

SQL Error 3

AND #RESOURCE_CATEGORY_CODE IN ([P.RESOURCE_CATEGORY_CODE])
Ben-mor
Super Collector

Re: Multi Select Filter fields

Mahendran Muthu
Yes... introducing error in Query is the best way of analysis.

I hope 'RESOURCE_CATEGORY_CODE' stores ID

[P.RESOURCE_CATEGORY_CODE] Stores Name

or vice-versa

Please check up this data type consistency.
Thank you.

Hi Mahendran,

RESOURCE_CATEGORY_CODE does not store an ID as such. It stores the values FULL_TIME, PART_TIME, CONTRACTOR A, CONTRACTOR B, SUPPLIER.

I added the P. to the P.RESOURCE_CATEGORY_CODE because it seemed the most logical thing to do and it worked for a drop down filter field.

Many thanks

Ben
Mahen M
Honored Contributor

Re: Multi Select Filter fields

I am not able to open the attachments, I am working on it.

Please change that 'P.' as 'VP.' and try once, In the mean time I will see the Query.

Thank you.

Regards,
Mahendran M
Ben-mor
Super Collector

Re: Multi Select Filter fields

Hi Mahendran,

I have chnaged the 'P.' value to 'VP.', there is no SQL error but if I select just one of the choices i.e. FULL_TIME no values are returned at all. I have tried this with variations as well.

Many thanks

Ben

Mahen M
Honored Contributor

Re: Multi Select Filter fields

You mean to say that for all the different kinds of input, you get no output.

Regards,
Mahendran M
Ben-mor
Super Collector

Re: Multi Select Filter fields

Yes thats correct!

If I choose no input, then there is output, the whole list is returned.
Mahen M
Honored Contributor

Re: Multi Select Filter fields

Ok, I am correct P. contains ID.

Whether you created a new validation of filter field, or re-using Existing one.

Regards,
Mahendran M
Ben-mor
Super Collector

Re: Multi Select Filter fields

The validation I used was from one that was previously there. I just copied it and have been using that version to avoid damaging anything.
The filter field is newly created as is the portlet datasource.

If you require any other information please let me know

Many thanks

Ben
Mahen M
Honored Contributor

Re: Multi Select Filter fields

Please introduce an error in the Query and Send me the query which comes in the front end in notepad, Kindly dont zip it.

Regards,
Mahendran M
Ben-mor
Super Collector

Re: Multi Select Filter fields

Hi

I have attached a word doc with some screenshots indicating the SQL error and also the validation, filter etc.

Thanks

Ben
Mahen M
Honored Contributor

Re: Multi Select Filter fields

Hi Ben,
I created portlet from my end, It works for me.

Regards,
Mahendran M
Ben-mor
Super Collector

Re: Multi Select Filter fields

Okay, so I guess it must be something wrong here then. I will keep investigating to try and figure out why this doesnt want to work.

Thanks

Ben
Mahen M
Honored Contributor

Re: Multi Select Filter fields

Perhaps, Set up a Webex meeting with HP Support.... I am not sure whether it is a problem with the current Service Pack which you are using.

Regards,
Mahendran M
Erik Cole
Honored Contributor

Re: Multi Select Filter fields

I'm with Mahendran - it seems you have a problem with the resolution of your token since in your "typo'd" SQL you are getting a ? instead of the actual values you selected.

The following does work:

and RESOURCE_CATEGORY_CODE in ([P.CATEGORY.TO_STRING])
Ben-mor
Super Collector

Re: Multi Select Filter fields

Okay, thank you both very much for your help. I will raise a ticket with HP support and see if they can help fix my problem.

Thanks

Ben
//Add this to "OnDomLoad" event