Clarity

Is it possible to return portlet filter values to the portlet grid results. I would like to be able to filter by a date range but then return the filter range values to the results set.    Here is the general idea (but this doesnt work):  SELECT @SELECT:DIM:USER_DEF:IMPLIED:INVESTMENT:V.UNIQUE_ID:UNIQUE_ID@,
      @SELECT:DIM_PROP:USER_DEF:IMPLIED:INVESTMENT:V.PROJECT:PROJECT@,
      @SELECT:DIM_PROP:USER_DEF:IMPLIED:INVESTMENT:V.WEEK_ENDING:WEEK_ENDING@,
      @SELECT:DIM_PROP:USER_DEF:IMPLIED:INVESTMENT:V.FILTER_VALUE_FROM:FILTER_VALUE_FROM@ ,       @SELECT:DIM_PROP:USER_DEF:IMPLIED:INVESTMENT:V.FILTER_VALUE_TO:FILTER_VALUE_TO@ FROM (
      SELECT (I.ID || S.ID) UNIQUE_ID,
              I.NAME PROJECT,
              S.WEEK_ENDING WEEK_ENDING,
              @WHERE:PARAM:USER_DEF:DATE:WEEK_ENDING_FROM @ FILTER_VALUE_FROM ,               @WHERE:PARAM:USER_DEF:DATE:WEEK_ENDING_TO @ FILTER_VALUE_TO
      FROM INV_INVESTMENTS I
              JOIN ODF_CA_STATUS S ON I.ID = S.ODF_PARENT_ID) V
WHERE 1 = 1
      AND @FILTER@  Where WEEK_ENDING_FROM and WEEK_ENDING_TO are the references to what the @FILTER@ produces when you do a SQL preview:  ...AND V.WEEK_ENDING >= :week_ending_from
AND V.WEEK_ENDING

Yes you can include the parameter as a constant column, and the logic of your approach seems ok (I can't test it right now). Maybe try something like this?[left]
SELECT
@SELECT:DIM:USER_DEF:IMPLIED:INVESTMENT:V.UNIQUE_ID:UNIQUE_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:INVESTMENT:V.PROJECT:PROJECT@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:INVESTMENT:V.WEEK_ENDING:WEEK_ENDING@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:INVESTMENT:V.FILTER_VALUE_FROM:FILTER_VALUE_FROM@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:INVESTMENT:V.FILTER_VALUE_TO:FILTER_VALUE_TO@
FROM (
SELECT
S.ID UNIQUE_ID /* you may not need to do this concatenation, now its numeric */
, I.NAME PROJECT
, S.WEEK_ENDING WEEK_ENDING
, @WHERE:PARAM:USER_DEF:DATE:WEEK_ENDING_FROM@ FILTER_VALUE_FROM
, @WHERE:PARAM:USER_DEF:DATE:WEEK_ENDING_TO@ FILTER_VALUE_TO
FROM INV_INVESTMENTS I
JOIN ODF_CA_STATUS S ON I.ID = S.ODF_PARENT_ID
WHERE S.WEEK_ENDING >= @WHERE:PARAM:USER_DEF:DATE:WEEK_ENDING_FROM@
AND S.WEEK_ENDING no guarantee on this code whatsoever!  [left]  [left] nag nag, I prefer commas first ;-) see   this image    for why.[left]
[left]

This has worked perfectly - thank you! I should remember to put WHERE parameters in the WHERE clause!

jadams_nyc wrote:

This has worked perfectly - thank you! I should remember to put WHERE parameters in the WHERE clause!

Hi

Were you able to get the Date Range working in Filter?

If so, could you please send across a copy of the NSQL and the screen shot of the portlet.

Thanks
Muthu
muthugomu.rathnamswamy@ca.com

^ DATE RANGEs work fine for normal attributes. Two separate :WHERE:PARAM: fields are required to implement the same sort of functionality using 'user parameter' fields (but they will implement as 2 distinct fields rather than one date field rendered as a 'date range').

Perhaps if you describe your problem you might get a better response?