Absent Member.. kruegke Absent Member..
Absent Member..
120 views

Using Portlet Column Value in Portlet Filter

Jump to solution

I have created a new Portlet column that displays a "Yes" / "No" value based on a SELECT statement against a table.  I would like to use this column (field) as a portlet filter (i.e., to allow filtering based on the derived value for this column).  Can this be done, and if so, how would the column be referenced in the filter SQL?

0 Likes
1 Solution

Accepted Solutions
Jason Nichols K Absent Member.
Absent Member.

Re: Using Portlet Column Value in Portlet Filter

Jump to solution
You can't reference your name for the field in the SQL. That's just a plain SQL limitation. What you can do, however, is put the statement itself into the where clause of the filter:

AND ((SELECT DECODE(Count(*),0,'NO','YES') FROM knta_lookups WHERE lookup_type = 'Key Freeze Dates' AND lookup_code = TO_CHAR(ts.sched_finish_date) AND enabled_flag = 'Y') = '[P.FREEZE_Y]')

Jason
6 Replies
MaxH Honored Contributor.
Honored Contributor.

Re: Using Portlet Column Value in Portlet Filter

Jump to solution

A filter in the data source should look something like this:

 

(for example)      and DRIVER = ('[P.DRIVER1]')

 

So, in your case, you should determine the column name, assign it a validation, call the column name where it equals the token from the validation, then use it for the filter.

 

Next, you'll need to use that filter in the Portlet Definition that you create for the Portlet so the user can filter on it.  Make sense?

 

 

Max

Absent Member.. kruegke Absent Member..
Absent Member..

Re: Using Portlet Column Value in Portlet Filter

Jump to solution

Maybe more specifics would help . . .  Here is the select statement code for the portlet column I want to use as a filter: 

 

        (SELECT DECODE(Count(*),0,'','YES') FROM knta_lookups WHERE lookup_type = 'Key Freeze Dates' AND   

         lookup_code = TO_CHAR(ts.sched_finish_date) AND enabled_flag = 'Y') FREEZE_PERIOD,

 

So I want to be able to check for this column "FREEZE_PERIOD" equaling "YES" in the AND statement for the new portlet filter named "FREEZE_YN".  Something like the following:

 

        AND 1 = decode('[P.FREEZE_YN]', 'N', 1, <FREEZE_PERIOD>, 'YES', 1, 0))  

 

I just do not know how the properly reference <FREEZE_PERIOD> here.

 

Thanks.

0 Likes
Absent Member.. randull Absent Member..
Absent Member..

Re: Using Portlet Column Value in Portlet Filter

Jump to solution

Hi,

 

What you have to do is add the field as a filter on your data source, from the workbench.  Then when you configure the portlet you set this filter as yes and that will do the magic.

Best regards,
Randall

-- Remember to give Kudos to answers! (click the KUDOS star)
"If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
0 Likes
Absent Member.. kruegke Absent Member..
Absent Member..

Re: Using Portlet Column Value in Portlet Filter

Jump to solution

I understand the need to add this column field as a portlet filter, but what I am struggling with is how to construct the associated "AND" clause for the filter.

0 Likes
Absent Member.. randull Absent Member..
Absent Member..

Re: Using Portlet Column Value in Portlet Filter

Jump to solution

Could you please clarify? I don't understand what is your question.

 

Best regards,
Randall

-- Remember to give Kudos to answers! (click the KUDOS star)
"If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
0 Likes
Jason Nichols K Absent Member.
Absent Member.

Re: Using Portlet Column Value in Portlet Filter

Jump to solution
You can't reference your name for the field in the SQL. That's just a plain SQL limitation. What you can do, however, is put the statement itself into the where clause of the filter:

AND ((SELECT DECODE(Count(*),0,'NO','YES') FROM knta_lookups WHERE lookup_type = 'Key Freeze Dates' AND lookup_code = TO_CHAR(ts.sched_finish_date) AND enabled_flag = 'Y') = '[P.FREEZE_Y]')

Jason
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.