Project and Portfolio Management Practitioners Forum
cancel

Help [Urgent] - Getting different result sets from portlets

SOLVED
Go to solution
Highlighted
sam.greg
Honored Contributor.

Help [Urgent] - Getting different result sets from portlets

Hi,

We have 1 portletA that shows 10 results when filtered based on 'Scheduled release'. But when I run 'Request List' portlet (built-in) with same filter on 'Scheduled release' it shows me 50 results.

Can somebody please help me in understanding why this difference?

(Just to let you know - When I ran SQL query attached with portletA from SQLPlus it showed me 1700 records.)


Regards,
.\Sam
24 REPLIES
Erik Cole_1
Outstanding Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

Is this a paging issue? Ie. at the bottom of the portlet does it say "Showing 1 to 10 of 200"
sam.greg
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

No Erik. Its only one single page. 1-20 of 20.


What to do?

Cheers
Sam.
Nisha Kurien
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

There must be an extra filter on portlet A that is restricting the results. Click on Edit Preferences and verify if there are any inputs for the filter fields.

- Nisha Kurien
sam.greg
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

No. Only thing common between the two portlet is
1. Request type - Development Req, Small Dev
2. Scheduled Release - May 09;Aug 09


Rest all fields are empty on botht the portlets.

What should be the reason?


Regards,
Sam
Nisha Kurien
Honored Contributor.
Solution

Re: Help [Urgent] - Getting different result sets from portlets

Can you document the differences in a word document so I can visually see them. It will really help in quickly resolving your issue.

- Nisha Kurien
sam.greg
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

Hi Nisha,

Please find attached issue summary document.

Please let me know.

Regards,
Sam
Nisha Kurien
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

I'm looking into this. I'll let you know what I find.
sam.greg
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

Sure. I shall wait.
Niraj Prabhu
Outstanding Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

Hello Sam,

I see that you are trying to use two request types together, Can you just try to use one request type on both custom and ootb portlet and see if the results are coming same. with ootb, the flag for Include Closed is default set to No. Will your custom portlet filters the closed requests?

Thanks!
Niraj
Niraj P.
sam.greg
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

Hi Niraj,

No, our custom portlet does not include closed requests.

I will do the change as mentioned by you and check now.

Thanks

Regards,
Sam
sam.greg
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

Hi Niraj,

I just removed one filter value from both the portlets. But now the returned no. of records for both the portlets are same (just similar to if there were NO filter).

Additionally, I could figure out that, few entires in returned records do not match between 2 portlet (around 4-5 records). What could be the reason here?


Thanks.

Regards,
Sam
Nisha Kurien
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

(a) When the user doesnt use the filters provided in step-3, both the portlets returns 200 records. (But the data in result is different I dont know why, can you guess?)
>> There is a setting in the file server.conf that limits the number of rows a portlet returns. By defualt it is set to 200. This would explain why you see 200 rows inspite of there being more rows. This is the parameter setting
com.kintana.core.server.PORTLET_MAX_ROWS_RETURNED=200
This parameter can be changed from 200 to 500 or 1000, but I've heard that increasing the value can cause performance issues. You could encourage your client to use more filter criteria to limit the total rows returned to less than 200.

For the data

(b) When the user uses the filters provided in step-3, RequestList portlet returns 100 records. But customizedRequestList do not return any single record. Why?
>> In the OOTB portlet, you have an additional condition (c) ACE Required = ALL which is not present for the custom portlet. Could that be a reason?

Secondly - I have a feeling it could be the way the code is formatted for the date field Scheduled Release. PPM is quite tricky with its date format, so could you please send me the code you used for the filter Scheduled Release? Ideally it should be in the format 'YYYY-MM-DD HH24:MI:SS'. For example in the "and" clause you would have

and krhd.parameter6 = to_date(SCHEDULED_RELEASE_TOKEN_NAME, 'YYYY-MM-DD HH24:MI:SS')
Nisha Kurien
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

Here are my responses below:

(a) When the user doesnt use the filters provided in step-3, both the portlets returns 200 records. (But the data in result is different I dont know why, can you guess?)
>> There is a setting in the file server.conf that limits the number of rows a portlet returns. By defualt it is set to 200. This would explain why you see 200 rows inspite of there being more rows. This is the parameter setting
com.kintana.core.server.PORTLET_MAX_ROWS_RETURNED=200
This parameter can be changed from 200 to 500 or 1000, but I've heard that increasing the value can cause performance issues. You could encourage your client to use more filter criteria to limit the total rows returned to less than 200.


