CODEWITHSUNDEEP
jasper-reports
Aubergine
Aubergine

Reputation: 6042

How to pass column names as parameters in jasper reports

I need to be able to pass column names as parameters for fields references in jasper reports whenever executing query.

This is required so that .jrxml report will not be tied to column names listed in the query(effectively allowing to run any sql query on a jrxml report).

I want to do something like $F{$P!{Parameter1}} for value or category expressions in charts, where:

<parameter name="Parameter1" class="java.lang.String" isForPrompting="false">
    <defaultValueExpression><![CDATA["country"]]></defaultValueExpression>
</parameter>

But I get error : "Field not found" get $P!{Parameter1 --- net.sj.jasperreports.engine.design.JRDesignExpression

Category data set(for chart):

<categoryDataset>
            <categorySeries>
                <seriesExpression><![CDATA[country]]></seriesExpression>
                <categoryExpression><![CDATA[$F{$P!{Parameter1}}]]></categoryExpression>
                <valueExpression><![CDATA[$F{number_of_sites}]]></valueExpression>
            </categorySeries>
        </categoryDataset>

I am 100 % somebody attempted to do the same thing, any suggestions please?

Upvotes: 0

Views: 3804

Answers (3)

veritaS
veritaS

Reputation: 519

you can not set variables in SQL as column or table names. But with the following trick you are able to. I hope this will help you.

    DECLARE @variable1 system; /* type system is datatype for tablenames, columns etc.*/
    DECLARE @variable2 system;
    DECLARE @variable3 system;

    Select @variable1 = tablename, @variable2 = columname, @variable3 = variable
    From tempdb.dbo.powerdeviation
    Where ID = @ID

    /*please note that if the select result is giving you more then one result,
    the first row is used as the variable! There will be not error*/


    DECLARE @sqlCommand nvarchar(max)='
    SELECT something
      FROM [db@variable1].[dbo].['+ @variable2 +']
      where LogInterval = 10 and PlantID = ''@variable3''
    '
    SET @sqlCommand = REPLACE(@sqlCommand,'@variable',@variable);
    SET @sqlCommand = REPLACE(@sqlCommand,'@variable2',@variable2);
    SET @sqlCommand = REPLACE(@sqlCommand,'@variable3',@variable3)

    PRINT @sqlCommand;
    EXEC sys.sp_executesql @sqlCommand;

Upvotes: -1

timo.rieber
timo.rieber

Reputation: 3867

I would try to manipulate the query (like dynamic sql) instead, using your parameter like you did within categoryDataset.

We used that many times to prepare our queries to retrieve the specified column for grouping purposes (mostly with jasperreports), and I'm pretty sure this will work for you, too.

Your query will look like this:

select
    column1,
    column2,
    column3,
    column4,
    $P!{Parameter1} as GROUP_COL

from
   yourTable

This is also covered in the jasper docs.

Upvotes: 0

Sharad
Sharad

Reputation: 3548

You can try this, use parameter directly with default value which is column name in your case.

<categoryDataset>
     <categorySeries>
         <seriesExpression><![CDATA[$P!{Parameter1}]]></seriesExpression>
         <categoryExpression><![CDATA[$P!{Parameter1}]]></categoryExpression>
         <valueExpression><![CDATA[$F{number_of_sites}]]></valueExpression>
     </categorySeries>
</categoryDataset>

Upvotes: 0

Related Questions