pass parameter the report output

[vjan]

Hi,
I use Design Studio to enrich report with parameter. I want user to choose the year from pulldown menu, then the report is generated having the choosed year in the header. The problem is....

1)I have Relational Process Action YEAR_FILTER which returns resultset like this:

YEAR_ID(number), YEAR_NAME(string)
-------------------------------------------------------
2003 ,2003
2004 ,2004
2005 ,2005
1111 ,ALL

2)I have parameter YEAR defined

3)I have Prompt Process Action for YEAR parameter where I set YEAR_FILTER as 'Source of Choices', column YEAR_ID as 'Column Containing Choice Values' and column YEAR_NAME as 'Column Containing Choice Names'. So the user see puldown list with 2003,2004,2005,ALL but in SQL queries numbers 2003,2004,2005,1111 are used.

4)Finally in Pentaho Report Process Action I select which parameters can be included in report output in the Parameters Section

5)Now I can include parameter in the xml file of the report like ${YEAR}


The problem is when user choose the ALL item, because in my report header is written year like '1111' instead of 'ALL'. So my question is, is there a way how to pass to the report output not the 'Column Containing Choice Values' but 'Column Containing Choice Names'?

vjan

[vjan]

Ok, has someone more elegant solution to this than mine?:

1)I placed new Relational Action YEAR_TEXT_FILTER with query


select (CASE cast({PREPARE:YEAR} as varchar) when cast(1111 as varchar) then cast('All' as varchar) else cast({PREPARE:YEAR} as varchar) end) as YEAR_TEXT

after the main SQL query Action.

2)and new parameter YEAR_TEXT

3)used Prompt Action for YEAR_TEXT with 'Prompt when one choice' unchecked, so this prompt menu does not appear to the user