(b) When the user uses the filters provided in step-3, RequestList portlet returns 100 records. But customizedRequestList do not return any single record. Why?
>> In the OOTB portlet, you have an additional condition (c) ACE Required = ALL which is not present for the custom portlet. Could that be a reason?

Secondly - I have a feeling it could be the way the code is formatted for the date field Scheduled Release. PPM is quite tricky with its date format, so could you please send me the code you used for the filter Scheduled Release? Ideally it should be in the format 'YYYY-MM-DD HH24:MI:SS'. For example in the "and" clause you would have

and krhd.parameter6 = to_date(SCHEDULED_RELEASE_TOKEN_NAME, 'YYYY-MM-DD HH24:MI:SS')

Let me know if this works.

- Nisha Kurien
sam.greg
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

Yeah, sure. I will go thru your points and change accordingly. I am still working on this. So, should be able to provide you an update in another 10-15 mins.


Thanks

Regards,
Sam
Nisha Kurien
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

Also by krhd.parameter6 - I just assumed that the column Schedule Release is from the header section and is stored in parameter6. Please replace accordingly.

- Nisha Kurien
sam.greg
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

Hi Nisha,

1) Regarding your point -
"ACE Required = ALL which is not present for the custom portlet. Could that be a reason?"

### I removed it and set it to but still same issue.

2) Regarding your second reply -
I have shared the SQL statement running at backend. Attahing the where clause snap used in filter 'Scheduled Release'.
So you could see, we dont have any date funtion used. Could this be a problem?


Thanks

Regards,
Sam

sam.greg
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

And the validation used in Schedule release is - attached.
sam.greg
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

Hi Nisha,

Any further pointers for me?

thanks

Regards,
Sam.

BTW- I think there is some bug in this forum site. I didnt actually clicked 'I found solution' but still that icon got enabled.
Nisha Kurien
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

Ok, so the assumption that Scheduled Release was a date field is wrong. The way you have set it with the validation is slightly incorrect.

In your query you are using krhd.parameter10 and in the "in" clause you are using the token VP.SCHEDULED_RELEASE.TO_STRING.

So ideally you are trying to match the Visible Token with the Parameter value. This way the value will always never match and the result set will be zero.

Whenever you use a SQL to match two sets together, make sure it is has either Visible Parameter on both sides or just the Parameter on both sides.

Before I confuse you more, this is what I'm trying to convey.

Your "and" clause should be
AND KRHD.VISIBLE_PARAMETER10 in (VP.SCHEDULED_RELEASE.TO_STRING)

(notice both have the Visible on both sides)
****************************

Or you should match both parameter values like
AND KRHD.PARAMETER10 in (P.SCHEDULED_RELEASE.TO_STRING)

(notice both have the just the parameter or P on both sides)

I'm sure this is the problem and changing this will solve your problem. Providing the screenshot really helped in pinpointing the issue.

Let me know how it goes.

- Nisha Kurien
Nisha Kurien
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

Slight change - I forgot to enclose the square bracket... Early morning, I need my coffee ;)

AND KRHD.VISIBLE_PARAMETER10 in ([VP.SCHEDULED_RELEASE.TO_STRING])

or you use
AND KRHD.PARAMETER10 in ([P.SCHEDULED_RELEASE.TO_STRING])

sam.greg
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

Thanks Nisha. I will apply the changes and update you shortly.

Thanks,

Regards
Sam
Nisha Kurien
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

In addition to changing the SQL for the filter Scheduled Release, please check all the other filter fields that you use. Ensure that the "and" condition uses either Visible on both sides or just Parameter on both sides.
sam.greg
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

Thanks a BUNCH Nisha. The problem is resolved. I think changing P - P or VP - VP solved the issue.

I really appreciate your stand on this issue and assisting me to close.

Thanks once again. I owe you a cofee!! Lemme know when and where ;)


Regards,
Sam.
Nisha Kurien
Honored Contributor.

Re: Help [Urgent] - Getting different result sets from portlets

You are most welcome. I'm glad I could help :)